How to handle dynamic column report in SSRS

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,

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

Regards

Amit Karkhanis

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook