Get an Average and a Sum in a Reporting Services Subtotal

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.

ReportingServicesSubtotal

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

SumAndAvg

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.

ReportingServicesFunctionCall

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.

22 Comments »

Comment by Barretto
2009-04-02 02:30:52

Good tip. After looking for it all over the internet I just found a good answer here in this article.
Congratulations !

 
Comment by Tom VdP
2009-04-08 11:11:32

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…)

 
Comment by Bruno Santos
2009-07-08 16:44:39

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

 
Comment by Tom VdP
2009-07-13 16:40:53

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

 
Comment by SANTOSH
2009-08-29 18:11:36

HI, your article is very good , this has solved my problem.

 
Comment by FHall
2009-09-18 19:58:43

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!

 
Comment by FHall
2009-09-18 20:06:44

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!

 
Comment by dragoshel
2009-09-22 12:44:16

I worked as a charm!
Great blog, thanks a lot!

 
Comment by Terese
2009-10-15 14:48:19

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.

 
Comment by Madhuri
2010-01-04 08:26:10

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.

 
Comment by ReportingServices
2010-03-01 22:01:29

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.

Comment by Tom VdP
2010-03-08 14:16:07

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.

 
 
Comment by David Pastrana
2010-06-04 18:54:41

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!

 
Comment by Denise
2011-01-26 19:54:38

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.

 
Comment by Maik
2011-03-01 22:58:28

Tom, thank you very much for this solution!
Helped me a lot.

 
Comment by Steph
2011-06-17 12:15:05

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 :)

Comment by Steph
2011-06-17 12:29:03

Nevermind.. got it working :)
Great.. thanks

 
 
Comment by Amir
2011-11-09 02:15:20

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

 
Comment by KimS
2011-11-22 17:18:24

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.

 
Comment by Salman
2012-01-18 18:19:24

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.

 
Comment by Carlos
2012-06-08 11:11:57

Thanks for sharing this, helped me out of a jam.

 
Name (required)
E-mail (required - never shown publicly)
URI
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Trackback responses to this post