Regularly I have reports that have an extensive amount of columns.
Because the amount of columns, reports tend to become inefficient and have too much information we don’t always need. The users may want to select certain columns and to make the report easier to read.
Hiding and showing columns in SSRS reports using parameters is a solution to make reports more dynamic and easier to use.
At the time of writing of this article, SQL Server Reporting Services did not yet have the possibility to use checkbox parameters. Instead we will be using a multi-value text parameter to show or hide our columns.
The project file
For the demo I created a dummy project that looks to the AdventureWorks2014 database. I have created a data set that returns the addresses from the Person.Address table.
The report contains a table that shows the data from the address data set.
If you want to play around with the demo, the project file can be downloaded from here. Make sure you use Visual Studio 2017 or higher.
Create the parameter
For us to have the ability to hide or show columns we have to create a parameter. We want to be able to filter on multiple columns so we want to create a multi-value parameter.
To do that, either right-click on “Parameters” in your “Report Data” pane, or right-click in the top pane for the parameter and click on “Add Parameter”.
When you click “Add Parameter” you get a window that looks like this:
Give the parameter a name, in this case I named it “ColumnFilter”, but you can name it whatever you want.
Select “Text” as the data type ( you can choose any other data type but I like text) and make sure you select “Allow multiple values”.
For this example we’re going to hide or show the address fields. The data set has two address columns, AddressLine1, AddressLine2.
Go to the tab “Available Values” and add the following fields:
In this example we have two values in our parameter. The label and the value are pretty self explanatory.
We’re going to add the values to the “Default Values” tab to make sure our report shows the columns by default.
Click “OK” and we have our parameter.
Show and hide the columns
Now that we have our parameter we can change the table to hide or show the address columns.
Right click on the column “Address Line 1” and click on “Column Visibility”.
The following window will appear:
Click on the “Show or hide based …..”. Click on the “fx” button and the following window appears:
This window is really powerful. We can do a lot with expressions throughout our SSRS reports.
In this case we’re only going to enter a small line of code:
=IIF(Join(Parameters!ColumnFilter.Value, ",").Contains("address1"), False, True)
Expressions are a little different that you might be used to with normal programming. We are not able to search through our multi-valued parameters like we do with arrays in other programming languages (at least I have not found a way yet).
Instead we will be joining all the values from the ColumnFilter parameter and check if it contains one of the values we like to search for.
What the expression does is combine all the values separated with a comma (“,”). If all the values are selected we’ll have a string like this “address1,address2”.
The next part will look if the string contains one of the values. The “.Contains” method returns true if a value is within that string and false if it’s not.
That entire expression is contained within an IIF that assesses if the statement is true or not. In this case, when the string contains the value we look for, we want to return false to not hide the column.
Add the code to the Expression window and click on “OK”. Click “OK” again to close the column visibility window.
Do the same thing with the column “Address Line 2” but change the value in the expression to “address2”.
Executing the report
Now that we’ve done all the work. Open the report preview.
In this example I deselect the the “Address Line 2” value and click on “View Report”. The result is the same table but with the column “Address Line 2” hidden.
The result can be seen below:
Showing or hiding columns with parameters is a very useful way to make your reports more dynamic.
This is one way of doing this, I bet there are other ways of showing or hiding columns but these tend to work really well in my environment. The users are able to adjust their reports and I don’t have to create multiple reports to get to the same result.
I hope you enjoyed this post and comments are always appreciated.