CODEWITHSUNDEEP
sqlreporting-servicesssasmdx
Jatin
Jatin

Reputation: 45

How do I use parameter in an MDX query?

I am using parameter called Region. It is having 3 values; Region L1, Region L2 and Region L3. I want user to select the region and that should trigger the MDX query in SSAS. Below is the query:

    select non empty

    {([Region].[Region L3].children , [Product Line].[Product Line L2].children)} on rows,

   {[Measures].[Clients], [Measures].[Commission]} on columns
  from [Products]

So, the report will initially ask to select the region level from the dropdown. If the user selects Region L2, the parameter will take the value of Region L2 and the query would be [Region].[Region L2].children.

I tried strtoset() but not sure, how would I use it here.I am not sure if I can do something like [Region].[@Region].children

Upvotes: 0

Views: 4607

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

Your parameter should be set to a valid unique name in your dimension.

="[Region].[Region L2].&[MyRegion]"

or

="[Region].[Region L1].children"

Then in your MDX script use the parameter as follows:

select non empty STRTOSET(@Region,CONSTRAINED) on rows,
{[Measures].[Clients], [Measures].[Commission]} on columns
from [Products]

So you have to populate your parameter taking in account that its value must be a valid unique name in your parameter.

If you are manually specifying the values in your parameter use:

enter image description here

Use the expression to produce the valid unique name in Value, i.e.:

="[Region].[Region L2].Children"

Let me know if this helps.

Upvotes: 4

Related Questions