How to suppress a repeating value? 2

[DannyDutch]

Hi All,

I'm using Crystal XI on a MS SQL 2000 database with an OLEDB connection.

I'm reading a table with purchase invoices.
I have create a formula named ITEMPRICE (amount/quantity) to divide the field 'amount' by the field 'quantity' to calculate the itemprice per item.

The report is grouped:

Group 1 by item
Group 2 by invoice

The formula ITEMPRICE is placed in Group Footer 2.

The report shows the following information:

Itemcode AAA
Invoice 123 $ 100
Invoice 456 $ 100
Invoice 789 $ 110

Actually I only need to see a result if the price is different then the previous price.
The result should look like:

Itemcode AAA
Invoice 123 $ 100
Invoice 789 $ 110

I've tried a formula in the section expert to suppress Group Footer 2 like:
if ITEMPRICE = previous(ITEMPRICE) then true

This results in the message:
"This field has no previous or next value"

Does anybody has a solution, it would be highly appreciated.

 

[lbass]

Please post your actual itemprice formula as used in the report.

-LB

 

[synapsevampire]

You'll need 2 formulas using variables here.

Item Group Header formula:
whileprintingrecords;
numbervar Price:=0

Right click the Invoice group footer section and select insert section below.

Place this formula in the LOWER Invoice group footer (B) section:

whileprintingrecords;
numbervar Price;
if previous({table.item}) = {table.item} then
Price:={table.itemprice}

So we check to make sure we're using the same item group there.

Then suppress this new lower group footer section.

Now right click the UPPER Invoice group footer and select format section->X2 next to suppress and place:

whileprintingrecords;
numbervar Price;
Price = {table.itemprice}

-k

 

[DannyDutch]

Hi lbass,

This is the actual formula:

if Sum ({gbkmut.aantal}, {gbkmut.bkstnr})= 0 then 0 else
Sum ({gbkmut.bdr_hfl}, {gbkmut.bkstnr})/Sum ({gbkmut.aantal}, {gbkmut.bkstnr})

 

[DannyDutch]

Hi Synapsevampire,

I understand that the first formula sets the price to zero.

The second formula checks if the previous item is equal to the current item.

the third formula will suppress the section if Price = {table.itemprice}.

I've tried your solution but it goes wrong with the second formula. The result is 1 invoice per item.
Group 1 is the item and this makes it sure that the item group is correct.
So because the previous value is the cuurent, the report returns only 1 invoice per item.

I hope that I've explained it in a correct way to you.

 

[lbass]

Try using this in the section expert->group footer->suppress->x+2 area:

currencyvar itemprice;
numbervar cnt;

if itemprice = {@yourformula} then
(
itemprice := itemprice;
cnt := cnt + 1
) else
(
itemprice := {@yourformula};
cnt := 1
);
cnt > 1

-LB

 

[DannyDutch]

Hi lbass,

This solution works great!
Thanks for your help.

Now I have the last question regarding the report:

For some items I see i.e. 50 lines.
I like to add a parameter which asks for the maximum nuber of lines (invoices).

How can I include this in your formula?

Thanks again.

 

[synapsevampire]

If a price is the same at the end of the item group and the beginning of the next item group, I think that LBs formula will suppress it, which I wouldn't think you'd want.

Not sure what failed in mine, but if you add in the check for the previous ITEM group to LB's formula you should be fine there as well, it's a better means anyway.

-k

 

[lbass]

Change the formula to:

currencyvar itemprice;
numbervar cnt;
numbervar invcnt := invcnt + 1;

if itemprice = {@yourformula} then
(
itemprice := itemprice;
cnt := cnt + 1
) else
(
itemprice := {@yourformula};
cnt := 1
);
cnt > 1 or
invcnt > {?numberofinvoices}

To address SV's apt concern, you would need to add a reset formula in the item group header:

whileprintingrecords;
currencyvar itemprice := 0;
numbervar cnt := 0;
numbervar invcnt := 0;

-LB

 

[DannyDutch]

Hi lbass,

You've solved the problem, I'm very happy!!!!
Also thanks for the contribution from SV!