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 -100This message has been edited. Last edited by: Kerry,
Joanna
WebFOCUS 7.6, Windows All output
January 06, 2010, 09:50 AM
fatboyjim
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
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,