Does anyone know how to remove/not show totals that equal zero. I have tried using the where total tab (amount field NE to zero) but that didn't work. Any ideas?

Here is a sample of my data:

Name Balance
Jane Doe 500
Bill Smith 0.0-don't want this to show
Kate Jones -100

This message has been edited. Last edited by: Kerry,

Hi Joanna,

I'm not sure if you are doing row total or column total/subtotal.

For subtotal, you can do a WHEN on it.
eg.
TABLE FILE EMPDATA
SUM
SALARY
BY DIV

ON DIV SUBTOTAL AS '*TOTAL'
-* This is the WHEN statement
WHEN SALARY GE 350000;
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL' SALARY
ON TABLE PCHOLD FORMAT HTML
END

Best Regards,

Jimmy Pang

Hello Joanna,

Based on your example I don't see why the WHERE TOTAL construct wouldn't work in our case.

Here's an example of Total Sales by Country using the CAR table:

  
TABLE FILE CAR
SUM SALES
BY COUNTRY
HEADING
"Total Sales by Country"
END

Notice that FRANCE had a total sales of 0. If you wanted that entry to be removed from the report, then WHERE TOTAL would be the way to go:

  
TABLE FILE CAR
SUM  SALES
BY COUNTRY
WHERE TOTAL SALES NE 0
HEADING
"Total Sales by Country (excluding 0 sales)"
END

Is that what you need?

Now it might be possible that what you are seeing in your report as 0.00 is in reality the formatted version of a different value (such as 0.00001) which would imply a data precision issue. It would help if you give us some more details about your masterfile structure and in particular the data type and length of the AMOUNT field along with a code snippet that illustrates the logic being used to exclude aggregated values of 0.

To illustrate my point, please take a look at the code below in which I'm making up a very small COMMISSION on sales and a new computed field TOT_COMMISSION is created to capture the aggregated commission by country. Notice that even though TOT_COMMISSION is displayed as 0.00 in reality it represents a different internal value and that's why WHERE TOTAL cannot filter it out.

DEFINE FILE CAR
COMMISSION/D20.15 WITH SALES = SALES * 0.000000001;
END
TABLE FILE CAR
SUM
        SALES
        COMMISSION
COMPUTE TOT_COMM/D20.2 = COMMISSION;
BY COUNTRY
WHERE TOTAL TOT_COMM NE 0
HEADING
"Total Sales by Country"
END

If that is your case you might need to HOLD your request using an appropriate formatted value (such as D20.2 for example) and then running the report out of the HOLD file.

Hope that helps,

Regards,
- Neftali.

This message has been edited. Last edited by: njsden,

quote:

(such as D20.2 for example)

The Idea is correct but the format wrong.

Dn.m format does not hold an absolute value.

You must convert to either integer or packed format.
Or use a GT 0 AND LT 0.0049999 type selection