SSRS – Using a Parameter to make Dynamic Columns

SSRS – Using a Parameter to make Dynamic Columns

Posted on June 10, 2013 by MikeDavisSQL

Multiple value parameters are a common tools used in SSRS. You can use this tool to select which columns actually show on a report. You will need to create a multiple value parameter and place an expression on each column on the report. More specifically the expression needs to be on the column visibility property of the columns.
Here is an example report with five columns.

Here are the parameter available values with the column choices. I hard coded these choices into the available value fields; you could use a query to get this information. The parameter is text value and multiple values are allowed. I placed a two digit number into the value fields and I will use them in the expressions for the column visibility. The reason I used a two digit number is so this technique will work for reports with more than 9 columns. I am going to look in a comma separated string that contains a list of all the values selected in the parameter. If I used single digits then the number one would be found in other numbers like ten or eleven. If you have more than 99 columns then you will need three digit numbers, and you need to tell your end user that they are crazy for wanting a report with 99 columns.

I right clicked on top of the first column and selected column visibility. For the columns visibility property I selected hide or show based on an expression and clicked on the expression button then typed in the following expression for the first column.
=iif(instr(Join(Parameters!ColumnSelect.Value,”,”),”01″)>0,false,true)
Let’s break this down into the individual functions. First the Join:
(Join(Parameters!ColumnSelect.Value,”,”)
The function takes each of the selected parameter values and joins them together in a string separated by the delimiter you specify at the end of the join. If the end user selects all of the columns the join would result in “01,02,03,04,05”.
Next the inStr function:
instr(Join(Parameters!ColumnSelect.Value,”,”),”01″)
The inStr function looks through the list of joined parameter values and determines if it exist in the string. If it does it will return the number of the character location when the string is found. If it is not in the string then the function will return a zero. This is why I used two digit numbers. If I was to search for a one and I had a row ten, it would find a match. Each column will search for a different string. The first column is searching for “01”, the second “02” and so on.
The next function is the iif:
=iif(instr(Join(Parameters!ColumnSelect.Value,”,”),”01″)>0,false,true)
The boolean statement in the if then is checking the value returned by the inStr function to see if it is higher than zero. If it is higher than zero then the column number is in the string of parameter values and we need set the hidden value to false. If not then we set the hidden property to true.
Here is an example of the report with a few columns selected. I placed a text box at the top of the report to show the joined values of the parameters.