Problem

I want to have a cross-tab report in SSRS whose columns may very depending upon the parameter values. We will check how this can be designed in SSRS.

Solution

Consider the following query which returns the sales data by territory and year. The query can be run on the AdventureWorksDW database in SQL 2005 / 2008

If I pass 2001 as a value for parameter @CalendarYear, the query will give the following output,

1_SQL_Server_How_to_handle_dynamic_column_report_in_SSRS

Pivoting the same information gives us the following view,

SalesTerritoryGroup 2001 2002 2003 2004
Europe 709947.202 1627759.715 3382979.267 3209356.08
North America 1247379.26 2748298.928 3374296.817 3997659.37
Pacific 1309047.2 2154284.884 3033784.213 2563884.29

\As @CalendarYear is kept as a parameter, changing the value will change the number of output columns in the pivot view. Meaning 2001 will return four columns (2001, 2002, 2003, 2004), 2002 will return three columns (2002, 2003, 2004) and so on. Now how to handle this dynamic column report using SSRS?

The solution is to use the Matrix Control to present the data. Matrix control helps in pivoting the data to create a cross-tab report within no time. It has three areas, Rows, Columns and Data. Just we have to drag and drop the required fields into Rows, Columns and Data from the dataset field list. (Make sure that the raw data is in the un-pivot form and SSRS will pivot it for you.)

2_SQL_Server_How_to_handle_dynamic_column_report_in_SSRS

Consider the above query which returns three columns i.e. SalesTerritoryGroup, CalendarYear and SalesAmount. Drag and drop the SalesTerritoryGroup field in the Row Group, CalendarYear field in the Column Group and Sales Amount field in the Data area.

3_SQL_Server_How_to_handle_dynamic_column_report_in_SSRS

As I have mentioned @CalendarYear in the query, SSRS will automatically create a parameter for this. Now preview the report and see the magic :)

@CalendarYear = 2001

4_SQL_Server_How_to_handle_dynamic_column_report_in_SSRS

@CalendarYear = 2003

5_SQL_Server_How_to_handle_dynamic_column_report_in_SSRS

Conclusion

SSRS enables to create cross-tab reports with dynamic columns without any efforts. This works both in SSRS 2005 and 2008. You can further try the chart control which also just takes the series data and display the graphs on the fly.

 

Regards

Amit Karkhanis

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook