Beginning Balance Calculation 2

[paljnad]

Hello,

I have a crystal reports 10.0 report that displays all the transactions from the general ledger for a given day or a range of days for a given account or a range of accounts.

For example, if the input parameter values for start date and end date are 01/06/2009 and 01/07/2009 then it displays all the trasactions that hit the ledger for that day for the accounts that are specified. The users could input just one account or a range of accounts.

The Crystal Report has 3 groups - account number, account description and calendar accounting period (it will be 2009-01-31 for the input dates above)

I have a running total displayed on the report that will add the monthly beginning balance to the amount posted for the input dates and accounts.

For example if the monthly beginning balance is 100 and the amounts posted for 1/6 and 1/7 are as follows:

20, 30, 40, 50, 100

then the report displays this:

Beg Balance: 100

Amounts for 01/06:20 Running Balance: 120
30 150
40 190
50 240
Amounts for 01/07:100 340


But this is incorrect because there could have been transactions posted from 01/01 - 01/05 which is not included in the running total.

I am using 2 formulae to calculate the running total

@balance
WhilePrintingRecords;
numbervar balance;
balance := balance + {AMOUNT}

@newbalance
{@Balance} + {MTD_BEG_BALANCE}

Any help will be appreciated.

Thanks.

 

[IanWaterman]

YOu have two choices.

Create a table in database which holds opening balances for each month. This is best solution as it reduces data set.

Bring back all data and calculate opening balance on fly, then conditionally suppress data from prior dates.

eg in detail line conditionally suppress

table.datefield < startdateparam

This will get slower and slower to process as your database builds up.

Ian

 

[lbass]

If your beginning balance is a field based on the prior period, then set the period of the report to include the period from day one through the end date. Then you can simply suppress the records that are less than the start date, but they will still contribute to your running totals, though not visible.

-LB

 

[paljnad]

Thank you both.

I was able to bring data from the beginning of the month to the end date supplied as an input parameter and suppress the data before the start date which was also supplied as a parameter.

 

[paljnad]

Okay, now the users want to be able to see a daily beginning balance.

So the monthly beginning balance and the running total amount (@newbalance from my original post) are displaying the right numbers but the users want to be able to see a daily beginning balance which is the ending balance of {?start date} - 1.

And the daily ending balance which is the last number on the detail row of the running total.

Is there a way I can do this?

 

[IanWaterman]

Just do a running total for each day, group report by day and resets RT on change of day group.

Change your suppression rules so that group footer shows for day preceding your start period

Ian

 

[lbass]

For the ending balance, just place a copy of your current running total balance in the footer where you want the ending balance displayed. For the beginning balance, I think you just need to change the suppression formula to be:

{table.date} < {?startdate}-1

If this isn't what you mean, you'd better show us what you are looking for with some sample data.

-LB

 

[paljnad]

Hello,

Some sample data:

MTD Beg Balance for October: 100

Daily Beg Balance for 10/01: 100
Amount on 10/01: Running Total
50 150
20 170
30 200

Daily Ending Balance for 10/01: 200

Daily Beginning Balance for 10/02: 200
Amount on 10/02: Running Total
20 220
30 250
50 300
Daily Ending Balance for 10/02: 300


The mtd beg balance is displayed in the group header section. The amount and the running total are displayed in the detail section. I have suppressed the detail section to not display any records before the start date (which is supplied as an input parameter) even though the report calculates the running total from the first day of the month until the end date (supplied as a parameter). This is because the start date might not be the first day of the month (it is in the above example, though !)

Thank you .

 

[lbass]

Insert a group on date on change of day, and then copy the running total into two group footer sections for day. Label the first as ending balance using a formula like this:

"Daily Ending Balance for " +totext({table.date},"MM/dd")+": "

For the second day group footer (which will act as a fake header for the following day) use the following:

"Daily Starting Balance for " +totext(next({table.date}),"MM/dd")+": "

Conditionally suppress GFb with this formula so that it doesn't appear at the end of the report:

onlastrecord

-LB

 

[paljnad]

I think its really close now.

When I put the running total in the GFa and GFb sections then it seems to add the last amount.

From the example above, GFa and GFb show 230 and 350 for the 2 days.

 

[lbass]

Sorry, I was thinking you were using inserted running totals. What is the content of the formula you are using for the running total?

-LB

 

[paljnad]

I have two formulae:

@balance
WhilePrintingRecords;
numbervar balance;
balance := balance + {LLICGENLEDGERTRANS.CONVERTED_AMOUNT}

@newbalance:
{@Balance} + {LLICGENLEDGERTRANS.SUM_MTD_BEG_BALANCE}

@newbalance is what I am displaying as the running total in the detail section.

Thank you.

 

[lbass]

Change {@newbalance} to:

WhilePrintingRecords;
numbervar balance;
balance + {LLICGENLEDGERTRANS.SUM_MTD_BEG_BALANCE}

Then you can use this in the group footers.

-LB

 

[paljnad]

It works like a charm.

Thanks!