Pages

Saturday 2 February 2013

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.










3 comments:

  1. HI i am venkatesh ,
    i am also follow same thing,its working fine but if i select 1 and 3 columns means its coming gap between 1 and 3 columns.. How can remove the gap..
    please help me its urgent

    ReplyDelete
  2. I Found this Error.
    please give me a solution ASAP.

    The Hidden expression for the tablix ‘Tablix1’ refers to a non-existing report parameter ‘DCParameter’. Letters in the names of parameters must use the correct case.
    ----------------------------
    The definition of the report 'Main Report' is invalid.
    ----------------------------
    An error occurred during local report processing.

    ReplyDelete
  3. Hi Bansi ,

    Instead of 'DCParameter' give the parameter name that you have created and also use the same name is expression .

    ReplyDelete