Introduction

In Reporting Services (2005) it is very easy to create a crosstab (a so called matrix). However, if you want to add additional rows or columns, this is limited to the functionality of totals or subtotals. In this article I will describe how to add extra columns by using a stored function in the underlying dataset.

For this article, I will use a very simple database containing sales per month. The final report should show the sales per customer and month. Additional columns should show the target sales per customer and the delta (actual - target) per customer:

Built-In Functionality

The basic crosstab is very easy to create:

We are creating a new Report. The report's dataset is set to an SQL statement returning

• the identifier / name for the column
• the identifier / name for the row
• the value of the cell defined by column / row

In our case we have a simple statement:

SELECT Customers.CustomerName, Months.Monthname, 
  ArticleSales.ArticleSold, ArticleSales.MonthID
FROM ArticleSales INNER JOIN
 Customers ON ArticleSales.CustomerID = Customers.CustomerID 
 INNER JOIN
 Months ON ArticleSales.MonthID = Months.MonthID

This SQL statement retrieves the following values:

In the layout tab we create a matrix element with

• rows set to =Fields!CustomerName.Value
• columns set to =Fields!Monthname.Value
• value set to =Sum(Fields!ArticleSold.Value)

Now the simple matrix is there. The columns are sorted alphabetically - which is not what we want, of course. Therefore, we edit the sorting of the column group and set it to =Fields!MonthID.Value.

With this approach we have our first result:

Adding Totals

By right-clicking in the cells with MonthName and CustomerName you open a context menu where you can add a subtotal. So we see, that with this standard approach the totals are computed in the layout of the report and not in the dataset. (The underlying dataset was not changed)

If you want to format the total row or column, click on the green triangle. In my example I formatted the totals in bold.

Our second example looks like:

How to get additional columns

Unfortunately the matrix control is very limited and we cannot add another column holding the target values per customer.

Therefore we have to use a different approach: The idea is to set up a stored function in the SQL Server which retrieves all the columns and rows including the target column and all the computed columns (delta and total as well).

First step: stored function

The stored function is of type "table-valued function". WIth right-clicking on Programmability > Functions > New > Multi-Statement Table-valued Function ... the SQL Server Management Studio opens a nice template (which is out of scope of this article).

In the first step, we want to use our function to create a report as in Example 1 (just the crosstab, no totals or computations):

CREATE FUNCTION dbo.createReportAsExample1 ()
RETURNS 
@CrossTab TABLE 
(
 rowSort int, 
 rowDesc nvarchar(50),
 colSort int,
 colDesc nvarchar(50),
 value int
)
AS
BEGIN
 /* basic crosstable data */
 INSERT INTO @CrossTab (rowSort, rowDesc, colSort, colDesc, value)
 SELECT ArticleSales.CustomerID, Customers.CustomerName, 
   ArticleSales.MonthID, Months.Monthname, 
   ArticleSales.ArticleSold 
 FROM ArticleSales INNER JOIN
 Customers ON ArticleSales.CustomerID = Customers.CustomerID 
 INNER JOIN
 Months ON ArticleSales.MonthID = Months.MonthID

 RETURN 
END
GO

The function retrieves a table which holds the following information:

• rowDesc: The name which should be displayed in the row (in our case the customer name)
• rowSort: The sort value in order to sort the rows properly (in our case the customer id)
• colDesc: The name which should be displayed in the column (in our case month name)
• colSort: The sort value in order to sort the columns properly (in our case the month id)
• value: The data value to be shown in the cell defined by (row/column)

The building of the report is straight forward:

As dataset use select * from dbo.createReportAsExample1 ()

In the layout, use a matrix element with

• rows set to =Fields!rowDesc.Value
• columns set to =Fields!colDesc.Value
• value set to =Sum(Fields!value.Value)
• the sorting of the column group changed to =Fields!rowSort.Value
• the sorting of the row group changed to =Fields!colSort.Value

With this we have only achieved the functionality of example 1, but in such way that we can easily expand it.

Second Step: extend the stored function with target value and computations

Add an extra column

When we want to add the target value to the matrix, we simply need to add records to the @CrossTab table for a colDesc='Target'. The colSort - column needs to hold a value which is bigger than all the other colSorts used until now, in order to see this column as rightmost column.

This can be easily achieved by adding the following code to the stored function:

 /* add target column */
 INSERT INTO @CrossTab (rowSort, rowDesc, colSort, colDesc, value)
 SELECT ArticleSalesTarget.CustomerID, Customers.CustomerName, 
   300010, 'Target', Target
 FROM ArticleSalesTarget INNER JOIN Customers ON 
   ArticleSalesTarget.CustomerID = Customers.CustomerID

As you can see, rowSort and rowDesc are set to the same values as before. This makes the target values and the actual values (of step 1) show up in the same rows.

Totals

With this method, the built-in functionality of totals / subtotals cannot be used any more, because this would add up all the values in the dataset and not only the actual values.

Therefore we have to add the totals manually as part of our stored function. (So in contrast to example2, where the totals were computed in the layout, here the totals are computed in the database.)

We are doing our computations in the stored function one after the other. Every computation is an INSERT into the @CrossTab-table. Therefore the second computation can use the result of the first computation and so on. Here are all the computations:

1. fill the actual values per month (as in step 1)
2. add the "totals"-column: for each row sum up the columns (you can use a query only using a SELECT from the @CrossTab table)
3. add the "target"-column: read the target from the appropriate table
4. add the "delta" column which - for each customer - subtracts the target column from the totals column (see next paragraph)
5. add the "totals"-row by summing up all values of the @CrossTab-table per column

Henceforth the stored functions will retrieve the following values:

Especially to be noticed is the column colDesc with the following values:

• Jan 2008 ... May 2008
• Total
• Target
• Delta

This is in contrast to the built-in functionality (see example 2) where we only retrieved Jan 2008 ... May 2008 as column values from the database.

The Delta-column

Since we are doing our computations on the data and not on the layout, we need to be careful that we fill the data for all rows where it is needed.

One good example is the Delta column. For every row of the final table we need to subtract Actual - Target. Since there can be customers with no actual values but target values and vice versa, we need to use a full outer join. Otherwise we might end up with rows in our final table which do not have the delta column filled. (See the final code down there)

Our final code for the stored function looks like:

CREATE FUNCTION dbo.createReportExample3 ()
RETURNS 
@CrossTab TABLE 
(
 rowSort int, 
 rowDesc nvarchar(50),
 colSort int,
 colDesc nvarchar(50),
 value int
)
AS
BEGIN
 /* basic crosstable data */
 INSERT INTO @CrossTab (rowSort, rowDesc, colSort, colDesc, value)
 SELECT ArticleSales.CustomerID, Customers.CustomerName, ArticleSales.MonthID, Months.Monthname, ArticleSales.ArticleSold 
 FROM ArticleSales INNER JOIN
 Customers ON ArticleSales.CustomerID = Customers.CustomerID INNER JOIN
 Months ON ArticleSales.MonthID = Months.MonthID

 /* add total column */
 INSERT INTO @CrossTab (rowSort, rowDesc, colSort, colDesc, value)
 SELECT rowSort, rowDesc, 300000 as colSort, 'Total' as colDesc, sum(value)
 FROM @CrossTab 
 Group by rowSort, rowDesc

 /* add target column */
 INSERT INTO @CrossTab (rowSort, rowDesc, colSort, colDesc, value)
 SELECT ArticleSalesTarget.CustomerID, Customers.CustomerName, 300010, 'Target', Target
 FROM ArticleSalesTarget INNER JOIN Customers ON ArticleSalesTarget.CustomerID = Customers.CustomerID

 /* add delta column */
 INSERT INTO @CrossTab (rowSort, rowDesc, colSort, colDesc, value)
 SELECT isnull(total.rowSort, target.rowSort), isnull(total.rowDesc, target.rowDesc), 300020 as colSort, 'Delta' as colDesc, isnull(total.value, 0) - isnull(target.value, 0)
 FROM (SELECT * FROM @CrossTab where colSort = 300000 ) as total
 FULL OUTER JOIN 
 (SELECT * FROM @CrossTab where colSort = 300010 ) as target ON total.rowSort = target.rowSort

 /* add total row*/
 INSERT INTO @CrossTab (rowSort, rowDesc, colSort, colDesc, value)
 SELECT 1000 as rowSort, 'Total' as rowDesc, colSort, colDesc, sum(value)
 FROM @CrossTab 
 Group by colSort, colDesc

 RETURN 
END

With this, we have almost finished the report shown in the introduction. The only thing left is to make some columns bold.

Formatting the total columns / rows

Since we do not use the built-in subtotals-feature there is only one column holding all the data in the layout. Therefore this column needs to be formatted according to its contents.

Henceforth we need to use an expression in the Font.FontWeight-Property of the data-cell:

=iif(Fields!colDesc.Value="Total" 
or Fields!rowDesc.Value="Total" 
or Fields!colDesc.Value="Delta", "Bold", "Normal")

WIth this we finished our task.

Advantages / Disadvantages of this solution

Disadvantages

The formatting has become a little more difficult and less straight-forward - as I showed in the last paragraph.

With the solution shown here we have moved the coding into the data source and the computation is not done in the layout. Especially if we have column-based computation (such as the delta column := column "Totals" - column "Target") it would be nicer to be able to do this in the layout.

Advantages

First of all I do not see any other way to achieve the same result. Tricks with two tables next to each other and so on do not really work (especially when thinking of exports to Excel etc.)

Since we are using stored functions for gathering the data we have full flexibility of T-SQL which seems to be without limits (at least, limits are far away) and the knowledge of T-SQL is wide-spread.

Furthermore this solution can be extended to show data from different SELECTs in one reporting table.

To go even further, this solution can be extended to show data from different data sources (such as Analysis Services and SQL Server) by using Linked Servers.

Some words on the examples

Of course, these examples are fictional and only set up for giving a good example. In real life several changes would take place:

• the SQL code might be streamlined (for example customer ID and name are used redundantly and could be updated in a last SQL statement)
• The example does not use parameters used. On the other hand, they can be easily added.
• I used an example with only one group on the columns / rows. Of course this can be easily extented to use nested groups as well. (Just add col2Sort, col2Desc, col3Sort, col3Desc to the @CrossTab in the Stored function)

If you do not want to type all the examples, you can download them here (including the Reporting Services solution and the SQL Statements to create the structure and load some example data)

Where to go from here?

In the project where we actually needed this feature, our data was not lying in SQL Server relational database but in Analysis Services. I will show in one of my next articles how we retrieved the data from Analysis Services using a similar approach.