All about Business Intelligence

You would think that if you wanted something simple like having a subtotal line with a sum and an average that this would be easily configurable in Reporting Services. Alas… Clicking on the Subtotal button magically adds a row to the matrix component. But, you cannot tell it what function to use: it will always show the sum of the numbers above. There is also just one row that can be added.

Frustrating, if you want to achieve something simple like this:

You will need to apply a few tricks to achieve the above result.

First of all, it is possible to put multiple values inside a single cell. If values are put in a string and seperated with linefeeds they will appear as if on seperate rows.

To demonstrate, replacing the text “Total” in the subtotal row with the expression ="Sum" + vbcrlf + "Avg" will show two row headers (albeit in a single row, but that is good enough for me).

The next trick is to call custom code to calculate the cell values. The custom code, a function, returns a string containing either a single value (when inside the matrix) or two values in two “rows” (when in the subtotal row).

Have a look at the code:

    Public Function MatrixCellValue(ByVal sumValue As Object, ByVal avgValue As Object, ByVal InScope As Boolean, ByVal sumFormat As String, ByVal avgFormat As String) As Object
        If InScope Then
            Return sumValue
        Else
            If Not avgValue Is Nothing Then
                Return String.Format(sumFormat, CDbl(sumValue)) + vbCrLf + String.Format(avgFormat, CDbl(avgValue))
            Else
                Return Nothing
            End If
        End If
    End Function

Let’s go over each of the input parameters:

• sumValue is the sum that is either shown inside the matrix area or in the subtotal row. This value will in any case be included in the return value of the function.
• avgValue is the average value as calculated by Reporting Services. It should only be in included in the return value of the function when the function is called from the subtotal row.
• InScope is a boolean that indicates whether the function is called from inside the matrix area or outside of it.
• sumFormat and avgFormat are format strings that will be applied to the sum and average values.

Now replace the expression in the matrix cell with the function call below and you will get exactly the result as pictured above!

=Code.MatrixCellValue(
                     Sum(Fields!Count.Value),
                     Avg(Fields!Count.Value),
                     InScope("matrix1_Date") and InScope("matrix1_Product"),
                     "{0,6:N0}",
                     "{0,6:N1}")

In this example, the matrix groups are matrix1_Date for row grouping and matrix1_Product for column grouping. Hence InScope("matrix1_Date") and InScope("matrix1_Product") indicates whether or not the current cell is inside the matrix area or not.

A little word on the format strings: "{0,6:N1}" means: replace the 0 with a numeric having 1 decimal and pad the result with spaces so that the result is 6 characters long. For more info about format strings see the MSDN documentation.