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.
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
SELECT 1 AS ID, 'Customer Id' AS CustomerID
SELECT 2 AS ID, 'Company Name' AS CompanyName
SELECT 3 AS ID, 'Contact Name' AS ContactName
SELECT 4 AS ID, 'Contact Title' AS ContactTitle
SELECT 5 AS ID, 'Address' AS Address
SELECT 6 AS ID, 'Postal Code' AS PostalCode
SELECT 7 AS ID, 'Country' AS Country
SELECT 8 AS ID, 'City' AS City
SELECT 9 AS ID, 'Phone' AS Phone
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.
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.