SSRS: How can i use a Parameter in MDX Query?

1
votes

I have been trying for some time to get a Parameter to work within an MDX Query which is automatically generated by SSRS Query Designer.

I have tries the following:

  1. FROM ( SELECT ( {[Dim Date].[Fiscal Year].&[+"@Current_FYear"+]}) ON COLUMNS

  2. FROM ( SELECT ( { [Dim Date].[Fiscal Year].[+"@Current_FYear"+} ) ON COLUMNS

  3. FROM ( SELECT ( {StrToMember(@Current_FYear,CONSTRAINED).lag(23):StrToMember(@Current_FYear, CONSTRAINED)}) ON COLUMNS

  4. FROM ( SELECT ( {[Dim Date].[Fiscal Year].[+"Parameters!Current_FYear.Value"+]}) ON COLUMNS

And none of the above give me the result i am looking for: To have the Dataset filter by the value in the Parameter.

Any help on this would b emuch appreciated!

Thanks,

1
reporting-servicesssrs-2012
If you go into the query designer, where you have the dimension that you are using for the date, you will have an operator value..... you can change this to suit your needs. You can have equal range etc. I was struggling to create a date range parameter and after changing the operator value to range, this solved my problem. – R_Avery_17
@Neal1581 thanks for the reply. Apologies as i am quite a newbie here and really struggling on this! When i go to Query Designer, how can an operator (+ or -) here assist me in this scenario?! Also when i am in the Query Designer, where mus ti go to accomplish this exactly. Again, sorry in advance for the spoon feeding questions. Thanks – GonnerDev
you could do with posting an image of your query designer, difficult to explain otherwise. – R_Avery_17

1 Answers

2
votes

First of all, I would suggest using the Parameter checkbox in the Query Designer and let SSRS build the MDX for you. Here's a screenshot in case you missed it.

enter image description here

Another reason to let the report create the parameter for you is that it will automatically create another hidden Dataset to populate the available values of the parameter.

If you do want to edit the MDX manually, the syntax could look like this:

SELECT NON EMPTY { [Measures].[Measure] } ON COLUMNS FROM ( 
SELECT ( STRTOSET(@Parameter, CONSTRAINED) ) ON COLUMNS FROM [Model])

The STRTOSET function converts the parameter value into MDX syntax. It can also handle multi-value parameters.

The parameter you reference here would also need to be defined in the Parameters tab of the Dataset Properties. This is where you link the parameter value from the report to the query.