none
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?



    • Edited by LMcCulloch Saturday, February 02, 2013 6:57 PM formatting
    Saturday, February 02, 2013 6:53 PM

Answers

  • 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

    Sunday, February 03, 2013 10:15 AM
  • 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.

    • Marked as answer by LMcCulloch Thursday, February 14, 2013 5:31 PM
    Thursday, February 14, 2013 5:28 PM

All replies

  • 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

    Sunday, February 03, 2013 10:15 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.

    Thanks.

    Friday, February 08, 2013 5:51 AM
  • 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.

    • Marked as answer by LMcCulloch Thursday, February 14, 2013 5:31 PM
    Thursday, February 14, 2013 5:28 PM