calculating totals using parameters 1

[namas]

Using Crystal 8.5

There's 6 columns in a table: year, period,
account,balance_debit, balance_credit,
balance_amount(i.e. balance_debit - balance_credit).
The account balances are by account, year and period.

year period account balance_debit balance_credit balance_amount

2006 0 1000 $10.00 ($5.00) $5.00
2006 1 1000 $100.00 ($50.00) $50.00
2006 2 1000 $200.00 ($500.00) ($300.00)

2006 0 1006 $20.00 ($20.00) $0.00
2006 1 1006 $18.00 ($8.00) $10.00
2006 2 1006 $90.00 $0.00 $90.00


I want to display the 'Beginning balance' of
each account from the above information
based on user selection criteria.

eg., Suppose user selects using parameter field,
year 2006 and period 1
(i.e. start of Range is 1 and end of range is 1),
for account 1000, report should display
beginning balance of $5.00 and the corresponding debit,
credit balance
for period 1 and ending balance amount of $55.00 as below:

year period account Beginning_Balance balance_debit balance_credit Ending_balance_amount


2006 1 1000 $5.00 $100.00 ($50.00) $55.00


Beginning_Balance + balance_debit + balance_credit = Ending_balance_amount

Another example, suppose user selects year 2006
and period 2 (i.e. start of range is 2 and
end of range is 2), for account 1000,
report shouls display as below:


year period account Beginning_Balance balance_debit balance_credit Ending_balance_amount

2006 2 1000 $55.00 $200.00 (500) (245.00)



I created two parameter fields: FiscalYear & FiscalPeriod.
FiscalPeriod accepts range values (0, 1, 2...13).
I grouped by account and got sum of debit, credit,
and balance columns to get the
totals for each field. I am having trouble
calculating the Beginning Balance.
I would appreciate any suggestions on how to solve this problem. I tried several ways w/o success. Thanks.

 

[synapsevampire]

Looks like some odd general ledger report.

the parametrs can't be used to filter data because you're using the start of year to date to determine the account amount.

Of course your ouput example belies this on the 0 period row since you state:

balance_amount(i.e. balance_debit - balance_credit).

So explain how -5 + 5 = 5?

It should be zero, not 5. The rest of the balance_amount values make sense.

But I'll guess that if you use a Running Total summing the balance amount you'll end up with the AMOUNT value you're after.

So figure out how the beginning_amount is being determined and then just use a Running Total of it for the AMOUNT.

Or post back with example data and output (or an explanation that eluded me) that matches the formula shown.

-k

 

[namas]

The beginning balance is being determined by period.
For eg, if user enters period 2, the beginning balance
for period 2 is sum of balance of period0 and period1.

If user enters period3, the beginning balance for period3
is sum of balance of period0, period 1 and period 2.

The formula for balance_amount is:

balance_amount = balance_debit - balance_credit

In this sense, I think parameter field is essential.
Thanks.

 

[lbass]

I think you just need to insert a running total {#endbal} for the ending balance where you select {@balance_amount}, sum, evaluate for each record, reset on change of group ({table.account}). For beginning balance, you can use a formula {@beginbal}:

{#endbal} - {@balance_amount}

If I'm following correctly, you can use the parameter like this in the record selection formula:

{table.year} = {?year} and
{table.period} <= {?period}

-LB

 

[namas]

The above formula gives Beginning balance as 0, though I think it should have been correct.

Another example below - if user selects period 1,
the beginning balance can be calculated using Running total
of account 1000 for both period 0 and 1, and by subtracting the period 0 balance_amount from the Running total amount.
It should also display just the debit and credit amount for that period 1, not the total debit and credit amount of period 0 and 1. Thanks for looking at this.

year period account balance_debit balance_credit balance_amount


2006 0 1000 $10.00 ($5.00) $5.00
2006 1 1000 $100.00 ($50.00) $50.00

 

[namas]

I think the reason Beginning Balance appears zero is:
when I use the selection formula

{table.year} = {?year} and
{table.period} <= {?period}

eg. for following, user enters period 1 of 2006.

year period account balance_debit balance_credit balance_amount


2006 0 1000 $10.00 ($5.00) $5.00
2006 1 1000 $100.00 ($50.00) $50.00

currently report displays

year period account beginning_balance balance_debit balance_credit balance_amount
2006 1 1000 0.00 $110.00 ($55.00) $55.00

I think the problem is Running totalFormula calculates
total as $55 and subtracting the balance_amount of $55
results in 0.

Formula: {table.period} < {?period}
in evaluate section of Running Total did not produce
desired result either.