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.


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
            If Not avgValue Is Nothing Then
                Return String.Format(sumFormat, CDbl(sumValue)) + vbCrLf + String.Format(avgFormat, CDbl(avgValue))
                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!

                     InScope("matrix1_Date") and InScope("matrix1_Product"),

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.


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?


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.

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!


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.
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

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!Team.Value =Fields!Technician.Value ***
=”Sum” + vbcrlf + “Avg”

*** is the field where i’ve put the following code in
InScope(“”matrix1_RowGroup2″”) and InScope(“”matrix1_WeekStartDate”"),

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
If Not avgValue Is Nothing Then
Return String.Format(sumFormat, CDbl(sumValue)) + vbCrLf + String.Format(avgFormat, CDbl(avgValue))
Return Nothing
End If
End If
End Function
Othervise you will recieve this error:
[b][BC30451] Name ‘code’ is not declared.[/b]


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.

Comment by pagespeed test
2018-08-05 21:53:42

Helpful information. Fortunate me I found your site accidentally, and I am surprised why this accident did
not happened earlier! I bookmarked it.

Comment by Mathias
2018-11-20 06:06:31

That puts e-cօmmerce sites on a good level playing field.
Internet marketting is more ɑffordable tan printed ads, radio
oor TV. Just like,whօ couⅼd be the audience you making the effort rеach and what іs better pаth to take
with reaching it.

2019-04-24 21:50:38

I don’t know if it’s just me or if everyone else experiencing issues with your
website. It appears as if some of the text in your posts are running off the screen.
Can somebody else please comment and let me know if this is happening to them too?
This may be a issue with my browser because I’ve had this happen previously.
Many thanks

2019-05-14 10:31:22

I don’t even understand how I finished up right here, but
I thought this put up was once good. I don’t realize who you are but definitely you are going to a
famous blogger if you aren’t already. Cheers!

Comment by fondina glock 17
2019-05-17 06:50:03

Thanks for any other wonderful post. Where
else may just anyone get that kind of information in such
an ideal means of writing? I have a presentation subsequent week,
and I’m on the look for such information.

Comment by best customs
2019-07-01 10:57:49

I think what you composed made a great deal of sense.

However, what about this? what if you were to write a killer headline?

I mean, I don’t wish to tell you how to run your website,
however what if you added a title that makes people desire more?
I mean Get an Average and a Sum in a Reporting Services Subtotal : Business Intelligence Blog is a little plain. You could peek at
Yahoo’s front page and see how they create news titles to
get viewers to click. You might add a video or a picture or two
to grab people interested about everything’ve got to
say. Just my opinion, it might make your posts a little bit more interesting.

Name (required)
E-mail (required - never shown publicly)
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