Saturday, February 02, 2013 6:53 PM
I've searched and experimented with this but still cannot get what I need.
This was dirt simple in Crystal, which I'm now porting to SSRS.
Here's an example data set:
Client | DeliveryId |DeliveryDate | DeliveryTotal | Item Bob | 1000 | Jan 01 | 50.00 | Socks Bob | 1000 | Jan 01 | 50.00 | Ties Bob | 2003 | Jan 03 | 14.00 | Socks Tim | 4500 | Jan 05 | 76.00 | Cufflinks
Here's the grouping structure:
Client DeliveryId (shows DeliveryTotal, DeliveryDate in group header) DeliveryDetail (shows Item, not DeliveryTotal since it is repeated in each record)
I need to show a client subtotal which sums all of the distinct DeliveryTotals for that client.
Here's an example showing the grouping and data:
Bob DeliveryID: 1000 DeliveryTotal: $50.00 DeliveryDate: Jan 01 Socks Ties DeliveryId: 2003 DeliveryTotal: $14.00 Socks Client Total: $64.00 (sum of the 2 DeliveryTotals, not the DeliveryTotal in each record since it is duplicated)
I've tried variations on Sum, Inscope, grouping contexts etc but no luck. Using any form of Sum multiplies the total by the number of records which is incorrect. If I could nest aggregates I'd be fine. If I could sum on ReportItems I'd be fine (I think). But it doesn't matter since SSRS doesn't allow either.
I've put some thought into using custom code, but then I'd be having to manually code grouping, sums, totals and resets on group change. That sounds ridiculous to me.
Can I somehow sum by group name, and DeliveryId? That would effectively create a sum at the right scoping level, and be a distinct sum for the entire Delivery record, not each detail row.
Again this was dirt simple in Crystal version 7... over 10 years ago.
- Edited by LMcCulloch Saturday, February 02, 2013 6:57 PM formatting
Sunday, February 03, 2013 10:15 AM
this was answered in the below post:
Teddy Bejjani - BI Specialist @ Netways
Friday, February 08, 2013 5:51 AM
I don't know who marked your reply as the answer but I didn't, and I'm still working on a solution.
I read the that thread you provided, and there are some options there that would likely work for some people.
In my case the underlying stored procedure is extremely complex and rolls up several layers of nested views, and deals with several UNIONs.
It is simply NOT feasible to modify my existing underlying stored procedure.
I am working on implementing report code to manually reset subtotals as I go.
At this point I have the inner subtotal calculating and resetting correctly but the top level report total isn't rolling up. I have more work to do.
Thursday, February 14, 2013 5:28 PM
Here is how I solved this problem without modifying the underlying stored procedure:
I added report code to manually sum and reset the totals at the levels I needed.
Note that you MUST use Shared variables otherwise the variables are limited in scope to only the current page, which is useless for rolling subtotals, but would be handy for page totals if you needed that.
Here's a sample of the code that gives me the Client total, as well as the overall report total (sum of all client totals):
Public Shared ClientTotal As double Public Shared ReportTotal As double private lastClientId as integer Public Function SumDeliveryTotal(ByVal Value As double, ByVal clientId as Integer) if (lastClientId <> clientId) ClientTotal = 0 lastClientId = clientId end if ClientTotal = ClientTotal + Value ReportTotal = ReportTotal + Value End Function
The comparison of the clientID value tells me when to reset the ClientTotal as that indicates the client group has changed.
The ReportTotal simply always sums the incoming value.
Then to call the code, I added an expression to a textbox in the header of each delivery group. I made the textbox hidden.
The expression is:
Then displaying the total I use this expression in a textbox, format as currency etc:
Works for now but is by no means ideal. I sure hope SSRS introduces better rolling/group/totals etc. Like I said, I was doing all of this with ease 10 years ago in Crystal v7.
I have NOT verified if the answer in the other solution works. Forum moderators marked it as an answer without confirming with anyone.
- Marked As Answer by LMcCulloch Thursday, February 14, 2013 5:31 PM