Dynamically Adding and Removing Columns to a Query and Report

Creating one report to meet multiple business requirements can save a lot of work down the road. A common scenario is for different users to ask for similar reporting metrics that may just be variations of existing reports. Using SQL Server Reporting Services, you can achieve this goal with a little planning and creative report design.

The following technique in Reporting Services for SQL Server 2008 uses a parameterized expression to modify a dataset so that it returns a different set of columns. Conditional logic in the report is used to include only columns in a table which correspond to the columns returned by the query. The same technique will work in earlier versions of Reporting Services but the designer user interface will be a little different.

This example uses a parameter to change the stored procedure that will be executed to return the report data. Conditional query logic could also be applied to generate a dynamic SQL or MDX statement.

To begin, use SQL Server Management Studio to connect to the AdventureWorksDW2008 sample database and then execute the following script to create two new stored procedures:

create proc spResellerSales1
    @DateFrom    int,
    @DateTo    int
as
    select
        d.CalendarYear, d.CalendarQuarter, d.EnglishMonthName as Month
        , sum(fs.OrderQuantity) as OrderQuantity
        , sum(fs.SalesAmount) as SalesAmount
    from
        FactResellerSales fs inner join DimDate d on fs.OrderDateKey = d.DateKey
        inner join DimProduct p on fs.ProductKey = p.ProductKey
        inner join DimProductSubcategory ps
on p.ProductSubcategoryKey = ps.ProductSubcategoryKey
        inner join DimProductCategory pc
on ps.ProductCategoryKey = pc.ProductCategoryKey
    where d.DateKey between @DateFrom and @DateTo
    group by d.CalendarYear, d.CalendarQuarter, d.EnglishMonthName
, d.MonthNumberOfYear
    order by d.CalendarYear, d.CalendarQuarter, d.MonthNumberOfYear
;
go
—————————-
create proc spResellerSales2
    @DateFrom    int,
    @DateTo    int
as
    select
        d.CalendarYear, d.EnglishMonthName as Month
        , sum(fs.OrderQuantity) as OrderQuantity
, sum(fs.SalesAmount) as SalesAmount
    from
        FactResellerSales fs inner join DimDate d on fs.OrderDateKey = d.DateKey
        inner join DimProduct p on fs.ProductKey = p.ProductKey
        inner join DimProductSubcategory ps
on p.ProductSubcategoryKey = ps.ProductSubcategoryKey
        inner join DimProductCategory pc
on ps.ProductCategoryKey = pc.ProductCategoryKey
    where d.DateKey between @DateFrom and @DateTo
    group by d.CalendarYear, d.EnglishMonthName, d.MonthNumberOfYear
    order by d.CalendarYear, d.MonthNumberOfYear
;
go

Note that the only significant difference between the spResellerSales1 and spResellerSales2 stored procedures is that the first one returns the CalendarQuarter column and the second one does not. Remember that this is a simple example for the sake of demonstration. You could have as many different procedures or query variations as you like as long as one of them returns all possible columns and the others return a subset of columns in the same order.

The report contains parameters, two that will be automatically generated from the DateFrom and DateTo parameters in the procedure, and another parameter named DataSource that I added myself. Note that I’ve provided default values for the DateFrom and DateTo parameters since they’re really not part of the demonstration scenario.

To populate the dataset Fields collection, configure the dataset to use a stored procedure and select the first procedure (the one that returns all of the columns) as you normally would without using an expression. The DataSource parameter (not to be confused with a report data source) is use to pass and swap the stored procedure names to the report’s main dataset. Manually add this parameter to the report:

The available values for this parameter include the two stored procedure names:

This parameter is referenced in the report’s main dataset using the expression: =Parameters!DataSource.Value

A table is added to the report and each dataset field is added as a column to the table:

Since the CalendarQuarter column may not be available when the spResellerSales2 stored procedure is used in the parameterized dataset, this column must be hidden when the column is not returned in the result set. This is accomplished by changing the Hidden property of the table column. Right-click the column header for the Calendar Quarter column and choose Column Visibility… from the menu. Choose Show or hide based on an expression and then click the expression button to use the Expression Builder to create the following expression for this property: =Fields!CalendarQuarter.IsMissing.

Remember that there is a difference between a report dataset and the query that it references. When a query is written, (or in this case, when a stored procedure is selected) field definitions are added to the dataset object and will be there whether the query returns a column or not. If that query doesn’t return a corresponding column for the field, the field’s IsMissing property returns True.

You’re all done!

Preview the report and choose the first stored procedure in the parameter list. The report returns columns for all fields, including the Calendar Quarter:

Change the Data Source parameter to use the second stored procedure and click the View report button on the toolbar. Now the report returns columns for all fields except the Calendar Quarter:

A copy of this sample SSRS 2008 report is included for download.

 Dynamic Columns.rdl (351.00 bytes)