Dynamic MDX Query

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?
Dinesh Patelproblem 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.Lev
el.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.Uni
queName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Co
unt]'
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.Va
lue &
".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 Co
unt]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2]
,
[Measures].[MyMeasure3], [Measures].[MyMeasure4]}
on columns, {" & Parameters!Dimension.Value & ".members} on
rows from [OLAP Test Cube]"
Regards,
Dinesh Patel
"Dinesh Patel" wrote:

> 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 nee
d:
> 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].
1;Overall
> Result].AllMembers ON 1
> FROM [OLAP Test Cube]
> let me know if you have any solution?
> Dinesh Patel