At a recent client engagement we built a SQL Server Reporting Services (SSRS) report with a T-SQL based data source. A requirement of the report was to create an action that drilled-through to another report. No big deal, right? This is SSRS 101. Well, that was until I realized that the parameters that had to be set on the drill-through report were based on MDX queries. I still didn’t think that it would pose a problem and proceeded to configure the textbox Action to go to another report. After I completed the configurations, I ran the report and it DID NOT WORK! Hmmm, what’s wrong?

The first step was to take a look at the parameters on the MDX based report, which revealed very little. I needed to see the source query that provided the data for the parameter. Initially I did not see the data set for the query. Then I realized that when you created parameters using the MDX query builder in SSRS it hid the corresponding data sets. To show them, right-click on the data sets folder and click Show Hidden DataSets. With the data set now visible, I was able to run the query in the designer. The designer revealed a little Golden Nugget of information. There was a column labeled “ParameterValue” as shown in the following figure:

This was interesting. It revealed a fully qualified MDX value as the Parameter Value. Therefore, using the traditional method of only selecting the column from the data set as the value to be passed would not work. So I decided to build an expression that would dynamically create values equal to those values in the ParameterValue column of the MDX query result set. The expression can be seen in the following figure:

I re-ran the report and clicked the textbox with the action:

This directed me to the MDX based report and the parameter was set correctly, which can be seen in the following figure:

Happy Days are here again!

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS, founder SQLLunch.com.