Get an Average and a Sum in a Reporting Services Subtotal
y2009m02d18
Tom VdP
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.
Good tip. After looking for it all over the internet I just found a good answer here in this article.
Congratulations !
Thank you
That is a bit the idea behind this blog: when I encounter a problem for which I cannot find a solution on the net, I post a solution here (if I find one and if time allows…)
Tom, the solution is very good, more for me is repeats the values also on the fields above, no only on the total field. How I can correct?
Thanks
Bruno Santos
Bruno, double-check that what you have put in the expression in the matrix field value is correct. Especially the names of the groups inside the InScope functions.
Hth,
Tom
HI, your article is very good , this has solved my problem.
This has been so helpful! I’ve got it to display what I want, the only issue is that I want the format to be different than “{0,6:N1}”. I need the number to be formatted based on a passed in setting (DecimalField). So, it might be 3 or it might 2 or 1 for formatting. I can’t hardcode 1. Any suggestions to being able to use the value of a passed in field? Thanks for any help!
Ha! I just figured out the answer to my question. I was able to do this in the field:
“{0,6:N” & Fields!DecimalPositioner.Value & “}”
Pretty simple. I appreciate all the great advice above!
Thanks!
I worked as a charm!
Great blog, thanks a lot!
I am trying to implement the above approach, but am only getting the total once. e.g.
Jan Feb Mar Total
Sales Dollars 10.00 10.00 10.00 30.00
Sales Discounts -3.00 -3.00 -3.00 -9.00
A regular subtotal would yield 7.00 7.00 7.00 21.00
what I’m getting when I add the code is this:
Jan Feb Mar Total
Sales Dollars 10.00 10.00 10.00 30.00
Sales Discounts -3.00 -3.00 -3.00 -9.00
My New Total 21.00
I’ve double check my syntax.
I have a query.
I am trying to get the average of a percentage column, and using the expression
‘ =Avg(Fields!SLA__.Value”) ‘ but its throwing error.
Any comments.
HI, your article is too good and this has solved my problem.
question:
Can I ues more than t values like Total & Avg and Max and Min?
When I try more then 2 its not working can you please advice.
Sure, since it is a string that is displayed in the cells, you could add as many values as needed. Just add them as arguments to the “MatrixCellValue” function and in that function concatenate the values seperated by vbCrLf’s.
Hi! That’s a good tip! But I need help about a similar problem:
Let’s think that you have some Categories and you are displaying the sales percentage in columns determinated by the month of the sale:
Jan | Feb | Mar | Apr | May | Jun
Category 1 10% 15% 20% 25% 30% 35%
Category 2 10% 15% 20% 25% 30% 35%
Category 3 10% 15% 20% 25% 30% 35%
So, now you need to show a new column with the average of the Past 3 months sales percentage of each category, just like this:
Jan | Feb | Mar | Apr | May | Jun | P3M
Category 1 10% 15% 20% 25% 30% 35% 30%
Category 2 10% 15% 20% 25% 30% 35% 30%
Category 3 10% 15% 20% 25% 30% 35% 30%
With the tip of this article, I have an idea to do this, but only to show the average of all the months and not only the last 3 months.
Any sugestion will be helpful.
Best regards!