Creating a Table Report with Dynamic Columns

Over time, similar reports may be designed from variations of common queries and datasets.  Creating one report to meet all these requirements can minimize development time and maintenance overhead.  By evaluating existing reports and making modifications, future requirements can be met without creating yet another report.  Parameters and expressions may be used to dynamically sort and group data.  By hiding and showing table columns, a report can accommodate different queries that return similar but different sets of fields. 

The following example report contains a table data region that will display different columns when presented with different query results.  In this scenario, three different sets of requirements are identified:

Requirement set 1

Group on: Category, Subcategory

Aggregate: SalesAmt, TaxAmt, FrieghtAmt

Requirement set 2

Group on: Category, Subcategory

Aggregate: SalesAmt, FrieghtAmt

Requirement set 3

Group on: Category, Product

Aggregate: SalesAmt, TaxAmt

Three separate queries can be presented to a report using expressions and parameters – using a few different techniques.  A parameter can return an entire SELECT statement to an expression, the query can be modified in an expression or custom code function or conditional logic may be used to change the name of a stored procedure or view stored in the database.  For this example to be portable, my report will use a parameter to pass in one of three separate SELECT statements.

As you can see, there are six possible fields in various combinations.  Normally a query would be written to return the specific fields, a table data region would be added to the report and then groups would be designed for only the two fields defined for that report.  When the group fields (say Category and Subcategory) are dragged into the Row Groups pane in the report designer, two groups are defined and two group columns are added in the group header area of the table.

Designing the Report

We start with a query that returns all six columns.  When this query is executed the first time, the report designer adds all six fields to the dataset fields collection in the report definition.

SELECT    
  c.EnglishProductCategoryName Category
, s.EnglishProductSubcategoryName Subcategory
, p.EnglishProductName Product
, SUM(f.SalesAmount) SalesAmt
, SUM(f.TaxAmt) TaxAmt
, SUM(f.Freight) FrieghtAmt
FROM
    DimProductCategory c
    INNER JOIN DimProductSubcategory s
    ON c.ProductCategoryKey = s.ProductCategoryKey
    INNER JOIN DimProduct p
    ON s.ProductSubcategoryKey = p.ProductSubcategoryKey
    INNER JOIN FactInternetSales f
    ON p.ProductKey = f.ProductKey
GROUP BY
  c.EnglishProductCategoryName
, s.EnglishProductSubcategoryName
, p.EnglishProductName

Next, add a table to the report body, select the table and then drag the first three fields to the Row Groups pane.  Drag the three measure fields (the SalesAmt, TaxAmt and FrieghtAmt numeric fields that should be summed at the group levels) into the three data cells to the right of the groups and set the Format to Currency for these cells.

This is a pretty typical table report design so far.

Here’s where we change things up a bit.  You cannot hide group header columns.  If you look carefully at the table in design view, you’ll see that there is a double-dashed line separating the group header column cells form the data cells (between Product and SalesAmt).  This indicates that the static cells on the left will render one cell per distinct group value and the dynamic cells on the right will render one cell per value on detail rows.  If any total row headers or footers are added to the design or if the groups are setup to drill-down, the dynamic cells will also return aggregate totals for their respective group ranges.  By definition, this is the difference in behavior for static and dynamic cells.

Problem: a column of static cells cannot be hidden

Solution: group header values don’t have to use static cells

Drag and drop the three group fields (Category, Subcategory and Product) into new cells on the right side of the double-dashed separator line and then delete the three original group header columns.  A dialog box will ask if I want to remove the groups or just the columns.  We want to delete the columns and leave the groups intact.

At this point the table will repeat the non-distinct group field values.  This is remedied by setting the textboxes in these cells to hide duplicate values within their respective groups.  For each cell, I use the drop-down list in the HideDuplicates property to select the name of that field’s group.  If you run the report it looks like a grouped table with non-repeating header columns.

Add a parameter to the report named pQuery and add three available values.  Each value will be a complete SELECT statement that returns a different combination of the same fields.

Available value 1:

Label: Sales, Tax and Freight by Subcategory

SELECT
   c.EnglishProductCategoryName Category
, s.EnglishProductSubcategoryName Subcategory
, SUM(f.SalesAmount) SalesAmt
, SUM(f.TaxAmt) TaxAmt
, SUM(f.Freight) FrieghtAmt
FROM
   DimProductCategory c
   INNER JOIN DimProductSubcategory s
   ON c.ProductCategoryKey = s.ProductCategoryKey
   INNER JOIN DimProduct p
   ON s.ProductSubcategoryKey = p.ProductSubcategoryKey
   INNER JOIN FactInternetSales f
   ON p.ProductKey = f.ProductKey
GROUP BY
    c.EnglishProductCategoryName
  , s.EnglishProductSubcategoryName

Available value 2:

Label: Sales and Freight by Subcategory

SELECT
    c.EnglishProductCategoryName Category
  , s.EnglishProductSubcategoryName Subcategory
  , SUM(f.SalesAmount) SalesAmt
  , SUM(f.Freight) FrieghtAmt
FROM
   DimProductCategory c 
   INNER JOIN DimProductSubcategory s
   ON c.ProductCategoryKey = s.ProductCategoryKey
   INNER JOIN DimProduct p
   ON s.ProductSubcategoryKey = p.ProductSubcategoryKey
   INNER JOIN FactInternetSales f
   ON p.ProductKey = f.ProductKey
GROUP BY
    c.EnglishProductCategoryName
  , s.EnglishProductSubcategoryName

Available value 3:

Label: Sales, Tax and Freight by Product

SELECT
    c.EnglishProductCategoryName Category
  , p.EnglishProductName Product
  , SUM(f.SalesAmount) SalesAmt
  , SUM(f.TaxAmt) TaxAmt
FROM
   DimProductCategory c
   INNER JOIN DimProductSubcategory s
   ON c.ProductCategoryKey = s.ProductCategoryKey
   INNER JOIN DimProduct p
   ON s.ProductSubcategoryKey = p.ProductSubcategoryKey
   INNER JOIN FactInternetSales f
   ON p.ProductKey = f.ProductKey
GROUP BY
     c.EnglishProductCategoryName
   , p.EnglishProductName

As you can see, each of these queries returns a different combination of fields but every one of them were present in the original query that was used to create the fields that are still part of the report definition.

We can feed a selected query string to the dataset by using an expression in place of the query.  Open the dataset properties and use the expression builder.  Delete the entire query text and replace it with this simple expression:

=Parameters!pQuery.Value

Click on the table in the designer.  This will show the row and column selection handles.  For each column, repeat the following steps:

Right-click the column header and choose Column Visibility…

In the Column Visibility dialog, select the radio button labeled Show or hide based on an expression.

Use the expression builder to create an expression that hides the column if that column is missing from the dataset result set:

=Fields!Subcategory.IsMissing

That’s it!  Switching to preview shows that the we now have a report that is completely flexible.  Selecting any one of the three parameters will cause the report to show only the columns returned by the corresponding query.

Download sample report