SSRS Subtotal matrix

Posted on 2011-05-11
Last Modified: 2012-05-11
Hi All,

I have a matrix that I'm trying to subtotal and can't figure out how to do this.  Essentialy across the top I have a variety of projects and across the left side is a variety of categories.

For example:

                ProjectA                ProjB                ProjC                           Status Totals (NEED)
                  #       $               #      $                #     $                         #                   $
Live         200    1.5M         5       50k             1     1M                      206         2.5M + 50k

OnHold      10    250k         1       10k             5     500k                   16              760K

Project Totals (NEED)
               210    1.75M         6       60K         6       1.5M                   222            3.31M

How do I get the project totals (to subtotal vertically)  and Status totals (subtotal horizontally) into the matrix for both # and $?

Question by:Kyle Abrahams
    LVL 22

    Expert Comment

    by:Nico Bontenbal
    What version of SSRS are you using.
    If it is 2005 you can right click the project header and choose subtotal, and right click the stutus header and click subtotal.
    If it is 2008 you can right click the Row- and Column Groups at the bottom of your screen and select Add Total|After.
    LVL 39

    Author Comment

    by:Kyle Abrahams
    Thanks for the response.  I actually had to add sum(Fields!Count.Value) instead of just Fields!Count.Value in my inner matrix.  Will give you the points if you can tell me why.
    LVL 22

    Accepted Solution

    The matrix does grouping as well. So your result could come in like this:
    ProjectA, Live, 150, 1000000
    ProjectA, Live, 50, 500000
    ProjectB, Live, 3, 40000
    ProjectB, Live, 2, 10000

    And your matrix would still have the values as in your example. So SSRS wants to know what do with the multiple values. It doesn't know in advance that you only supply unique values, so it wants an aggregate (for example sum) function.
    LVL 39

    Author Closing Comment

    by:Kyle Abrahams

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
    Introduction Earlier I wrote an article about the new lookup functions ( that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now