Dynamic MDX Query

I am working on SRS 2005 report and need to calculate measures based on parameters. The new great feature of SRS2005 which allows to use parameters in MDX query unfortunately does not work in inner query. For example I am building 4 time sets based on specified date ranges and then calculate summary for each time set. So query looks like this:

With

Set [Time1] AS '@.ParameterTime1'

...

Set [Time4] AS '@.ParameterTime4'

member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '

...

member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '

Select

{

[Measures].[Count1, ... , [Measures].[Count4]

} on Columns,

{

...

} on Rows

From [Cube]

It would be great if we still had dynamic queries supported with MDX which was available in SRS 2000. In that case I could build a query string and incorporate paramters in SET section. For example this type of query would not work in SRS 2005. I'm getting an error.

="With Set [Time1] AS ' " & Parameter!ParameterTime1 &"' ....."

If someone found any solution of using complex MDX queries with parameters, please respond. I am searching the web for a few days and no luck.

Thanks

Olga

Hi,

You can use parameters in the calculated members or sets, but you need to remove the character '.

With
Set [Time1] AS @.ParameterTime1
...
Set [Time4] AS @.ParameterTime4
member [Measures].[Count1] as 'Sum ( { [Time1] } ,[Measures].[Count]) '
...
member [Measures].[Count4] as 'Sum ( { [Time4] } ,[Measures].[Count]) '
Select
{
[Measures].[Count1, ... , [Measures].[Count4]
} on Columns,
{
...
} on Rows
From [Cube]

Regards,
Telmo Moreira

|||

Telmo,

Thanks a lot ! You saved my day.

The query with parameters works fine.

Olga

|||

Hi,

I have a similar requirement but the number of sets i need to create is not fixed and needs to be dynamic. I will know the number of date ranges i need to filter on only at run time and i dont want to build an MDX query at runtime. Is there a simpler way of achieveing this by passing all the date ranges I require as a single parameter?

Thanks in Advance,

Arun

|||Hi,
is it possible to use a parameter as mentioned below?

select [Measures].[Internet Sales Amount] on 0,
@.ProductCategories on 1
from [Adventure Works]
And the MDX it would actually run against Analysis Services would be:
select [Measures].[Internet Sales Amount] on 0,
{[Product].[Category].&[1],[Product].[Category].&[2]} on 1
from [Adventure Works]