How to only show the line with the last that is not zero.

[Former Member]

I would like to create a formula that shows only the last line that is not equal to zero. As you can see in the picture below (left), all of the lines are being shown in the report even if the quantity is zero. Is there a way I could do a "if then" statement like If {compQty_form} = 0 then use previous line. I would also like for it not to show the lines that are zeros or the lines before, only the line that is just before the first zero if possible.

     

[former_member292966 · Accepted solution]

Hi Amanda,

What we can do it use 3 formulas to create a running total.  The first will declare and initialize a variable.  The second is to evaluate the quantity.  If it's not zero then we'll pass it to the variable. The third formula will display the variable which should be something other than zero if there are records.

@Initialize

WhilePrintingRecords;

NumberVar showQty := 0;

Drop this formula into the Group  Header 1 section and you can suppress the formula so you don't see it.

@GetQty

WhilePrintingrecords;

NumberVar showQty;

if  {compQty_form} <> 0 then

     showQty :=  {compQty_form};

Drop this into the Detail section and also format to suppress.

@ShowQty

WhilePrintingrecords;

NumberVar showQty;

Drop this into your Group Footer.  It should show the last non-zero number it found in the Detail section.

Now, how to show only the record before the first zero, that I'll have to think about.

Good luck,

Brian

[Former Member · Accepted solution]

Brian,

Its says a string is required for the <> 0 in the second formula.

For His Kingdom,

Amanda

[former_member292966 · Accepted solution]

Hi Amanda,

If the field is a string then we can change the formula to accomodate it like:

@GetQty

WhilePrintingrecords;

NumberVar showQty;

if  ToNumber ({compQty_form}) <> 0 then

     showQty :=  ToNumber ({compQty_form});

I'm using the ToNumber function to convert the field to a number.

Just confirm that your field name is compQty_form.  Crystal normally has a field as table.fieldname.

Thanks,

Brian