How to get Group totals as a percentage of Report Totals
Hint Ref: 021205120001
Hint Date: 12/05/2012
Hint Details:
ISSUE:
To be able to have a column which is totalled in a Group Footer and the Report Summary but which, on each detail line show this column amount as a percentage of the Group Footer Total and on each Group Footer line shows the percentage of the Report Column Total
EXAMPLE:
I have a report that lists outstanding invoices, grouped by customer. The report shows the outstanding amount for each invoice and both the summed total by customer and overall report total. Next to each of these individual invoice outstanding amounts I want to show the percentage split of the total owing for each customer (in the case of the invoice lines) and the percentage split of the total owing for each customer against the report total (in the case of the group summary lines)
SOLUTION
1. ON THE REPORT DESIGN TAB
On the Invoice line in the 'Detail' of the Report design I have place a DBText field, to display the outstanding amount (called OutAmount), which is called DBText1. On the same line I have placed a Variable called Variable1 to display the percentage.
On the 'Group Footer' of the report I have placed a DBCalc field which SUMs the outstanding amount (OutAmount), which is called DBCalc1. I Right-Clicked on the Field DBCalc1 and selected 'Look Ahead' from the menu. On the same line I have placed a Variable called Variable2 to display the percentage.
On the 'Summary' of the report I have placed a DBCalc field which also SUMs the outstanding amount (OutAmount), which is called DBCalc2. I Right-Clicked on the Field DBCalc2 and selected 'Look Ahead' from the menu.
2. ON THE REPORT CALC TAB
In the 'Report Objects' window (top left) right click and select 'Events'
LOCATE THE VARIABLE: Variable1
Right-Click on'OnPrint' and select 'New'
The Event should contain the following (and assumes the pipeline for OutAmount is called 'Transactions'):
Procedure Variable1OnPrint
begin
Variable1.value := Transactions['OutAmount']/DBCalc1.GetLookAheadValue(DBCalc1.GetLookAheadKey)*100;
end
LOCATE THE VARIABLE: Variable2
Right-Click on'OnPrint' and select 'New'
The Event should contain the following:
Procedure Variable2OnPrint
begin
Variable2.value := DBCalc1.value/DBCalc2.GetLookAheadValue(DBCalc2.GetLookAheadKey)*100;
end