Reporting Services dynamic custom parameter

I wanted to have something else instead of default parameter control on the top of the rendered report. This is what I was able to come up with in the end. You have a menu on the left and by clicking on a country values in the report on the right will change accordingly by “dynamically” changing the MDX WHERE clause in underlying SSAS query.

By dynamic I mean that if you add a country to “Geography” dimension it will show in the menu. It’s not working the way the regular parameter control is working. Each click on the country means a query to SSAS, which is not a case in parameter control – you set all the parameter options and THEN it will render the report.

Main points of this solution :

Menu on the left is tablix with just one column and hidden header.
What makes it “dynamic” – e.g. that you can toggle option on and off – is background color function depending on
variable/parameter pair which is storing actual and “clicked” option stored in string in form of 1 or 0 on particular position in the string from the left. For instance, “0011” means that options 3 and 4 (Canada and France in this case) are on and the rest is off.
When you click on any country, report parameter called “rpCountry” is computed as combination of report variable vToggle  and position of clicked column in “menu”. That is – vToggle is storing previous setting of menu and parameter rpCountry is storing current setting of menu and after report is rendered vToggle is set to rpCountry value.
Since background color is set dynamically based on position of a row and vToggle value,  it appears that menu row was toggled on and report values has changed accordingly. When in fact it changed color AFTER report was rendered again.
Underlying MDX query takes rpCountry string as an parameter and WHERE slicer clause is set “dynamically” accordingly to which country is toggled on.

This is the MDX query for menu :

WITH MEMBER Measures.x AS [Geography].[Country].CurrentMember.Name
SELECT { Measures.x} ON COLUMNS,
{ ([Geography].[Country].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM [Adventure Works] CELL PROPERTIES VALUE

This is the MDX query for the table of actual values :

SELECT NON EMPTY { [Measures].[Reseller Sales Amount] } ON COLUMNS,
NON EMPTY { ([Product].[Product Categories].[Product].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works]
WHERE

FILTER([Geography].[Country].AllMembers,
MID(@rpCountry, RANK([Geography].[Country].CurrentMember, [Geography].[Country].Members ),1) =
"1" )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

What’s crucial here is the WHERE clause where the cube is sliced according to @rpCountry parameter which is just menu string representation. What the Filter does is that it will select just those members of [Geography].[Country] hierarchy which position (computed by RANK) in the parameter is set to 1 (decided by MID function).

Menu tablix textbox has set Fill propery function :

=IIF(MID(variables!vToggle.Value, RunningValue(Fields!x.Value, CountDistinct, Nothing),1) = "1",
"LightBlue","Silver")

This is coloring each row of the menu depending on row number ( nice trick with RunningValue here – need to find proper author reference here) and variable vToggle which is holding current menu setting.

There’s also Action property set :

Function is :

=IIF(Mid(Variables!vToggle.Value, RunningValue(Fields!x.Value, CountDistinct, Nothing),1) = "1"
,
Mid(Variables!vToggle.Value, 1, RunningValue(Fields!x.Value, CountDistinct, Nothing) -1) & "0"
& Mid(Variables!vToggle.Value, RunningValue(Fields!x.Value, CountDistinct, Nothing) + 1)
,
Mid(Variables!vToggle.Value,1, RunningValue(Fields!x.Value, CountDistinct, Nothing) -1) & "1"
& Mid(Variables!vToggle.Value, RunningValue(Fields!x.Value, CountDistinct, Nothing) + 1)
)

This is setting 1 or 0 on row number position in the string. This is what it makes work – it’s building the parameter for which countries should be shown in the report.

There’s report variable vToggle :

And report parameter rpCountry is set :

I hope I didn’t forget anything important here.

Some caveats : you might want to do some handling regarding All member – I just wanted to show a principle. Default value of parameter is set arbitrary – you have to be careful to not exceed number of hierarchy members. All functions (RANK in MDX or RunningValue in SSRS) are order dependent – that means you have to bear in mind how are members in hierarchy sorted. I already mentioned it, but this involves round-trip to server – you click on menu and query is send to server and report rendered again. But that’s the case with reporting services – it’s not an application, it’s the reporting tool.