SSRS/MDX - Parameter value as a Column Header

Question

Using SSRS and MDX

Goal: pull parameter value and show it as the header text for a column

Specs: parameter value is in MDX format.Only one parameter value at a time report generation. This value is populated based on what the person clicked in previous report.

i.e. If the person clicks on Did You Get Proper Help? Hyperlink, the next report will have the following value for TextName parameter

[Questionnaire].[Question Hierarchy].[Question Name].&[Did You Get Proper Help?]

I want to show just “Did You Get Proper Help?” as the name of the header in one of the columns without the other stuff.

The ”Did You Get Proper Help?” is just an example of many options the user can click on in previous report. Based on what text hyperlink they click, the parameter value will be populated and that is what I need to show as the header name of one of the columns.

I am not sure how to tackle this problem.

Thank you

Frank Goortani Frank Goortani · Accepted Answer · 2012-09-06T22:22:01

This is how I deal with this problem in my reports: (I use this method for reports with multiple datasets from multiple Cubes)

1- In SSRS, create your dataset in design view with a default value for the filter but don't make it a query parameter.

2- Create another Dataset for your Report Parameter. You can create calculated columns in this dataset to have different formats for showing the value (specially if its date) Lets call it MonthDataSet.

3- Add new Parameter from the MonthDataSet. This is the tricky part: choose different fields for Value and Label

4- Open your Dataset Property and from there open the Query Designer. Add Parameter to the filter and click OK to close the Query Designer, but don't close the Dataset Property. In the Parameter Tab, use expression to enter the Parameter Value. In the following example, I calculated the end of the month:

="[Date Dimension].[Accounting Month].[Month].&[" &
format(DateValue(year(Parameters!MonthEnd.Value),
month(Parameters!MonthEnd.Value)+1,1).AddDays(-1),"yyyMMdd") & "]"

Now you can use your parameter in your report as [@MonthEnd].Label

SSRS/MDX - Parameter value as a Column Header

1 Answers