Prior Balance 1

[BB45]

CE10.0
SQL 2000

I am trying to get the balance of a field prior to the LastFullMonth. I have built the following into a subreport because my main report returns the LastFullMonth. Is there a way incorporate AllDatesToToday as this does not seems to be working. Here is what I am using in my subreport:

if {Table.Date} < LastFullMonth
then sum ({table.Amount},{table.clientname})

The number I am getting is way too low. Also, I have linked my subreport to the clientname in the main report.

Thanks
BB

 

[synapsevampire]

You don't need a subreport, but since you aren't looking for optimizing, rather how to use a subreport for a date range, alter the subreport's Report->Selection Formula->Record to reflect:

{Table.Date} < minimum(LastFullMonth)

-k

 

[BB45]

How can I do it without the subreport?

Thanks
BB

 

[synapsevampire]

Pull all of the data into one report, then use Running Totals with Evaluate->Use a Formula to identify each aggregate period required.

-k

 

[kskid]

If you are working with a large database, I would get with your dba and create a view of the prior balance. Then all you have to do is left outer join the prior balance to the lastfullmonth records.

Just a thought.

Another alternative would be to create a separate report with end of year balance and export to an Excel file. I say end of year, so you only have to run it once a year and insert it into you main report as a subreport using Excel as your datasource. As for your main report, select all records YTD, link to the subreport containing the Excel spread sheet to pick up previous year,s balance and add them together.

Cheers,
-LW

 

[BB45]

What I am trying to get is like a credit card statement where it will show your prior balance and then the detail of the transactions for the LastFullMonth. How would those formulas need to be setup? I tried a could of "if" statements based on the individual transactions but it is printing all records. What I want to see is:

transID Type Amount
1 Charge $100
5 Payment $50

Prior Balance $300
Current Balance $350

Thanks
BB

 

[kskid]

Use running totals.

#pbcharge: sum all charges < minimum(lastfullmonth)
#pbpayment: sum all payments < minimum(lastfullmonth)

Formula
@pbbalance: #pbcharge - #pbpayment

#lmcharge: sum all charges in lastfullmonth
#lmpayment: sum all payments in lastfullmonth

Formula
@curbalance: @pbbalance + (#lmcharge - #lmpayment)

Place all of the above running totals and formulas in the group footer and suppress any field you don't want to see.

Cheers,
-LW

 

[BB45]

Ok I think I got it figured out

Thanks
BB

 

[synapsevampire]

Right, as in "Pull all of the data into one report, then use Running Totals with Evaluate->Use a Formula to identify each aggregate period required."...

-k