on 10-29-2014 8:08 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
95 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.