Search code examples
reporting-servicesssrs-2008mdxssrs-2012ssrs-tablix

How to replace/get value of parameter in MDX query passed by SSRS report

Sorry for very basic question,

I have SSRS report which takes value of parameter dynamically and creates MDX query.

Now, I have that MDX query and I want to run in SSMS or MDX studio. But I am not sure how to replace the parameter value.

For example:

WITH MEMBER [Measures].[HC Threshold] AS Val(strtomember(@HC_Threshold).Name), FORMAT_STRING="$#,0"

And I found HC_Threshold dimension, which looks like

enter image description here

based on this, how do I modify 'Val(strtomember(@HC_Threshold).Name), FORMAT_STRING="$#,0"', if user selects 75000.

Let me know if you need any more information, I am completely new with SSRS and Parameterized queries in MDX.

Thanks

Solution

  • Replace the parameter value for a string that produces a valid member of your dimension.

    select [Product].[Model Name].[Model Name] on rows,
[Measures].[Sales Amount] on columns
from [Adventure Works DW2012]
where STRTOMEMBER("[Ship Date].[Calendar Quarter].[4]")

    Note STRTOMEMBER() receives a string as argument and converts it to a valid member.

    In your scenario it would be something like:

    STRTOMEMBER("[HC_Threshold].[ID].[7500]")