Hi,
Here's what you need to do:
1. Create a formula field called "AdminFee" with this code and place it on the "Details" section:
If {Table.Action} = 'Administration Fee' then {Table.Net};
2. Right-click this Formula Field > Format Field > Common tab > Check "Suppress".
3. Create a formula called @Net and replace the Original "Net" field with this formula field on "Details" section:
shared numbervar net; shared numbervar gnet; If {Table.Net} = Maximum({Table.Net}, {Vehicle Reg Number field}) then net := {Table.Net} + Sum({@AdminFee}, {Vehicle Reg Number field}) Else net := {Table.Net}; gnet := gnet + net; net; //Replace {Table.Net} and {Vehicle Reg Number field} with the correct database fields for Net and Vehicle Regn Number.
4. Create a formula called @Vat and replace the Original "Vat" field with this formula field:
shared numbervar net; net * 0.2;
5. Create a formula to Reset the variables and place this on the Group Header:
shared numbervar net := 0; shared numbervar gnet := 0;
6. Create a formula to Display the subtotal for "Net" and place this on the Group Footer:
shared numbervar gnet;
7. Create a formula to Display the subtotal for "Vat" and place this on the Group Footer beside "Net":
shared numbervar gnet; gnet * 0.2;
Hope this helps.
-Abhilash
Conditionally suppress the Admin fees and add a sort to your data so that Admin fees are at the top of each day
Create 2 formula
@reset //Place this in your date group header, header can be suppressed
Whileprintingrecords;
Global numbervar admin:=0;
@display// use your relevant fields in formula place this in position of current net value
Whileprintingrecords;
Global numbervar admin;
If Action = Admin fee then Admin:=Admin+netamount;
If NetValue = Max(NetValue, DateGroup, "daily") then NetValue+admin else netvalue
Your Group total should continue to work as even though Admin fees are suppressed they will be included in sub and report totals
Repeat process for VAT, you can use same reset formula to zero the Vatadmin variable.
Ian
Add comment