Sunday, October 17, 2010

Displaying Dynamic Columns in SSRS Report

Problem: How to display selected columns dynamically in SSRS reports.
Example: A report contains more than 30 fields. Some users want to see only 5 fields, some users 10 fields, and other may want to see 20 fields.
Solution: Add a Report Parameter having the values as the name of all the fields of dataset. Now set hidden expression for each column of the tabular report.

Here is the solution with an example:

STEP1:
Create a report with required dataset. Drag and drop table control and select dataset fields.
In my example, I have following fields in the dataset: Year, Quarter, Month, Date, Product Name, Customer Name, Sales Region, Sales Country, Order Number, Sales Amount.

STEP2:
Create a dataset dsColumns using below query:
SELECT 1 ID, 'Year' AS ColumnName UNION
SELECT 2 ID, 'Quarter' AS ColumnName UNION
SELECT 3 ID, 'Month' AS ColumnName UNION
SELECT 4 ID, 'Date' AS ColumnName UNION
SELECT 5 ID, 'Product Name' AS ColumnName UNION
SELECT 6 ID, 'Customer Name' AS ColumnName UNION
SELECT 7 ID, 'Sales Region' AS ColumnName UNION
SELECT 8 ID, 'Sales Country' AS ColumnName UNION
SELECT 9 ID, 'Order Number' AS ColumnName UNION
SELECT 10 ID,'Sales Amount' AS ColumnName


STEP3:
Create a new parameter with name pDisplayFields and Promt Display Columns as shown below:
In Available Values of Report Parameter Properties wizard, select Get values from a query, select dsColumns in Dataset, ColumName in value field and label field.

In Default Values of Report Parameter Properties wizard, select Get values from a query, select dsColumns in Dataset, ColumName in value field.

STEP4:
Now you have to set the expression to display the colummns which are selected in the pDisplayColumn parameter. Right click on First Column (Year in my example) and click Column Visibility...
Write following expression in Show or hide based on an expression of Column Visibility wizard:
=IIF(InStr(JOIN(Parameters!pDisplayFields.Value,","),"Year")>0,False,True)

Now repeat this expression for all the columns by modify the expression for the respective column name accordingly.

Thats all. Now preview the report. You will see all the columns by default.



Now select required columns in Display Column parameter to modify the report layout at run time.


Cheers!!!

23 comments:

  1. In our situation, we had a table that contained 1) ID 2) column name as displayed in the report 3) column name as it is in the table 4) table name. We built an SP with dynamic query which takes the selected column IDs as comma separated string parameter and based on the selected parameters query was built dynamically. This way, the data will be filtered while building the data set itself and will reduce lot of processing on the report server end as well as reduce lot of data traffic over network. We used a matrix report to display the column names and data from the data set.

    ReplyDelete
  2. Thanks Kiran for the great comment. I had implemented the logic you mentioned couple of years back. Also I am planning to post this in my next article.

    However there are certain things we should consider while choosing appropriate approach:
    1. What if source is CUBE instead of SQL Server
    2. For dynamic sql we need to make sure the query optimizer is generating and using the optimal query plan.
    3. For heavy report we should use cached report to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports. You may not be able to use cached features in case of Dynamic SQL.

    ReplyDelete
  3. Hi Hari - I'm trying to create a report showing an area chart based on some dynamic sql that creates a pivot statement. The problem is I don't know how many columns there will be in the data set. I start with 3 columns in my result set and then the rest are built dynamically. Hence all I see in BIDS is a 3 column table. Do you have any ideas as to how this might be accomplished?

    ReplyDelete
  4. Hi Hari,
    I used an alternative form of the hide column expression when dealing with the multivalue parameter.

    =IIF(Filter(Parameters!MultiOption.Value,"6",true).Length>0,False,True)

    Which means to show this column if it's selected in the parameter list. In this example, 6 is the id of the label.

    I had such a small dataset, so going to the trouble of tailoring the query was not important.

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Hi Hari,
    I have a stored proc which returns different number of columns based on some condition selected everytime(depends user input). How to build dynamic columns to map the dataset returned in SSRS?

    ReplyDelete
  8. This comment has been removed by the author.

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. There is one issue with your expression: it will return extra columns if you have another with a name that is a substring of your test column name. For instance, if you are testing a column called ""Fiscal Year", and your table has columns named "Year" and "Fiscal Year", both would be returned.

    ReplyDelete
  11. 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
  12. HI Kabu
    My problem is related by quite different, I have a pivoted report that needs the report user to select a date range, the column headers gets generated based on the wideness of the date range like [Jan 2013], [Feb 2013] etc. these headers get generated only at run time by the proc. how do capture these in ssrs. thanks

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. What I have written above is just confusion, I would like to delete it. Your tip works great! Thank you Hari Sharma!

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. this expression not working on ssrs 2008 . please help me .

    =IIF(InStr(JOIN(Parameters!pDisplayFields.Value,","),"Year")>0,False,True)

    ReplyDelete
  17. Thank you very much. You saved my time very much.

    ReplyDelete
  18. how to add total field in dynamic column please provide me the code

    ReplyDelete
  19. This comment has been removed by the author.

    ReplyDelete
  20. This comment has been removed by the author.

    ReplyDelete
  21. Thank you Hari, a great option!
    In my report I called the parameter "Display additional fields" because I want to have the first 4 columns always visible, and then the rest of them as optional. So I listed in the dataset only the optional ones and set the visibility condition just for those fields. The thing is the parameter doesn't allow blanks, so it's not returning the mandatory 4 columns if I don't choose any of the additional fields. Any suggestions to do this?

    ReplyDelete

Here are few FREE resources you may find helpful.