Using a Running Total in a formula 1

[pconnick]

Crystal Report 10

Database: Oracle 9i

I have a running total in the group 1 footer for total sales

Group 1 Dept
Group 2 Season Code

I want to divide season code totals by dept totals and insert this field in group 2 footer

sum(sales) by season code / running total by dept

The reason why I am using a running total instead of sum is becasue the user wanted to exclude a couple of season codes in totals.


sum(sales) by season code is correct

but

How do I divide group level 1 value in group level two formula.

 

[bdreed35]

The problem is that your Running Total won't have the correct value until the Group Footer prints.

I would create a formula that returns either the sales, or 0 depending on the season code:

If not({table.season_code} in ["1","2","3",etc]) then
{table.sales}
Else
0

This will evaluate for each record returned from the database.
You can now create a standard summary off of this field, and then use that summary field in your calculation instead of the Running Total.

~Brian

 

[pconnick]

I apologize; I didn’t give you enough information. Based on what I wrote you solution is correct.

Groups

Dept
Sort (season codes group)
Class
Subclass
Etc.

Sort

Spring/Summer (1)
Fall/Winter (2)
Year Round (3)
Spring/Summer/Fall/Winter (4)
Basic (5)
Discontinued (6)

I want to exclude group 4 from my totals.

In group 1 footers season code does not exist. That is why I went with the running totals.

I tried to create a formula to sum the total sales

if {Command.SORT} = 4 then

0

else

Sum ({Command.UNIT SALES TY}, {Command.DEPT})

And this formula

if {Command.SORT} <> 4 then

Sum ({Command.UNIT SALES TY}, {Command.DEPT})

else

0

I am still getting (Spring/Summer/Fall/Winter (4)) values in my total

 

[pconnick]

dbreed35

I must be brain dead today. Your solution did work.

Thanks

 

[synapsevampire]

You didn't try as Brian suggested.

Post example data, and expected output, not a text description of what it might be, or shouldn't be.

This "In group 1 footers season code does not exist. That is why I went with the running totals." doesn't make sense, if an expected output from a group doesn't exist, you wouldn't need to code for it.

To eliminate a specific season code from its group, create a formula in the details as Brian suggested:

@ValidSales
If not({table.season_code} in ["1","2","3",etc]) then
{table.sales}
Else
0

Now you can use something like the following in any group header or footer:

sum({@ValidSales},{table.group})

The {table.group} being whatever group you want the sum for, so if you're doing a percentage:

You would need this for every group level to eliminate the data from those totals as well.

Much more simply, filter that data from the report by using:

Report->Selection Formulas->Record:

{table.seasoncode} = "Spring/Summer/Fall/Winter"

-k

 

[pconnick]

synapsevampire,

Thanks for your input. Brian's suggesstion did work.