Subtotals from group header, and parent groups

Question

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.

Any suggestions?

Answer 1

this was answered in the below post:

http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/4563c797-df05-43bf-80d1-e1757d06daf6/

---

Teddy Bejjani - BI Specialist @ Netways

Answer 2

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.

Thanks.

Answer 3

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:
=Code.SumDeliveryTotal(Fields!DeliveryTotal.Value, Fields!ClientID.Value)

Then displaying the total I use this expression in a textbox, format as currency etc:
=Code.DeliveryDepotTotal

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.