SSRS – Recursive Sum did not provide the correct results at the sub-grouping level

• Tamrak

  SSC Eights!

  Points: 900

  More actions

  October 16, 2018 at 11:22 am

  #371483

  (I use the SSRS 2012 version)

  I have been working on the SSRS report for the recursive organization. I got the result working partially. Let me explain step by step.

  1.       The recursive levels are working fine. I got everyone listed with correct hierarchical levels.

  

  2.       “Total Hours” column provides the correct numbers.

  Example:  Joseph has the result of 3,609.00 in Total Hours.
  The result comes from the direct reports:   285.25 (Brad’s team) + 235.50 (Brian’s Team) + 244.50 (Garth’s team) + 1,901.50 (Patrick’s Team) + 203.00 (Timothy’s Team).    This comes up at:  2,869.75 hours.
  Then, on Joseph’s line -> that’s hours of his direct reports:
  So,  we add: 80.25 + 526.5 + 3+ 129.50  =  739.25
  All combined to: 2,869.75 + 739.25   = 3,609.00

  3.       On the sub-level, Patrick’s team has David and Hart as his direct reports and each direct report has some team members.

   For example:   Hart:  He has two direct reports, Jo and Thomas and both also has sub ordinates in each team. However, the total hours for Hart are 809.25, which are from: 174.00 hours for Jo and 397.25 hours for Hart. Make the combinations of: 571.25 hours.  Then,  Jo and Thomas each put 99.75 + 137.25 + 1 = 238.00 hours.  Total for Hart’s team = 571.25 + 238 = 809.25 hours.
  Each wrap up the hours to the next level.

  We have good "Total Hours" because the report sums the correct hours within the appropriate hierarchical levels.
  ------------------------

  Row and Column Grouping:

  

  ---------------------------

  Problem:

  I cannot make the week column to work as in the Total Hours column.  Apparently, it looked like the sum of the hours, were counted only one level apart.  We would like to see the organization level, just like we see in the Total Hours column.

  Another word, I would like to see total hours column broken down by each week. But I feel that, my SSRS expression was incorrect for the Week column.

  The expression that I used for the total hours column is:

  =sum(Fields!TotalHours.Value,"bossname",  recursive)

  RDL Layout and SSRS Expression:

  
  The expression that I used for the week column is:

  =Iif(InScope("Week"), Iif(InScope("bossname"),  sum(Fields!TotalHours.Value),  sum(Fields!TotalHours.Value, "Week")),
              Iif(InScope("bossname"),  sum(Fields!TotalHours.Value,"Week",recursive),  sum(Fields!TotalHours.Value)))

  Can you let me know of what I did wrong on this?  Thanks.