01 February, 2010

How to: show dynamic columns in SQL server Reporting services

How to display dynamic columns in Reports?
Let me explain what I mean dynamic here. I have a stored procedure which return some columns depending on User choice. In that case I dont have any clue about the columns which are in the report.

In plain english, whatever dataset is coming from database, I need to display that information in a table or matrix. The dataset may contain 2columns or 25 columns. How to achieve this?
After so much search I found an interesting property in SQL Server Reporting services for all Fields. There will be a property 'IsMissing' for each Field in the Dataset we defined in the report. To achieve dynamic behavior, I Created a table with all possible columns and toggled the visibility of the columns depending on their IsMissing status.

For each column in the table there will be a Visibility Expression set on Hide Property. For Example, say the column is binded to a column 'Name' the details cell will have text Fields!Name.Value. The visibility expression for this column will be =IIF(Fields!Name.IsMissing,true,false). Now the report will show the columns selected in the stored procedure only.

One Important thing to remember: When we are binding a dynamic dataset, SSRS dont have capability to detect the Fields present in the dataset. For this we need to specify the all possible fields in the Dataset, To do this follow the steps:
  • Goto Data tab in the report and open the dataset which returns dynamic columns
  • Click on the ... button besides the dataset to edit the dataset
  • A pop-up window for dataset will appear, in that window go to Fields tab
  • There we have to write all possible fields in the Dataset
  • Click OK and Preview your report
(If there are so many fields in the dataset it is very difficult to enter each column in the tab manually. I did face this, One shortcut way to do this is write dataset query so that it return all the required columns. The fields will be automatically filled. Now open the .rdl file in any text editor and change the Query part to actual query which return dynamic columns)