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.
