Adding Column Groups into Tables

Andrew Smith Posted on

Whilst it’s tempting to think of an SSRS tablix as being used for either a conventional table or for a pivot table, it is possible to set one up in such as way as to mix both these behaviours.

In the AdventureWorksDW database, each sales order record can have many sales reasons associated with it, such that a single sales order line can become multiple lines when the sales transactions fact table is joined with the sales reasons fact table, e.g.:

tablixbaserows

If a report is required where all the sales reasons for an order line must appear on the same row, then we can do this, by starting with a table in SSRS with a similar layout to the above, but where a column group is added to represent the 4th column. So the final report looks like:

tablixfinalreport

The design layout to produce this is quite simple:

tablixdesignlayout

The column group seen here just groups on the SalesReasonName field, and then I use the following expression in the tablix cell being grouped to convert the field value names to “Yes” or nothing (since the column headings suffice):
=IIF(IsNothing(Fields!SalesReasonName.Value), Nothing, “Yes”)

The expression in the column group tablix heading is essentially Fields!SalesReasonName.Value, but where I use a nested IIF() statement to abbreviate some of the longer promotion names.

The row group that can be seen started off as the default details group, but I then changed this to group on fields that uniquely identify a row. So it groups by this expression:
=Fields!SalesOrderNumber.Value & “_” & Fields!SalesOrderLineNumber.Value

So the column group and the row group combined give us a single row for each sales line number, with columns for all the possible sales reasons.

An obvious concern with using column groups like this is that for reports being exported to rendering formats such as PDF or TIFF, the columns could expand off the side of the page, either initially or at some time in the future if more sales reasons are added. So this needs to be considered up front, but in cases where the range of column group values is small and unlikely to change, this is a viable option.

As an aside, pivoting on the sales reason name could be done in SQL via the PIVOT operator, if SQL Server is being used as the data source. E.g. this query:

WITH PivotData AS
(
SELECT FullDateAlternateKey, FIS.SalesOrderNumber, FIS.SalesOrderLineNumber, SalesReasonName
FROM FactInternetSales AS FIS
INNER JOIN DimDate AS D ON FIS.OrderDateKey = D.DateKey
INNER JOIN FactInternetSalesReason AS FISR ON FIS.SalesOrderNumber = FISR.SalesOrderNumber AND FIS.SalesOrderLineNumber = FISR.SalesOrderLineNumber
INNER JOIN DimSalesReason AS SR ON FISR.SalesReasonKey = SR.SalesReasonKey
AND FullDateAlternateKey = DATEFROMPARTS(2008,05,15)
)
SELECT FullDateAlternateKey, SalesOrderNumber, SalesOrderLineNumber, [On Promotion], Other, Price, Review, [TV Advertisement]
FROM PivotData
PIVOT(MIN(SalesReasonName) FOR SalesReasonName IN ([On Promotion], Other, Price, Review, [TV Advertisement])) AS P

produces this table:

tablixdesignpivot

The drawback with this approach though is that all possible sales order reasons for now and ever more must be known at the time of writing the query, since they are hard-coded into it. So doing the pivoting via SSRS remains the preferred option.