Help Summing Group Totals
Hi All
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 have rows of data (shipping costs) and job no splits.
So tracking no T1 may have 1 job associated and tracking no T2 may have 3 jobs associated. I the pro-rata the costs across the jobs. If each shipment costs $10 for tracking no T1, job no J1 will get $10, for tracking no T2, jobs J2, J3 & J4 will each
get $3.33.
The 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
I want to do 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. Ship Total on the report is First(Fields!ShipTotal.Value)
TrackNo
JobNo
I want to sum the Ship Cost total for the entire report (not the split job totals, as these have rounding errors: 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
Regards
Mark
November 9th, 2012 8:31am
Hi Mark,
Based on your description, the column ShipTotal displays the first value of ShipCost of each TrackNo group, and you want to calculate the aggregate of the first ShipCost for the report. Since we cannot use nested aggregate functions and using ReportItems
in aggregate function is also not allowed. To work around this issue, we can try to use custom code. Please refer to the following steps.
1. Add the following code in the code section of the report properties dialog box.
Dim public totalCost as float
Public Function AddTotal(ByVal cost AS float ) AS float
totalCost = totalCost + cost
return cost
End Function
Public Function GetCost()
return totalCost
End Function
2. Add the field TrackNo as row group into Row Group pane of the table, and clickAdd group header. Add the second row groupJobNo to the table data region.
3. Add a new column next to the column which displays the values of TrackNo.
4. On the intersection of the group header row and the new column, specify the value with following expression.
=Code.AddCost(First(Fields!ShipTotal.Value))
5. Add a new row outside the parent row group, and specify a textbox with following expression.
=Code.GetCost()
The following screen shot shows a similar report in my test environment, please take as reference.
For more information, please see:
Using Custom Code References in Expressions (Reporting Services)
Regards,
Fanny LiuFanny Liu
TechNet Community Support
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2012 5:04am