Wednesday, January 11, 2012

SSRS Dynamic Reports...

Recently I was participating in a training class where we were working on SSRS and explaining features to developers who were new to SSRS. A developer raised question, how can one generate dynamic reports in SSRS, on further discussion the scenario was to have a datagrid like control in SSRS. In the .NET framework one can use datagrid and bind it to a Stored procedure and generate dynamic reports. In SSRS one uses dataset and a table/matrix control to build reports. I decided to breakdown the question into different types of dynamic reports in SSRS. The first type of dynamic report is using Matrix Control in SSRS, where in there is a column defined in the Matrix report which can take multiple values. For example take the case of metrics across different calendar years from 2008 to 2011. The matrix report would look like this.

When the year is passed as a parameter the year columns would be dynamic based on the criteria. For example when 2008 is passed as a year then display only 2008, when 2009 is passed as a year display both 2008 and 2009.
In case of a Matrix report the report is dynamic in terms of the values available for a column. Moving on to other type of dynamic reports where the columns itself are dynamic, the approach has to be changed. One of the strategies is to use a table driven approach where in there is a table called tblreportcolumns in the sqlserver database which holds all the possible columns that could be generated for a report. Here let us say we have a stored procedure say usp_gendynamicreport which takes in a parameter, based on different parameter values different columns are returned. On the SSRS side one has to use expressions to show and hide the required columns based on the parameter passed to the stored procedure.
The expression for each column would be something to the effect like:
//For First Column

=IIF(InStr( Fields!NumRows.Value,"a")=0,True,False)
//For Second Column
=IIF(InStr( Fields!NumRows.Value,"b")=0,True,False)
For more details on this approach, one can get to this article:

The next approach to creating dynamic reports is to use the approach described in the following link, here the dynamic reports are created using C#/.NET framework, this approach gives a pretty sophisticated dynamic report solution.

No comments:

Post a Comment