Most recent opening balance from Account Balance Table 1

[oknuhai]

Hi,

I am trying to pull the opening balance per general ledger account, there is a Balance Table but it only have ending balance for the month if an account has activities.

e.g Acct# 1000-0004-00

PrdEndDate Balance
2/28/2009 200.00
4/30/2009 100.00

I created {?BegDate} and {?EndDate} to pull account transactions(subreport), my formula to get the opening balance is {?BegDate} - 1. So if I am pulling 6/1/2009 to 6/30/2009, the account & balance will not show up.

Can someone please help?

Thanks!!

 

[lbass]

Create a formula like this:

//{@prevdate}:
if {PrdEndDate} < {?BegDate} then {PrdEndDate}

Then use a formula like this to get the beginning balance:

if {@prevdate} = maximum({@prevdate},{table.acct}) then
{table.bal}

-LB

 

[oknuhai]

I am not able to get the formulas to work, does it have something to do with the version of crystal I am using? (Currently using 10 because our accounting system)

and for the second formula, I am suppose to do that in the Select Expert, right?

 

[lbass]

No. Create both formulas in the field explorer->formula->new. The second formula will show you the balance. The date fields should be sorted in ascending order. I guess you would need to conditionally suppress the detail section by using a formula like this:

{@prevdate} < maximum({@prevdate},{table.acct})

Maybe you should explain where you want to show the opening balance--what report section.

-LB

 

[oknuhai]

Thanks LB!

I was getting all the balances before the BegDate, but by using your formula to suppress the detail section does the trick...Now I only get the most recent closing balance.