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.