SSRS – Using a Parameter to make Dynamic Columns

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.

clip_image002

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.

clip_image004

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.

clip_image006

Advertisements
This entry was posted in SSRS, Syndication and tagged . Bookmark the permalink.

6 Responses to SSRS – Using a Parameter to make Dynamic Columns

  1. I’m having trouble viewing the images. But this is a neat way to do this, I have done it before in the past, but did it differently and I think I like this way better.

  2. Beth says:

    Thank you – I’ve added it to my Favorites for future use : )

  3. Ray says:

    I built something very similar to this, and I want to change my report to display the columns order (not the sort order but vertical order of the columns). Is this possible in SSRS? I have been told no but still think there has to be a way.

    My dataset has the possibility of 302 columns, although realistically, any single user only uses 10-20. Some want Unit Name, Receive Dttm, Message Type, and others want Message Type, Unit Name, Receive Dttm (as an example). Can I accomplish this is SSRS within one report/rdl? I’m starting to think the solution is to build out functionality in my c# code to handle this not in SSRS. Which will require me to write a new windows service for report scheduling. So getting SSRS to do this for me is kind of a big deal. Please let me know if you have a solution for this.

  4. Pooja says:

    Your post really helped me save a lot of time to figure out the logic for visibility. Thanks so much!

  5. Mukesh Kumar says:

    SSRS – LookUp, MultiLookUp and LookupSet Functions
    These beautiful features bring a lot of satisfaction to report writer and developers to marge values from two datasets. These functions are is playing a very handy role to combine data from two datasets in a single data region which was not possible in earlier versions of Reporting Services.
    SSRS – LookUp, MultiLookUp and LookupSet Functions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s