Summing Group Items in SSRS 2008 R2

Question

I have what I think is a fairly simple problem, but can't for the life of me figure out how to do it (SSRS 2008 R2). I'm using a tablix, as I need to display detail data (not just aggregates)

I have rows of data (shipping costs) and job cost distribution

The (abridged) data in the report is

TrackNo      ShipCost   NoJobs          JobNo        Job Cost
T1             10.00        1             J1           10.00
T2             10.00        3             J2            3.33
T2             10.00        3             J3            3.33
T2             10.00        3             J4            3.33

So, TrackNo T1 may have 1 job associated with it and TrackNo T2 may have 3 jobs associated. I then pro-rata the costs across the jobs. If each shipment costs $10:

• for tracking no T1, job no J1 will get $10.00
• for tracking no T2, jobs J2, J3 & J4 will each get $3.33

I want to have a drill down report like this:

TrackNo     Ship Total         No. Jobs         Job No             Job Total
+  T1           10.00              1
                                                  J1                   10.00
+  T2           10.00              2                                                          
                                                  J2                    3.33
                                                  J3                    3.33
                                                  J4                    3.33

In my report, group 1 is tracking no; group 2 (child group) is job no.

TrackNo
    JobNo

Ship Total on the report is First(Fields!ShipTotal.Value)

I want to sum the ShipTotal for the entire report (not sum the job totals, as these have rounding errors: e.g. 3.33 * 3 = 9.99)

How can I sum the values shown against group 1 (First(Fields!ShipTotal.Value))? I'm figuring Scope is the way, but I'm lost.

Answer 1

Often this question is asked for older versions of SSRS, so it's nice to answer for a recent version that supports the feature you need. SSRS 2008R2 introduced support for aggregates of aggregates.

In your total box for the report set the expression to be:

=SUM( MAX(Fields!ShipTotal.Value, "TrackNo"), "DataSetName")

As you mentioned, the Scope is a key part of this. The expression above indicates that SSRS should take the First ShipTotal for each "TrackNo" group, and add those up for every TrackNo group in the dataset. (Substitute your data set name in there, of course.)

Two other two methods to get this result will also work in earlier versions of SSRS as well:

• Use Embedded code to keep track of the aggregate.
• Perform the math in your query, return the result with a different dataset, or as a new field with each row.