How to print a formula using two running totals in a group header ? 1

[johnwolf]

Hello,

Using CR 8 / Oracle database.

I am new to CR
I am programming a report where I calculate the maximum annual savings in costs per print using generic against original toners

Data sample

PRIMARY_REF OEM_REF CAPACITY TONER_TYPE PRICE PRINTER_NAME

aaa Q5949A 2500 OEM $50 HPLJ1320
bbb Q5949X 6000 OEM $80 HPLJ1320
ccc Q5949XD 12000 OEM $150 HPLJ1320
ccc Q5949X 6000 GENERIC $40 HPLJ1320

I print articles details, grouped by PRINTER_NAME, and in the group footer I use 2 different conditional totals using the minimum function based on the following data: cost per print (@PRICE/CAPACITY), with conditions (TONER_TYPE= “OEM”) , (TONER_TYPE=”GENERIC).

Then I have a formula in the group footer using the difference between the 2 totals that I multiply by a constant (annual number of prints) to get the final result.(annual savings in $)

Until here, everything fine, though I don´t know if this is the most efficient way (I´ll have to print dozens of printers en each report, and some are using much more supplies).

Now if I want to put this formula in the header, it won't print. I have read user´s manual on CR two pass, and also many threads on group headers and running total and I am still confused if it is possible or not to have this information in the group header.

Thanks in advance for any help

 

[IanWaterman]

The RT has not been evaluated at that point.

YOu can split your footer into two sections and then display all of your group header data in the Lower GF section after the RT has evaluated.

Ian

 

[johnwolf]

Thanks for your answer!

If I understand well, this solves one of the issues: name of the printer and annual savings are together.

But I need to print the details and to have name of the printer and annual savings before them and your answer does not solve this issue.

Am I right?

 

[IanWaterman]

YOu are correct.

The only other option is to create a sub report which does the RT calculations for you, you can then pass this info from sub report to main report using shared variables.

YOu would have to put subrepor in group Header in section above display.

Down sie is that SR would have to execute for each printer. This may have a speed impact on your report.

Ian

 

[lbass]

You might be able to do this with conditional formulas, but I can't tell what the calculation is that you are doing in the group footer. Please show the content of the calculation, and the content of any formulas which it contains.

-LB

 

[johnwolf]

Aim of the report is to make a commercial offer for all the supplies a consumer can buy for its printers, stressing the savings he makes with generic supplies.

First I created a group#1 on OEM_REF that enable to print OEM and Generic prices (as totals) on a same line and compare them easily.

I supressed details and it is like

GROUP#1 FOOTER
OEM REF OEM_PRICE GENERIC_PRICE OEM_CPP GENERIC_CPP
Q5949A $50 $0 0.0200 0.0000
Q5949X $80 $40 0.0133 0.0067
Q5949XD $150 $0 0.0125 0.0000

Formulas in details are all made the same (prices, capacity, cost per print), like this

@GENERIC PRICE
if {TONER_TYPE}="GENERIC" then {PRICE}

...

@GENERIC_COST_PER_PRINT
if {@GENERIC_CAPACITY}>0} then {@GENERIC_PRICE}/{@GENERIC_CAPACITY}


Then I have another group#2 based on PRINTER_NAME

In the footer of group#2 is where I place the running totals

I used the wizard for both @GENERIC_COST_PER_PRINT and @OEM_COST_PER_PRINT with the Minimum() summary function,

and an evaluation formula like

{@GENERIC_COST_PER_PRINT }>0 and {PRIMARY_REF}[2]= "X" and {COLOR}="K"

Nota: in the data sample in the first post I did not put other fields like color, and other lines like fuser or maintenance kit that do appear in group#1 footer. The condition {PRIMARY_REF}[2]= "X" make me select toners only. Condition on color will help me do the same calculation for color laser printers.


with a reset on group#2

Those two running totals are #OEM_MIN_COST_PER_PRINT and #GENERIC_MIN_COST_PER_PRINT.

the final formula is

@SAVINGS
"Annual savings are : $" + Totext(#OEM_MIN_COST_PER_PRINT-#GENERIC_MIN_COST_PER_PRINT)*48000


GROUP#2 FOOTER
#OEM_MIN_COST_PER_PRINT #GENERIC_MIN_COST_PER_PRINT
0.0125 0.0067
Annual savings are : $278

Hope it is clear thanks to all for your help

 

[lbass]

Create a formula {@null} by opening and saving a formula without entering anything. Then instead of using running totals, create like this:

//{@Generic}:
if {@GENERIC_COST_PER_PRINT}>0 and
{PRIMARY_REF}[2]= "X" and
{COLOR}="K" then
{@GENERIC_COST_PER_PRINT} else
tonumber({@null})

Create a second formula {@OEM} for the OEM amount like the first. Then create a formula that you can place in the group header:

//@SAVINGS:
"Annual savings are : $" + Totext(minimum({@OEM},{table.group2field})-minimum({@generic},{table.group2field}))*48000

You can display the minimum amounts of each in the group #2 footer by inserting minimums at the group level on the detail formulas.

-LB

 

[johnwolf]

Solved, works fine, thank you.
One question: what is the use of the {@null} formula ?

I thought that tonumber({@null})would give 0 as a result and apparently it is not the case, it prints nothing.

 

[lbass]

By using the {@null} formula the inserted minimum picks up the lowest non-null value, instead of the implied result of 0 if you didn't use {@null}.

-LB