SSRS summary of nested groups at outer group footer

Question

I have a report ouput like this, it has nested groups, outer group is on Business Partners and the inner one is on Currency Code.

Busines Partner1
    Opening Balance for USD as of 2013-10-01
        Transaction 1..
        Transaction n..
        USD Totals
    Balance for EUR as of 2013-10-01
        Transaction 1..
        Transaction n..
        EUR Totals

Busines Partner2
    Opening Balance for USD as of 2013-10-01
        Transaction 1..
        Transaction n..
        USD Totals
    Balance for EUR as of 2013-10-01
        Transaction 1..
        Transaction n..
        EUR Totals

What I need is on Business Partner Group Footer, I'd like to display overall outstanding balances for each currency code. Something like;

Busines Partner1
    Opening Balance for USD as of 2013-10-01
        Transaction 1..
        Transaction n..
        USD Totals
    Balance for EUR as of 2013-10-01
        Transaction 1..
        Transaction n..
        EUR Totals

    (Assume below section is Business Partner1 Group Footer)
    Currency, Opening Balance, Sum of In Period, Overall
         USD           200.00            300.00     500
         EUR           150.00            400.00     550
         Total                                     1050

Busines Partner2
    Opening Balance for USD as of 2013-10-01
        Transaction 1..
        Transaction n..
        USD Totals
    Balance for EUR as of 2013-10-01
        Transaction 1..
        Transaction n..
        EUR Totals

    (Assume below section is Business Partner2 Group Footer)
    Currency, Opening Balance, Sum of In Period, Overall
         USD           100.00            300.00     400
         EUR           250.00            300.00     550
         Total                                      950

Question is how can I achieve this?

I am pulling data from an SP, with opening balances for a given date.
Number of currencies are not known beforehand.
And the report has 2 groups, outer group is on Biz.PartnerId inner one is on currency code.
SSRS 2008 R2 ( I can upgrade to latest and greatest if this is an issue to consider.)

Sam Sam · Accepted Answer · 2013-11-18T18:57:18

I think that you just have to structure your groups right..

based on the following sample data:

SELECT 'BusinessPartner1' BusinessPartner, 'EUR' CurrencyType, 100 Amount, 122 TotalSpend UNION
SELECT 'BusinessPartner1' BusinessPartner, 'GBP' CurrencyType, 111 Amount, 301 TotalSpend UNION
SELECT 'BusinessPartner2' BusinessPartner, 'EUR' CurrencyType, 81 Amount, 191 TotalSpend UNION
SELECT 'BusinessPartner2' BusinessPartner, 'GBP' CurrencyType, 13 Amount, 93 TotalSpend UNION
SELECT 'BusinessPartner2' BusinessPartner, 'USD' CurrencyType, 30 Amount, 32 TotalSpend UNION
SELECT 'BusinessPartner3' BusinessPartner, 'GBP' CurrencyType, 78 Amount, 42 TotalSpend UNION
SELECT 'BusinessPartner3' BusinessPartner, 'USD' CurrencyType, 210 Amount, 61 TotalSpend UNION
SELECT 'BusinessPartner3' BusinessPartner, 'EUR' CurrencyType, 12 Amount, 71 TotalSpend

If you structure your groups like this, (1 parent, 1 child, 1 adjacent to child):

Grouping example 1

Then you get the below output, which I think is the same sort of thing that you're after. To get exactly what you need I think that you would also need a details child group on each of the adjacent groups

Grouping example 2

SSRS summary of nested groups at outer group footer

1 Answers