Recursive Sum for a Group in a Matrix in SSRS

April 22, 2009

Recursive Sum for a Group in a Matrix in SSRS

I wrote a post not too long ago about creating Sum for a Group with Recursion in SSRS. A reader of that post dropped me a line as she was having trouble recreating the same behavior in a matrix (the post itself had demonstrations in it only for a table). So I decided to look into how a recursive sum acts in a matrix, and boy what a learning experience that turned out to be…
I created a report with a matrix which connected to Adventure Works DW. I defined the employees as the group on the rows (the recursive group with the parent – child relationship), and the product line as the group on the columns. I added styling to the padding and headers in SSRS as I have demonstrated in previous posts.
Now, the thing was that if I used the simple recursive sum I used for my table, I would get the recursive sum for the parent employee, but it would be the same for all the product lines. Obviously, this was not what I was looking for.At this point, I would really like to thank Boris Kogan who introduced me to the InScope function in SSRS, and helped me play around with it. The function helps you see where you are in respect to a dataset, a group, or a data region. Using the InScope function in a matrix can help us manipulate the sum that’s created for each part of the matrix (the inner cells, the total for the columns, the total for the rows or the total for the matrix). In a brilliant post by Jorg Klein, you get a very elegant and powerful solution to how to break your matrix into all of its parts. Using Mr. Klein’s very smart expression, I wrote:

=Iif(InScope(“matrix1_ProductLine”), 
   
Iif(InScope(“matrix1_FullName”), 
      
sum(Fields!SalesAmount.Value), 
      
sum(Fields!SalesAmount.Value,“matrix1_ProductLine”)),
     Iif(InScope(“matrix1_FullName”), 
        
sum(Fields!SalesAmount.Value,“matrix1_FullName”,recursive), 
         sum(Fields!SalesAmount.Value)))  

Which basically means that in the matrix the following happens (see picture with correlating numbers):




  1. In the cells, we get for each employee the sales he made for that product line. If he’s the boss, we still get only the sales he made (and not his sales with his workers sales)

  2. The subtotal for the row group is as usual for the scope of product line.

  3. The recursive sum appears in the subtotal of the column group for the employee.

  4. The total for the matrix is as usual (a total of the data in the dataset).
Which gives us:

   

But you might still ask “What about a recursive sum in the cells?” Well, I do admit my solution isn’t full… I did try to apply:
sum(Fields!SalesAmount.Value,“matrix1_FullName”,recursive) 
For the sum in the cell but I have to admit it didn’t work. What I got was the total for each employee for all of his sales (including his children, if he’s a parent). That’s all his sales – not just for that product line. I even tried adding another iif clause where I defined that only level zero (of the parent) will get the recursive sum and the rest will get the usual sum. That also didn’t help.
Thing is, that the recursive sum actually needs to run on two levels of scope for it to appear correctly. We need the recursive sum for both groups we have in the matrix – both the employee and the product line, because we’re actually trying to sum the sales of an employee and all his workers for a specific product line (not just their sales in general). In the recursive sum though, you can only give one scope for it to run on – either the employee or the product line. You cannot define it on two scopes. So in the solution I’m proposing, you only see the recursive sum in the column group subtotal.
I would also add that though in my previous post about recursive sum I gave you the solution for Reporting Services 2005 and Reporting Services 2008, this time I’m giving you the solution just for SSRS 2005 because my VPC crashed, when my PC crashed…. :(

I’ll try to get you the solution for SSRS 2008 in an upcoming post.
In the meanwhile, I’m attaching my report for your viewing pleasure: Sum Recursive Matrix
I hope this helped you create the recursive sum you need for a group in your matrix.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

30 comments

  1. wingApril 23, 2009 ב 17:50

    hi Ella:
    can you give a detailed sample to me?I am a fresher to the SSRS that used it to display the parent-child dimensions in matrix.this problem has already puzzled me for many days.thx.

    from wing

    Reply
  2. Ella MaschiachApril 26, 2009 ב 9:39

    Hi Wing,

    I have already attached the detailed file to the blog post (look at the last paragraph “Sum Recursive Matrix”).

    Hope that helps,
    Ella

    Reply
  3. ThatoMay 27, 2009 ב 14:18

    Hi Ella

    I am having difficulties understanding this InScope function. I tried copying your code above and using it for navigation on matrix totals but I’m not winning everytime I click on the total it randomly select a cell and pass it as a parameter which then give me incorrect info.

    may you please give an example of how to do this

    many thanks

    Reply
  4. Ella MaschiachMay 27, 2009 ב 15:48

    Hi Thato,

    The formula is not meant for navigation but as an expression in a cell inside the matrix itself. Try to build an SSRS project in your BIDS and then download the RDL file I put at the end of the post, and attach it to the project.
    Look at the file and that should explain it the best way.

    All the best,
    Ella

    Reply
  5. SriMay 28, 2009 ב 7:04

    Exporting the report to Excel does not keep the visual hierarchy. Any ideas / suggestions to keep the + , -?

    Thanks for this article.

    Reply
  6. Juan IgnacioNovember 23, 2009 ב 20:28

    Hi Ella,
    I’m having a problem with a Tablix in SSRS 2008.

    I have:

    On rows: 2 levels of hierarchy.
    On columns: 2 levels of hierarchy.

    Everything is with subtotals. It’s something like this:
    Year
    M1 M2 M3 Totalyear

    Category C1
    Subcategory S1
    S2
    S3
    C1Total
    Category C2
    Subcategory S1
    S2
    S3
    C2Total

    Overall Total

    What i want to do, is to calculate a percent over the parent group, i.e:
    in (S1,M1), i want ((S1,M1) / (C1Total, M1))

    the same way, what i want in (C1Total, M1) is to calculate ((C1Total, M1) / (Overall Total, M1)).

    So far, i’ve been able to calculate percent over the parent group, but the problem is that it calculates it over the total of the tablix, so it gives me back the same value for each month.

    I’ve been reading about this and it sais that you can’t calculate an aggregation function with 2 scopes (category and month, in this case).

    I need to find another way to calculate what i want.

    The data comes from a MOLAP cube in analysis services, so an alternative is to precalculate it there, but i haven’t been able to do it.

    Any help is welcome.

    Thx to anyone who answers me. (i’m sorry about my english, but is not my native languaje).

    Juan Ignacio

    Reply
  7. Ella MaschiachNovember 29, 2009 ב 11:33

    Hi Juan,

    Indeed you are right and you cannot calculate an aggregation function with 2 scopes. If you’re willing to use a scope in MDX, you would need something along the lines of:

    Create Member CurrentCube.[Measures].[Ratio S1M1 to C1M1]

    As IIf

       (

          IsEmpty

          (

             [Measures].[Sales], [Category]

          ),

          Null,

          (

            [Measures].[Sales], [Subcategory]

          )

          /

    (

          [Measures].[Sales], [Category]

    )

       ),

    Format_String = “Percent” ;  

    Assuming your regular measure is called [Measures].[Sales Ratio] what you would need to do in MDX is also add a scope to replace the ratio calculated with the one you created yourself:

    Scope

    (

       [Subcategory].Members,

       [Date].[Fiscal].[Fiscal Months].Members,

       [Measures].[Sales Ratio]

    ) ;  

    This = ([Measures].[Ratio S1M1 to C1M1]) ;

    End Scope ;

    Hope that helped,

    Ella

    Reply
  8. RaviDecember 1, 2009 ב 11:32

    Hi,

    I am able to create “A Recursive Hierarchy Group”. My problem is the data appears in this way

    - A 8000
    A1.1 5000
    A1.2 3000
    - B 17000
    B1.1 8000
    B1.2 9000

    but I want the report to show the data in the below format

    A1.1 5000
    A1.2 3000
    - A 8000
    B1.1 8000
    B1.2 9000
    - B 17000

    Please let me know if this is possible in Recursive hiearacial Groups ?

    Thanks

    Reply
  9. Ella MaschiachDecember 8, 2009 ב 9:06

    Hi Ravi,

    I regret to say that as far as I know that’s not possible.

    All the best,
    Ella

    Reply
  10. RaviDecember 10, 2009 ב 16:12

    Hi Ela,

    Thanks for your answer,Please let me know if you come accross this.

    Bye
    Ravi

    Reply
  11. Juan IgnacioDecember 22, 2009 ב 22:03

    Hi ella, thanks for your answer, it works just fine! :)

    Reply
  12. Rohit KumarAugust 20, 2010 ב 13:28

    Hi,
    i have a small query in SSRS, which is quite big for me right now. Actually i hav to calculate the Progressive total in a Table. The situration is like:

    Intial(Opening Bal) : 2000
    S.No Cost Progressive_Cost
    1 100 2100 (Opening Bal + Current Cost)
    2 200 2300( Previous Progressive_Cost+cost)
    3 300 2600(2300 + 300)

    Kindly help me, How can i achieve that.I am using SSRS right now.I have also tried Previous() Function but thats not giving the desired Result

    Reply
  13. Ella MaschiachAugust 23, 2010 ב 16:01

    Hi Rohit,

    First of all the opening balance should be also part of your dimension. It should look like:

    S.No   Cost   Progressive_Cost
    0              2000       2000
    1              100         2100
    2              200         2300
    3              300         2600

    This way you can achieve progressive cost with the SSRS function of RunningValue. The function is located under Sommon Functions > Aggregate. For use of the function, please look at the following:
    http://msdn.microsoft.com/en-us/library/ms159136.aspx
    http://blog.stevienova.com/2007/07/02/ssrs-runningvalue-to-get-cumulative-totals/

    All the best,
    Ella

    Reply
  14. djaifOctober 20, 2010 ב 16:21

    does the sum recursive work in the sort expression in rs 2005? I get error.

    Reply
  15. Ella MaschiachOctober 24, 2010 ב 16:11

    Hi Djaif,

    I don’t think that SSRS can compute the recursive subtotal (whose value is dependant per cell) and sort on it, all before rendering the report. If you want to sort on this expression, you’ll have to calculate all the values before hand in the dataset itself of the report.

    All the best,
    Ella

    Reply
  16. danieleNovember 3, 2010 ב 15:11

    with rs2000 I have sortexpression on the group witch contained a sum with recursive parameter
    IIF((Parameters!SortDir.Value=”Desc” AND Parameters!Sort.Value=”Revenue”),Sum(FIELDS!RevTotal.Value, “table3_CustomerGroup”, Recursive),0)
    So rs did all the job sorting in recursive way every level in the hierarchy :
    Parent1 1000
    Parent 2 1500
    Parent 1-2 700
    Enfant1 300
    Enfant2 400
    Parent2-2 800
    Enfant1 200
    Enfant2 600
    But in rs 2005 this expression results error!! Without the parameter recursive there is no error but the sort does not work on parents
    I tried to implement it using the interactive sort new feature in rs2005. There is no error for the sort expression with recursive parameter but the sort does not work properly.
    Clients families are grouped sing a key CustomerId, ParentCustomerId.

    Reply
  17. Ella MaschiachNovember 6, 2010 ב 18:16

    Thank you Daniele for the added information on Djaif’s question!

    Reply
  18. danieleNovember 8, 2010 ב 17:03

    no its not add information!!! I have exactly the same problem as Djaif. I dont know how to make it work using just features of RS2005!!!?

    Reply
  19. idaNovember 26, 2010 ב 13:42

    hi,
    thanks for your solution!
    i have also 1 problem about sum over colums.

    column1 column2
    50 30
    100 60
    sum col1: 150 sum col1:90
    Grand Total = sum col1 + sum col2

    i try to calculate this Grand total but RS 2008R2 don’t want to summe the values. Please can you help me?
    Ida

    Reply
  20. Ella MaschiachDecember 1, 2010 ב 14:56

    Daniele – as I have told Djaif before, I’m afraid I cannot assit you on the matter.

    Ida – try creating another column which has an expression to sum column 1 + 2.

    All the best,
    Ella

    Reply
  21. DeviJanuary 12, 2011 ב 2:30

    Hi Ella,

    Sorry if it is a repeat, from the figure, What is the process or expression to add column “ “T and Column “ “S” Assuming it is matrix report and the columns are displayed based on the selection in the parameters. Say for example your param A (You selected “R,M,S,T”) every time your selection changes, but you always need to sum the last 2 columns.
    Any suggestions are highly appreciated.
    Thanks,
    Devi

    Reply
  22. Ella MaschiachJanuary 15, 2011 ב 23:47

    Daniele and Djaif – please look at the following link:
    http://technet.microsoft.com/en-us/library/dd255193.aspx
    Follow the steps described for “To sort values in a group, including the details group, for a Tablix”. You should plant your expression in the sort expression for the group and that should work well enough.

    Devi – just to make sure, are you asking how to sum the last two columns, never mind which 2 columns were chosen by the user?

    Reply
  23. SubbuNovember 9, 2011 ב 16:26

    Hi Ella,
    I have an issue with SSRS 2008(not sure if its an issue). Our requirement is to have a recursive hierarchical report which i was able to crack through. However we also have a column grouping and when the recursive sum (rolling up sub total) is used, the values are being doubled.

    Month1 Month2 Month3
    Level 1 3 2 1
    Level 2 2 0 0
    Level 3 1 2 1

    As per the above, I need to display hierarchical information month wise along with the subtotals rolling up. However recursive SUM double up the values.
    Is there a way out to display accordingly. All the help is much appreciated. Thanks.

    Reply
  24. jennetteJanuary 11, 2012 ב 18:40

    hi, thanks for the post. it really helped me a lot. just a quick question, i tried the solution below but it seems it’s only showing 2 levels? I have a data that drill down upto 5 levels. please help. thanks

    Reply
  25. Ella MaschiachJanuary 12, 2012 ב 17:25

    Hi Jennette,

    The report can drill just as deep as the Parent – Child (or any recursive hierarchy for that matter) can go. But you can only define one parent and one child. If what you have isn’t a recursive hierarchy but rather a basic hierarchy with 5 levels, use the usual report grouping.

    All the best,
    Ella

    Reply
  26. NunoApril 19, 2012 ב 14:24

    Hi,
    I’m new in the SSRS, and i’m having a problem creating a measure to do a apparently easy SUM with a condition!
    Can someone help me?

    What i have is:
    [Dm Plano Contas].[Snc Cod].[1]

    [Dm Plano Contas].[Snc Cod].[23]
    [Dm Plano Contas].[Snc Cod].[231]
    [Dm Plano Contas].[Snc Cod].[2311]
    [Dm Plano Contas].[Snc Cod].[2312]

    And what i want is to SUM all the childrens of the [Dm Plano Contas].[Snc Cod].[23] where the [Measures].[Saldo] is smaller than zero.

    Reply
  27. Ella MaschiachApril 23, 2012 ב 15:27

    Hi Nuno,

    From the example it looks like what you want is to check that measure for all the descendants of that member as the key is either 3 or 4 letters long (which I guess means different levels). Using MDX functions you can write something along the lines of:

    Sum(YTD(), iif(([Measures].[Saldo], [Dm Plano Contas].[Snc Cod].currentmember) <0, [Measures].[Saldo], 0)

    Create that sum as a new measure in your cube and use that as a measure for the recursion in SSRS I described in the post.

    I would show in the report with just the relevant members as I don’t see the logic in this measure for any member that isn’t under [Dm Plano Contas].[Snc Cod].[32].

    Please also look into the children, descendants and members MDX functions if they’re at all relevant for you.

    Hope that helped,
    Ella

    Reply
  28. sarvamOctober 4, 2012 ב 13:23

    how to do the average of average in a matrix .

    Reply