Sunday 23 June 2013

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.


 

No comments:

Post a Comment