Today's & Yesterday's Running Total and Variance 1

[fryguy5049]

I have read many previous posts but none seem to address the problem of getting yesterday's (or last years) running totals. I am using CR 10 & MySql. The data looks like this:
ACCT AMT DATE
1000 53 08/01/2004
1200 11 08/01/2004
1000 43 08/02/2004
2000 56 08/01/2004
2000 33 08/02/2004
3000 88 08/01/2004
3000 99 08/02/2004
I want the report to look like:

ACCT 08/02/2004 08/01/2004 VARIANCE
1000-2000 43 54 (11)
2000-4000 132 144 12
The problem I'm having is trying to get a previous total of a summary field. Now I have a formula for each ACCT group and then a summary to total any records the fit within the criteria of the formula. Is there a way to get yesterdays totals? Thanks!!

 

[bdreed35]

Create a formula to group yor records:

@ACCT

if acct field is text, then put double quotes in like this: ["1000" to "2000"]
if {table.acct} in [1000 to 2000] then 
    "1000-2000"
else
    "2000-4000"

Insert a group on this formula.
Create another formula to return the amounts for yesterday and place the formula in the details section:

@Yesterday

if {table.date} = currentdate - 1 then
    {table.amount}
else
    0

Create another formula to return the amounts for today and place the formula in the details section:

@Today

if {table.date} = currentdate then
    {table.amount}
else
    0

Right click each of the two formulas and insert a sum at the ACCT group level.
Move the two summaries from the group footer to the group header.

Create one more formula to get the variance.

@Variance

sum({@Today},{table.acct}) - sum({@Yesterday},{table.acct})

Place this formula in the Group Header as well.
Suppress the details section and the group footer.
Lastly, go to Report, and select Perform Grouping on Server.


~Brian

 

[fryguy5049]

Thanks, that is very helpful. Lets say now that I want to perform a subtotal on accounts 1000-4000 ( I will probably do this 4 or 5 times throughout the report). What kind of formula would I use and where would it go? Thanks Again.
Chris

 

[bdreed35]

Can you explain more on how your report is structured?

What groupings do you have?
How high do the Acct Numbers go?

A sample output would be very helpful. Something like what you have in the first post, only showmore of the report.

A more accurate answer can be given with this info so we don't go down the wrong path.

~Brian

 

[fryguy5049]

The rate for acct is 1000-9999. The report is a income statement. Which shows totals from groups of accts(1000-1999) and also can just be the total from one specific account(5500). Here is the format:
today(date) yesterday(date) variance
Operating Expense
Salaries (acct 7000-7006) $1000 $1500 $(500)
Mktg. Expense (acct 7007) $45,000 $43,000 $2000

Total Opearting Expenses $46,000 $44,500 $1500
There is about 5 lines in the report I need to take the sum of 4 lines preceding it (sub-totals). Is there an easy way to accomplish all this? Thanks