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!
I have a situation where I want to subtract the results of a column group from each other. For instance, each row of the matrix is a different calculation of different fields and the column group is comprised of two dates the user enters as parameters (firstdate, seconddate).
So, it looks something like this:
Jan 1 2010 Jan 1 2011
#of Employers with medical participants 58 66 (a countdistinct)
# of Medical participants 250702 423960 (a count)
Average EE Premium $164.86 $186.69 (an average)
How can I make a total row that subtacts the values in the column groups, for instance 66-58 = 8 as the #Difference. I also need a percentage calculation, so 66-58/58. Also, since each row is a different calculation based on a different field in the dataset, this isn’t a “one expression fits all” type of scenario for the total row. Is there any way to do this in SSRS 2005? Or, does it get any easier to add total rows in 2008 where they use a tablix structure instead of having only the option of a table or a matrix?
Any help would be most appreciated. I’ve been driving myself nuts with this.
Tom, thank you very much for this solution!
Helped me a lot.
Hi
I really hope you can help me get this working.. at the moment i’m struggling to get the custom code section right.
I’m assuming i need to change parts of the code you’ve put in above to reflect my data/matrix? and in the piece you past into the field code i need to change the groups and the field i’m summing?
but i’m not sure exactly how
(Sorry.. I’m new to reporting and VB code)
The matrix i have looks a little like this –
=Fields!WeeksStartDate.Value
=Fields!Team.Value =Fields!Technician.Value ***
=”Sum” + vbcrlf + “Avg”
*** is the field where i’ve put the following code in
=Code.MatrixCellValue(
Sum(Fields!Closed.Value),
Avg(Fields!Closed.Value),
InScope(“”matrix1_RowGroup2″”) and InScope(“”matrix1_WeekStartDate”"),
“”{0,6:N0}”",
“”{0,6:N1}”")”
Does that look right?
How do i change the custom code to work with my matrix?
Sorry to be a pain.. and thanks for your time in advance
Nevermind.. got it working
Great.. thanks
Thanks for the solution!
Just one reminder:
As Code.MatrixCellValue() is a custom code you need to add the following script in the code property of the report.
Report Property -> code add the following script:
[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
[/code]
Othervise you will recieve this error:
[b][BC30451] Name ‘code’ is not declared.[/b]
Cheers,
Amir
Bless you! I’m just learning SSRS and TOTALLY did not know and would never have guessed that subtotals can only SUM. You helped me get the quarterly averages that were required on a report (that, incidently was supposed to have been written by a contractor, but forgotton/lost….) Thanks to you this report is now ready! And, thanks a lot for explaining the format codes! Your article is the best–extrememly well written, documented and thorough.
Thank you, i needed a way to display a Total and its column percentage as compared to the overall.
your example will guide me nicely.