How to use MDX Query with Excel

[Database Administrator]

Hello Everybody

I am using SQL Server 2000 (with Service Pack 3) and Analysis Service
(with Service Pack 3). I have made one Cube. It's working fine. But
problem arise when I tried to use MDX Query. Actually I have an MDX
query and I want to see it's result in excel.

So I want to know is these any way of using MDX Query as Data Source of
Excel as we can use any Cube ???
Any help or suggestion will be appriciated !!!!

Thnaks


Ganesh

[Database Administrator]

The code below allows you to write your own MDX & have it presented in
Excel.
Much better to buy an Excel add-in.
They are not expensive.
I like XLCubed & MIS Plain.

Regards,

John Keeley

www.johnkeeley.com



Option Explicit


Private db As ADODB.Connection


Public Sub DisplayMDX()
Dim sQry As String
Dim sConnection As String
Dim rs As Cellset
Dim i As Integer, j As Integer, k As Integer
Dim intCellY As Integer, intCellX As Integer
Dim ws As Worksheet
Dim Product As String
Dim Cover As String
Dim STD As String
Dim SelfIssue As String



Product = "[Product].[" & Sheets("Result").cboProduct & "]"
Cover = "[Cover].[" & Sheets("Result").cboCover & "]"
STD = "[STD].[" & Sheets("Result").cboSTD & "]"
SelfIssue = "[SelfIssue].[" & Sheets("Result").cboSelfIssue & "]"

'*--------------------------------------------------------------------------------------------------
'* Construct your MDX query how you wish, this may take parameters
from a drop down box on a report
'* or just be typed in here.

'*--------------------------------------------------------------------------------------------------
sQry = "SELECT" & Chr(10)

sQry = sQry & " CrossJoin({[YOA].[Yoa].Members,[YOA].[2002 v
2001]},{[TypeOfMeasure].[NB
Count],[TypeOfMeasure].[LapseRatio],[TypeOfMeasure].[ClaimFreq],[TypeOfMeasure].[ClaimFreqExGlass]})
on Columns," & Chr(10)

sQry = sQry & " {[WorkMth].[Work Mth].Members} on Rows" & Chr(10)
sQry = sQry & "FROM" & Chr(10)

sQry = sQry & " [DetailedTriangulations] " & Chr(10)
sQry = sQry & "WHERE " & Chr(10)

sQry = sQry & "(" & Product & ", " & Cover & ", " & STD & ", " &
SelfIssue & ") "

'Open a new ADO connection
Set db = New ADODB.Connection


'Change the data source to your server name and the provider to
MSOLAP (I think!)
sConnection = "DATA


SOURCE=chaucerbdcukw;PROVIDER=MSOLAP;DATABASE=Deta iledTraingulations;"
'Use this user name and password to connect to the server.
db.Open sConnection, "Admin", ""


'Open a CellSet to store the results of the query.
Set rs = New Cellset


'Tidy the query of an erroneous spaces
sQry = Trim(sQry)


'Open the query that was constructed above
With rs
..Open sQry, db
End With


'Add a new worksheet to display the results
Set ws = ActiveWorkbook.Worksheets("Result")


With ws


'*--------------------------------------------------------------------------------------------------
'* Read in Column Header

'*--------------------------------------------------------------------------------------------------
'For i = 0 To rs.Axes(0).Positions.Count - 1
'intCellY = i + rs.Axes(1).Positions(0).Members.Count + 1
'*Moves the Header across*'
'If rs.Axes(0).Positions(i).Members.Count = 1 Then
' .Cells(5, intCellY).Value =
rs.Axes(0).Positions(i).Members(0).Caption
'Else
' .Cells(5, intCellY).Value =
rs.Axes(0).Positions(i).Members(0).Caption
' .Cells(6, intCellY).Value =
rs.Axes(0).Positions(i).Members(1).Caption
'End If
'Next


'*--------------------------------------------------------------------------------------------------
'* Read in Row Header

'*--------------------------------------------------------------------------------------------------
For j = 0 To rs.Axes(1).Positions.Count - 1
intCellX = j + rs.Axes(0).Positions(0).Members.Count + 7
'*Shifts the rows down (originally 1)*'
If rs.Axes(1).Positions(j).Members.Count = 1 Then
..Cells(intCellX, 1).Value =
rs.Axes(1).Positions(j).Members(0).Caption
Else
..Cells(intCellX, 1).Value =
rs.Axes(1).Positions(j).Members(0).Caption
..Cells(intCellX, 2).Value =
rs.Axes(1).Positions(j).Members(1).Caption
End If


'*--------------------------------------------------------------------------------------------------
'* Read in values for corresponding row header

'*--------------------------------------------------------------------------------------------------
For k = 0 To rs.Axes(0).Positions.Count - 1
intCellY = k + 2 '*Shifts the numbers to the left*'
..Cells(intCellX, intCellY).Value = rs(k, j).FormattedValue
Next
Next
End With

End Sub


ganesh.bansal@gmail.com wrote:
> Hello Everybody
>
> I am using SQL Server 2000 (with Service Pack 3) and Analysis Service
> (with Service Pack 3). I have made one Cube. It's working fine. But
> problem arise when I tried to use MDX Query. Actually I have an MDX
> query and I want to see it's result in excel.
>
> So I want to know is these any way of using MDX Query as Data Source
of
> Excel as we can use any Cube ???
> Any help or suggestion will be appriciated !!!!
>
> Thnaks
>
>
> Ganesh

[Database Administrator]

Hi Keeley

Thanks for your prompt reply !!!!

Please answer my obe more question.

Is there any way to use As-On-Date. Let me explain my problem exactly.

I have one cube with data on period basis. we can say fact table has 3
columns: DataId, PeriodId, Amount. My dimension table has 2 columns:
PeriodId and PeriodEndDate. Now what I want that when user selects any
periodenddate, he should see data upto that PeriodEndDate now only from
that Period.

I think when v join Fact table and Dimension table, OLAP used inner
join with = from both tables while i want to use >= from both tables.

Is there any way to do it........
Any help or suggestion will be appriciated !!!!
Thnaks


Ganesh

[Database Administrator]

Hi Keeley

Thanks for your prompt reply !!!!

Please answer my obe more question.

Is there any way to use As-On-Date. Let me explain my problem exactly.

I have one cube with data on period basis. we can say fact table has 3
columns: DataId, PeriodId, Amount. My dimension table has 2 columns:
PeriodId and PeriodEndDate. Now what I want that when user selects any
periodenddate, he should see data upto that PeriodEndDate now only from
that Period.

I think when v join Fact table and Dimension table, OLAP used inner
join with = from both tables while i want to use >= from both tables.

Is there any way to do it........
Any help or suggestion will be appriciated !!!!
Thnaks


Ganesh

[Database Administrator]

Hi Keeley

Thanks for your prompt reply !!!!

Please answer my obe more question.

Is there any way to use As-On-Date. Let me explain my problem exactly.

I have one cube with data on period basis. we can say fact table has 3
columns: DataId, PeriodId, Amount. My dimension table has 2 columns:
PeriodId and PeriodEndDate. Now what I want that when user selects any
periodenddate, he should see data upto that PeriodEndDate now only from
that Period.

I think when v join Fact table and Dimension table, OLAP used inner
join with = from both tables while i want to use >= from both tables.

Is there any way to do it........
Any help or suggestion will be appriciated !!!!
Thnaks


Ganesh

[esteban_peru]

hello man, thank you for your vba code,
im writing a prototype, and searching information about rendering output mdx query in a pivot table, it was the way how i arrived here. I asked me how your results look, could you put an image of your excel interface result, it could help me a lot.

Thank You
Regards
Esteban