Custom reports - subtotalling groups
Posted: 28 Aug 2011, 16:05
I am trying to produce a report showing invoices and payments against those invoices - in customer order. I have joined:
SALES_LEDGER table to AUDIT_SPLIT table on ACCOUNT_REF
AUDIT_SPLIT table to AUDIT_USAGE table on ACCOUNT_REF
The report is intended to look something like (with extraneous details omitted):
Customer ABC123
Invoice £500
Payment £200
Payment £300
Sub-total payments: £500
Invoice £425
Payment £150
Payment £100
Sub-total payments: £250
Total invoiced: £925
Total payments: £750
Customer CDE456
etc
Groups are set up on Customer and Invoice.
The detail values and subtotals are OK, but the 'Total invoiced' evaluated for each customer is wrong: Sage is evaluating the ‘Total invoiced’ expression AUDIT_SPLIT.GROSS_AMOUNT(Sum) by assuming that there is an invoice amount alongside each payment line and adding the lot up. For example, instead of ‘Total invoiced £925’, Sage is assuming an invoiced value of £500 for each of the first two payments plus £425 for each of the second invoice’s payments and displaying ‘Total invoiced £1850’. The same is true for Grand totals. In other words, it is adding up the values from the detail lines when I want it to add up the values from the group header.
I am obviously doing something wrong, but DAK how I tell Sage (in the expression for ‘Total invoiced’) that I want it to be the sum of the values of each group variable?
SALES_LEDGER table to AUDIT_SPLIT table on ACCOUNT_REF
AUDIT_SPLIT table to AUDIT_USAGE table on ACCOUNT_REF
The report is intended to look something like (with extraneous details omitted):
Customer ABC123
Invoice £500
Payment £200
Payment £300
Sub-total payments: £500
Invoice £425
Payment £150
Payment £100
Sub-total payments: £250
Total invoiced: £925
Total payments: £750
Customer CDE456
etc
Groups are set up on Customer and Invoice.
The detail values and subtotals are OK, but the 'Total invoiced' evaluated for each customer is wrong: Sage is evaluating the ‘Total invoiced’ expression AUDIT_SPLIT.GROSS_AMOUNT(Sum) by assuming that there is an invoice amount alongside each payment line and adding the lot up. For example, instead of ‘Total invoiced £925’, Sage is assuming an invoiced value of £500 for each of the first two payments plus £425 for each of the second invoice’s payments and displaying ‘Total invoiced £1850’. The same is true for Grand totals. In other words, it is adding up the values from the detail lines when I want it to add up the values from the group header.
I am obviously doing something wrong, but DAK how I tell Sage (in the expression for ‘Total invoiced’) that I want it to be the sum of the values of each group variable?