Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Reporting Services: Adding extra columns / rows to a matrix

By Martin Cremer, (first published: 2008/07/24)

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:

CustomerName MonthName ArticleSold MonthID
Franz Beckenbauer Jan 2008 10 200801
Franz Beckenbauer Mar 2008 7 200803
Franz Beckenbauer Apr 2008 12 200804
Toni Schumacher Jan 2008 3 200801
Toni Schumacher Feb 2008 5 200802
...      

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:

rowSort rowDesc colSort colDesc value
1 Franz Beckenbauer 200801 Jan 2008 10
1 Franz Beckenbauer 200803 Mar 2008 7
1 Franz Beckenbauer 200804 Apr 2008 12
1 Franz Beckenbauer 300000 Total 29
1 Franz Beckenbauer 300010 Target 20
1 Franz Beckenbauer 300020 Delta 9
2 Toni Schumacher 200801 Jan 2008 3
...        

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.

Resources:

ExtendingMatrix.zip
Total article views: 24742 | Views in the last 30 days: 27
 
Related Articles
FORUM

Total Server Memory less than Target Server Memory

If total is less than target....

FORUM

Column and Row Total

Column and Row Total

FORUM

Why both "Target Server Memory" & "Total Server Memory" 0 ?

Why both "Target Server Memory" & "Total Server Memory" 0 ?

FORUM

"Total Server Memory" & "Target Server Memory" are very low

"Total Server Memory" & "Target Server Memory" are far below the physical memory

FORUM

Help with crosstab query

Crosstab query

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones