Running Total of Info not Displayed

[basquiat]

I’ve got the following very irritating problem…

I’ve got a table with 2 fields, account number and balance;
Account Balance
A 100
B 200
C 300
D 400
E 500
F 600

On my report I only want account D, E and F (in that order) and the balance next to it;

When the report gets to account E it should take the value of E (500) and add the running total of account A to C to it [500 + (100+200+300)] = 1100

The report should look like this….
D 400
E 1100
F 600

I’ve added a running total for A, B & C (RunTotal) and changed the formula for the balance to…
If Account = “E” then
Formula = Balance + RunTotal
Else
Formula = Balance
End if

But I get the following error ...“A running total cannot refer to a print time formula”
I know that is because the value calculated is not calculated at a time that the running total needs it….but how do I get around it?

I don't think it's possible to use a summary since the balances to be summarized is not included on the report;

Any help would be most welcome

 

[synapsevampire]

I think that your unusual businjhess requirements require a more convoluted formula, such as:

whileprintingrecords;
numbervar AtoC;
numbervar D;
numbervar E;
numbervar F;
If account in "A" to "C"
then
AtoC := AtoC+{table.value}
else
If Account = "D" then
D:={table.value}
else
If Account = "E" then
E:={table.value}
else
If Account = "F" then
F:={table.value}

Now you can refer to this formula from another formula for display or do so within this formula by using the same variables defined here.

-k

 

[lbass]

Why not just create a formula:

if {table.acct} in ["A","B","C","E"] then "E" else {table.acct}

Insert a group on this formula and then insert a sum on {table.amount} at the group level.

-LB

 

[basquiat]

Thank you for the feedback;

Both your methods still give me the error…“running total cannot refer to a print time formula”
I think it’s because it’s doing the calculation during pass #2 and I need it during pass #1?

I am trying to write a Balance Sheet (for Financial Statements);
The report is based on a table, almost like a Trial Balance;

The table has a field with all the general ledger account number from “1000000” to “9999000” and a field with the balance of the account;
“1000000” to “4999000” are Income Statement accounts…I don’t want to display them on my Report;
And “5000000” to “9999000” are the Balance Sheet accounts I need;

The problem is that account “5200000” (the Retained Income account) only shows the opening balance and not the current periods profit;
So I have to add the balances of accounts “1000000” to “4999000” to determine the Net Profit for the selected period and add that to the balance of “5200000” to get to the total Retained Income and to get my Balance Sheet to balance;

Note that adding “1000000” to “4999000” would give the profit and so would adding “1000000” to “9999000” ….maybe that opens up some other options?;

Your help is much appreciated….I’ve really been struggling with this one;

 

[lbass]

So are you saying that "balance" is not a field, but a formula? Please provide the contents of the formula.

-LB

 

[basquiat]

There's a field "AccNumber" holding the account numbers;
Then there are 24 fields with the balance/movement for each month of the current year (BalanceThis01, BalanceThis02 .... BalanceThis12) and previous year (BalanceLast01, BalanceLast02 .... BalanceLast12);

The balance field holds only the movement on the account for that period;
For example: The field "BalanceThis01" for "AccNumber" '1000000', shows the Fee Income for January 2005; ('1000000' being the account number for Fee Income');
T
he ‘BalanceLast01’ field is different from the other 23 balance fields because it holds the opening balance of the balance sheet accounts plus the movement during January 2004; (2004 being the ‘previous’ or ‘last year’

The thing with a Balance Sheet is that if you run it for say March 2005 you dont only want the movement for March on your Balance Sheet items (like Bank, Fixed Asset ect.) you want the total of the these account including their opening balance;

I’ve created a formula called ‘Opening Balance’;
Formula = ({LedgerMaster.BalanceLast01} + {LedgerMaster.BalanceLast02} + {LedgerMaster.BalanceLast03} + _
{LedgerMaster.BalanceLast04} + {LedgerMaster.BalanceLast05} + {LedgerMaster.BalanceLast06} + _
{LedgerMaster.BalanceLast07} + {LedgerMaster.BalanceLast08} + {LedgerMaster.BalanceLast09} + _
{LedgerMaster.BalanceLast10} + {LedgerMaster.BalanceLast11} + {LedgerMaster.BalanceLast12})

This formula adds all the balances of the previous to get to the opening balance of the current year;

I've created a period parameter;
So the user will select the month he wants to view;

Then I created a formula field called 'Current';
This formula field have 12 'Select Case' statements....

Select Case {?Period}

Case 1
Formula = {@OpeningBalance} +{LedgerMaster.BalanceThis01}


Case 2
Formula = {@OpeningBalance} + LedgerMaster.BalanceThis01} + {LedgerMaster.BalanceThis02}

...and so on;

This works just fine for all my Balance Sheet Accounts except for the Retained Income account;
The reason is that the only retained income balance lies in ‘BalanceLast01’ which is in fact the retained income up to December 2003;

So if I run a balance sheet for say January 2005 the retained income entry would only show the retained income up to December 2003;

If the user select period 1, I want to say this in my ‘Current’ formula field…..

Select Case {?Period}

Case 1
If {LedgerMaster.AccNumber} = “5200000”
Then
Formula = {@OpeningBalance} of “5200000” + sum of all the {LedgerMaster.BalanceLast01} up to {LedgerMaster.BalanceLast12} for accounts ‘10000000’ to ‘4999000’ + sum of LedgerMaster.BalanceThis01} for all accounts ‘1000000’ to ‘4999000’
Else
Formula = {@OpeningBalance} +{LedgerMaster.BalanceThis01}
End If

…this would then take the Retained Income of 2003 (@OpeningBalance) and add all the monthly profits for 2004 to it and then add the profit for January 2005 to it, to get to the total retained income;

I hope this makes sense?

Thanks

 

[lbass]

I'm not sure I follow all of this, but if you created two running totals {#balLast} and {#balThis}. For {#balLast} you would sum {@OpeningBalance}, evaluate using a formula:

{LedgerMaster.AccNumber} in ‘10000000’ to ‘4999000’

Reset never. Repeat for #balThis, substituting your formula for this year's balance. Then create a formula:

if {{LedgerMaster.AccNumber} = “5200000” then {@Openingbalance}+{#balLast}+{#balThis} else
{@Openingbalance} + {@Thisyearsbalance}

-LB

 

[basquiat]

Thanks lbass;
I changed the way the report was grouped and followed your advice above;
Works fine now;

Thank you very much for the help;