Revenue Total Divided by Select Expense Accounts

[AntonioPerera123]

Hi All,

My Report needs to look like this :
(Need to calculate the % against total revenue for Accounts 8400 & 8500 only)


REVENUE
6000 Takings $1000
6100 Sales $2000
6200 Sales - Beer $2000
Total Revenue $5000

EXPENSES $
8000 General Expenses $1000
8123 Wages $500
8125 Other Expenses $900
8400 Cleaning $760 15.2%
8500 Waste $450 9%

I am having trouble in getting the revenue total to line up against expense lines 8400 & 8500 only so that I can calculate the Percentage
Any ideas on how to achieve this?
(report attached for reference) - please note the above example is simplified, actual report is a more complex one.

 

[briangriffin]

Assuming group 1 is transaction type - Revenue, Expenses - and that the account totals are in the details section...

Create this formula and place in the details section:

whileprintingrecords;
numbervar v_expenses;
if 
{Group1Name} = "Expenses"
and
{AccountNumber} in [8400,8500]
then v_expenses := v_expenses + {AccountTotalField}
else v_expenses;

numbervar v_revenue;
if {Group1Name} = "Revenue"
then v_revenue := v_revenue + {AccountTotalField};

v_expenses

You should see it increment to only display the total of accounts 8400 and 8500.

Then create this formula and place it in the report footer:

whileprintingrecords;
numbervar v_expenses;
numbervar v_revenue;

v_expenses%v_revenue

That should display the % you're looking for. If the account totals are actually in the group 2 footer, then place the first formula in the group 2 header and it should still work.

This will take some tweaking but you get the idea - have the variable look at the account number and then either include or exclude the amount.

If you are testing for a lot of accounts, you can use ranges, discrete accounts, or both:

{AccountNumber} in [8400 to 8500, 8600, 8700 to 8900]