Practical Business Intelligence

In a Reporting Services tablix (or matrix in Reporting Services 2005), it's common to group on a time period such as Year in order to show annual totals. This gives you an easy comparison of figures, such as in the image on the left. This is a valid and useful visualisation of numbers.

However, it doesn't easily enable users to see the percentage variance between the two members of the Year group. If you need to see the amount by which your figures have changed, you need to have it calculated. This can then be displayed next to the annual totals as in the image on the right.

A simple way might be to have the calculation done in your table's data source and displayed as FIRST(Field) rather than SUM(Field). This will work if your query is at the same grouping as your matrix, but because percentages can't be summed, or averaged, or any other type of aggregation to get values at a subtotal or total level, you can't have a total line or change the grouping.

You are left with using an expression in the report. The formula itself is very simple:

(([This Year]-[Last Year])/[Last Year]) * 100

The problem is that there is no scope which lets you grab the value of one group member - only for the group as a whole or the entire table.

The solution is to put the values you need into variables which can then be used in expressions in the report itself. Two calculated fields are needed to achieve it. They are not added to the query but as calculated fields of the datasource. The first holds values of the two years, but makes the value of the first year a negative number. This can then be SUMmed in order to give a value difference. Add a calculated field with the name "Difference" and add the following code:

=CDbl(iif(Fields!Year.Value=datepart("yyyy", now())-1, (Fields!Sales.Value * -1), Fields!Sales.Value))

In this example the code is dynamically working out the current and previous year so there is no need to change the formula each year. The result is explicitly converted to a double just to be sure we will always work with a consistent data type. The second calculated field is called "FirstValue" only contains values for the first year:

=CDbl(iif(Fields!Year.Value=datepart("yyyy", now())-1, Fields!Sales.Value, 0))

To display the percentage change inside a tablix, add a new column outside the year group. In the column's value cell, add an expression with the following code:

=Sum(Fields!Difference.Value)/Sum(Fields!FirstValue.Value)

This expression can then be placed in any table cell and will give the correct percentage variance at any subtotal level in the table.

To achieve the same result in Reporting Services 2005, you can't mix tables and matrices in the same container so you will need to add a second table immediately beside the matrix and place the expression in it.