Skip to Content
author's profile photo Former Member
Former Member

Showing Zero if field is null

Hi All,

I have a report having qty fields.I need to show as zero when value is null.

So wrote a formula like this.

@Target Qty:

if is null(qty) and is null(act qty) then 0 else qty * act qty.

I placed this is in detail section and working fine.

Then I created a summary sum(@Target Qty) for this.This also should show as zero when the qty fields are null.

I placed this in report footer .Whenever i kept this in report the @Target Qty which is in detail section is hiding.If i removed summary formula then @Target Qty is showing.

Please suggest what is the problem

Add comment
10|10000 characters needed characters exceeded

1 Answer

  • Best Answer
    Posted on Sep 09, 2013 at 09:50 AM

    Hi Divya,

    So if I understand this correctly, you have a formula (@Target QTY) placed in the Details Section.

    You've then inserted a summary on the formula and placed it on the Report Footer. After the summary is paced on the Report Footer, the formula field on the Details Section disappears?

    Do you have a conditional suppression logic for the field or the section? Are you sure this is what is happening?

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Divya,

      Thank you for attaching the report.

      I tried creating a sample report with a similar formula and for some reason CR does the same thing.

      Honestly, I've never seen something like this before. In the end, I could show the summary using a Manual Running Total.

      So, modify the @qty formula to:

      WhilePrintingRecords;

      numbervar summ;

      if isnull({ScannedDetails.qty}) and isnull({ScannedDetails.manualqty})

      then

      0

      else

      summ := summ + {ScannedDetails.qty}+{ScannedDetails.manualqty};

      Create another formula to show the final sum and place this on the Report Footer:

      WhilePrintingRecords;

      numbervar summ;

      -Abhilash