Adding Two Variables in the Group Footer 1

[blueboyz]

Crystal Reports v9
Peachtree PSQL database
Btrieve connection

I have an existing report displaying items grouped by a field and then grouped by location and listing the # of cases sold per month, with a YTD Case Total and a column for Last Year YTD Total.

The group 2 is based on whatever is in the item's location field. The group options has the following formula:

If {LineItem.Location} = "a" then "7 DD"
else If {LineItem.Location} = "b" then "12 ORIG"
else if {LineItem.Location} = "c" then "14 DD"
else If {LineItem.Location} = "d" then "16 ORIG"
else If {LineItem.Location} = "e" then "STUFFED PIZZA"
else If {LineItem.Location} = "j" then "7 ORIG. B.S."
else If {LineItem.Location} = "jj" then "7 ORIG. B.S. NB"
else If {LineItem.Location} = "k" then "4 HFRF B.S. 144"
else If {LineItem.Location} = "kk" then "4 HFRF B.S. 216"
else If {LineItem.Location} = "kkk" then "7 HFRF B.S. 108"
else if {LineItem.Location} = "l" then "7 ORIG. B.S. 18"
else if {LineItem.Location} = "ll" then "7 ORIG. B.S. 36"
else if {LineItem.Location} = "lm" then "4 B.S.S.P. 32"
else if {LineItem.Location} = "m" then "4 B.S.S.P. 8"
else if {LineItem.Location} = "ma" then "4 B.S. 9"
else If {LineItem.Location} = "n" then "7 B.S.S.P. 72"
else if {LineItem.Location} = "oa" then "4 PEPP B.S. 144"
else if {LineItem.Location} = "oo" then "4 PEPP B.S. 216"
else if {LineItem.Location} = "p" then "4 JPJ B.S. 144"
else if {LineItem.Location} = "pp" then "4 JPJ B.S. 216"
else If {LineItem.Location} = "q" then "7 APPLE B.S. 72"
else If {LineItem.Location} = "qq" then "7 APPLE B.S. 1B"
else If {LineItem.Location} = "z" then "B SAUCE"

The report hides the Group Header and the Details.

If the item's location is "kk", "oo", or "pp"
then I am suppressing that row in the group footer.

I am trying to add the suppressed row to the row above it for the column titled "Last Year YTD".
Before I can add the row, the suppressed amount has to be converted using a formula:

{@KK} = WhilePrintingRecords;
NumberVar KK := (If {LineItem.Location} ="kk" then {@Convert 4HFRF LYYTD})

{@KKConvert} = if {LineItem.Location} = "kk"
Then (Sum({@LAST YR YTD CASES}, {LineItem.Location}) * 216)/144

I also have a formula for the line above the “kk” item:

{@K} = WhilePrintingRecords;
NumberVar K := (If {LineItem.Location} ="k" then Sum ({@LAST YR YTD CASES}, {LineItem.Location}))

I placed {@K} and {@KK} in the Details Section (that is suppressed).

I created variables to initialize {@K} and {@KK} and placed them in the Group 1 Header. I also created variables to Display {@K} and {@KK} in the Group 2 Footer.

I created another variable to add {@Display K} and {@Display KK}:
{@K+KK} = WhilePrintingRecords;
NumberVar K;
NumberVar KK;
NumberVar Total;
if {LineItem.Location} = "k" then
K := Sum ({@LAST YR YTD CASES}, {LineItem.Location});
if {LineItem.Location} = "kk" then
KK := {@KKConvert};
if {LineItem.Location} in ["k","kk"] then
Total := K + KK;

I placed {@K+KK} in the Group Footer #2. (I did not create a variable to initialize {@K+KK}).

The report does not add {@K} + {@KK} in the LAST YEAR YTD column. It only shows the value of what is in {@KK}.

How do I get the report to display the total of {@K} and {@KK}?


Thank you for your help.

 

[Madawc]

If you want to add values from lines with 'kk', a running total should do it, with the formula set to test it. Or if it also has to follow a line with 'k', use the Previous command.

The use of Crystal's automated totals is outlined at FAQ767-6524. They are much less work than writing your own variables.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

You can't get the variable to show the correct result in a row preceding the calculation. I think you should approach this differently. If you want two rows to combine, then create an outer group that combines the two, as in:

if {LineItem.Location} in ["k","kk"] then "ks" else
if {LineItem.Location} in ["o","oo"] then "os" else
if {LineItem.Location} in ["p","pp"] then "ps" else //etc.

You could make a conditional adjustment to the amount field in a separate formula, something like:

if {LineItem.Location} = "kk" then
({table.amt}*216)/144 else
{table.amt}

This could then be summed at the outer group level.

-LB

 

[blueboyz]

Thank you LB for your solutions. I did try to come at the report from another point of view. I created a formula that included

if {LineItem.Location} in ["k","kk"] then "ks" else
if {LineItem.Location} in ["o","oo"] then "os" else
if {LineItem.Location} in ["p","pp"] then "ps" else //etc.

Next, I created a group based on this formula. The row magically rolled up to the row above it and the totals are all correct now. Originally, the formula was created in the group options, but the group rows appeared once for {LineItem.Location} = "k" and once for {LineItem.Location} = "kk". Once I created the formula {@Item Group} =

If {LineItem.Location} = "a" then "7 DD"
else If {LineItem.Location} = "b" then "12 ORIG"
else if {LineItem.Location} = "c" then "14 DD"
else If {LineItem.Location} = "d" then "16 ORIG"
else If {LineItem.Location} = "e" then "STUFFED PIZZA"
else If {LineItem.Location} = "j" then "7 ORIG. B.S."
else If {LineItem.Location} = "jj" then "7 ORIG. B.S. NB"
else If {LineItem.Location} in ["k", "kk"] then "4 HFRF B.S. 144"
else If {LineItem.Location} = "kkk" then "7 HFRF B.S. 108"
else if {LineItem.Location} = "l" then "7 ORIG. B.S. 18"
else if {LineItem.Location} = "ll" then "7 ORIG. B.S. 36"
else if {LineItem.Location} = "lm" then "4 B.S.S.P. 32"
else if {LineItem.Location} = "m" then "4 B.S.S.P. 8"
else if {LineItem.Location} = "ma" then "4 B.S. 9"
else If {LineItem.Location} = "n" then "7 B.S.S.P. 72"
else if {LineItem.Location} in ["oa", "oo"] then "4 PEPP B.S. 144"
else if {LineItem.Location} in ["p", "pp"] then "4 JPJ B.S. 144"
else If {LineItem.Location} = "q" then "7 APPLE B.S. 72"
else If {LineItem.Location} = "qq" then "7 APPLE B.S. 1B"
else If {LineItem.Location} = "z" then "B SAUCE"

and then replaced my original Group #2 with {@Item Group}, the report worked like a charm.


Thank you.