Dynamic Column Report In SSRS (Multi value parameter)

Dynamic Column Report In SSRS Step By Step 

 

Dynamic Column Report In SSRS

I am showing you how to create Dynamic Column Report. It means when a user select two column the report will display only two columns and if they select five columns then it will display five columns. So we can say that the report will display the field on the basis of user selection.

For this we have to create two datasets.

Here is an example from Northwind Database.

Create first dataset named Hussain_Dataset.

SQL Scripts:

SELECT        CustomerID, CompanyName, ContactName, ContactTitle, Address, City, PostalCode, Country, Phone FROM  Customers

Insert a Table in design area and create a Table Like that: 

Now go to the dataset and create another dataset  Hussain_Dataset1

SQL Scripts:

SELECT        1 AS ID, 'Customer Id' AS CustomerID

UNION

SELECT        2 AS ID, 'Company Name' AS CompanyName

UNION

SELECT        3 AS ID, 'Contact Name' AS ContactName

UNION

SELECT        4 AS ID, 'Contact Title' AS ContactTitle

UNION

SELECT        5 AS ID, 'Address' AS Address

UNION

SELECT        6 AS ID, 'Postal Code' AS PostalCode

UNION

SELECT        7 AS ID, 'Country' AS Country

UNION

SELECT        8 AS ID, 'City' AS City

UNION

SELECT        9 AS ID, 'Phone' AS Phone

FROM            Customers

Now create a parameter DCParamaeter and click the Allow Multiple value.

Go to the Available Values and choose Get values from query and set dataset to Hussain_Dataset1 , Value Field CustomerID, Label Field CustomerID.

And Now the time to set the Column Visibility. For this we go to first column of the Tablix and right click after that we click on Column Visibility option.

A new popup box will appear. Here we choose Show Or Hide Based On Expression and click on fx button.

A new popup box will appear here we write the expression.

Expression :

=IIF(InStr(JOIN(Parameters!DCParameter.Value,","),"Customer Id")>0,False,True)

We have to perform this step to each column of Tablix.

(Note:Don’t forget to modify the expression for the respective column name accordingly.)

Your Dynamic Column report has completed. Now run the Report. Here is the some preview of your Dynamic Column Report.