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.
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
select dst.SalesTerritoryGroup, dd.CalendarYear, SUM(fis.SalesAmount) [SalesAmount] from FactInternetSales fis inner join DimDate dd on fis.OrderDateKey = dd.DateKey inner join DimSalesTerritory dst on fis.SalesTerritoryKey = dst.SalesTerritoryKey where dd.CalendarYear >= @CalendarYear group by dst.SalesTerritoryGroup, dd.CalendarYear order by 1,2
If I pass 2001 as a value for parameter @CalendarYear, the query will give the following output,
Pivoting the same information gives us the following view,
\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.)
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.
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
@CalendarYear = 2003
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.