sql server data warehouse: Hi,

I want to build Dynamic MDX query in SQL Server 2005. Is it possible?

ex. I have one SQL Server Report parameter which contain following value:
1. Station
2. Free Test

I have three Dimension Station, Free Test, Overall Result and one measure
Total Test.

If I select Station in report parameter then following report format i need:

Station | Pass | Fail | Total Test
x1 60 40 100
x2 ... ... ...

If I select Free Test in parameter then following report format i need:
Free Test | Pass | Fail | Total Test
yes 60 40 100
no ... ... ...

I tryed using following query but not sucessful:

WITH SET [Groupby] AS STRTOSet(@Query1)
SELECT NON EMPTY ([Measures].[Total Test Count]) ON 0,
NON EMPTY [Groupby] * [Dim OverallResult].[Overall Result].[Overall
Result].AllMembers ON 1
FROM [OLAP Test Cube]

let me know if you have any solution?

problem was solved.

First execute following query and design the report.

with member [Measures].[MyMeasure] as '[Free Test].currentmember.name'
member [Measures].[MyMeasure2] as '[Free Test].currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3], [Measures].[MyMeasure4]} on columns, {[Free
Test].members} on
rows from [OLAP Test Cube]

and then Edit Dataset using (…) button and paste Dynamic query on Query
String Textbox and click ok.

="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
".currentmember.name'

member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
".currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
".currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3], [Measures].[MyMeasure4]}
on columns, {" & Parameters!Dimension.Value & ".members} on
rows from [OLAP Test Cube]"

Regards,
Dinesh Patel

[quoted text, click to view]