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,

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

   

6 Comments on “How to handle dynamic column report in SSRS”

  1. Hi ,

    I followed the steps as mentioned above.Didn’t get dynamic columns. Is there any settings to change.

    Thanks,

    Sree.

  2. Hi Shree, no setting changes. The report in the above example is just using the Matrix control to pivot the data and present it in a cross tab reporting format. Just check whether your scenario matches with this one.

  3. You have calculate the sum of the totals by writting the (sum[SalesAmount])

    how abt substracting the current year – previous year. How do we do that, there is no such option of (Sub[SalesAmount])

  4. Hi Amit,

    It is so helpful.

    I have other requirement like another column besides these columns like as below

    2013-09-30 2013-08-31 2013-07-31 2013-06-30 ……. 2012-09-30 Percentage Diff

    Group1 Group2 $1000 $1900 $2000 $5000 …….. $2200 $1000(2013-09- 30)/$5000(2013-06-30)

    All the above dates are dynamically populated…

    Could you please advise on this.

    Appreciate all and any help.

    Thanks,

    Divya

Leave a Reply

Your email address will not be published.