SQL Server: How to handle dynamic column report in SSRS (PIVOIT)

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.

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,

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.)




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




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.