Hi all. I have a cross tab report showing sales per account for the past 6 months. The report works ok, but I want to filter the data to only show rows where one or months have sales = 0, but where all 6 months are not = 0. Can anyone point this newbie in the right direction?

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

Try WHERE TOTAL. Its usage is described in the documentation. That will allow you to eliminate whole sorted sections when the total amount is not 0. If you have both positive and negative numbers that may add up to zero, you would have to either use absolute values (in a nonprinted column) or create a binary flag indicating whether or not a field on each record is truly zero and then WHERE TOTAL on that flag.

Thanks for the pointer. I had included my sales as SUM BLPHIST.BLPHIST.HIKWH AS 'Usage'for the 6 month period. When I use WHERE TOTAL BLPHIST.BLPHIST.HIKWH EQ 0; I only pick up rows where ALL sales are either null or 0. What I really want is the rows where there may be one or two 0 values. I want to exclude both the 'all zero' and the 'no zero' rows. We are trying to screen for exceptions.

Create an extra field that says

ZEROS/I6=IF VALUE EQ 0 THEN 1 ELSE 0;

put this field in your report with a noprint.

Then say
WHERE TOTAL ZEROS NE 0

This is what Darin says

Thanks. I included the code as indicated, but still get the same result. The only rows included are those where all months have sales = 0. But, is there a way to show rows where any single month has sales = 0 while other months have sales > 0?

I tried setting another compute field for NOT_ZEROS and fitering on ZEROS > 0 AND NOT_ZEROS > 0, but that returned no records. It appears that the compute field is filtering on the entire 6-month period, rather than individual months, which makes sense using WHERE TOTAL, but I need to report on situations like this:

JAN FEB MAR APR MAY JUN
SALES 789 1485 0 889 589 798

What am I missing? I also tried using define fields and filtering on WHERE instead of WHERE TOTAL, but that did not work. Here is the code in case it helps...
-* HTML Tool
-* FF Line do not change this line! Field Name
-* FF Line do not change this line! Alias
-* FF Line do not change this line! Format
-* FF Line do not change this line! Segment
-* FF Line do not change this line! displayTree=0
-* J001 J001
-* J002 J002
JOIN
INNER
BLPHIST.BLPHIST.HIACCT AND BLPHIST.BLPHIST.HISUB AND BLPHIST.BLPHIST.HISERV AND BLPHIST.BLPHIST.HIRSP
IN BLPHIST
TO MULTIPLE BLPMSTR.BLPMSTR.MSACCT AND BLPMSTR.BLPMSTR.MSSUB AND BLPMSTR.BLPMSTR.MSSERV AND BLPMSTR.BLPMSTR.MSRSP
IN BLPMSTR TAG J001
AS J001
END
JOIN
INNER
J001.BLPMSTR.MSBOOK
IN BLPHIST
TO UNIQUE BLPBK.BLPBK.BKBOOK
IN BLPBK TAG J002
AS J002
END
TABLE FILE BLPHIST
SUM BLPHIST.BLPHIST.HIKWH AS 'Usage'
COMPUTE
-* COMPUTE BEGIN ZEROS
ZEROS/I6 = IF BLPHIST.BLPHIST.HIKWH EQ 0 THEN 1 ELSE 0;
-* COMPUTE END ZEROS
COMPUTE
-* COMPUTE BEGIN NOT_ZEROS
NOT_ZEROS/I6 = IF BLPHIST.BLPHIST.HIKWH NE 0 THEN 1 ELSE 0;
-* COMPUTE END NOT_ZEROS
BY J001.BLPMSTR.MSMLOC AS 'Location'
ACROSS BLPHIST.BLPHIST.HIMMYY AS 'Load Month'
WHERE (J002.BLPBK.BKCYCL EQ &BKCYCL.(FIND BLPBK.BLPBK.BKCYCL IN BLPBK ).Cycle:.)
AND (BLPHIST.BLPHIST.HIMMYY GE &HIMMYY.Load Month (Like 200901).)
AND (J001.BLPMSTR.MSSTAT EQ 2)
AND (J001.BLPMSTR.MSRATE NE '177'
OR '199'
OR '499')
AND (BLPHIST.BLPHIST.HISERV EQ &HISERV.Service 1 or 3:.)
AND (BLPHIST.BLPHIST.HIADJ EQ 0);
WHERE TOTAL (ZEROS NE 0);
HEADING
"Zero Usage Report"
FOOTING
"&DATE"
ON TABLE SET STYLE *
-INCLUDE IBFS:/CFG/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENDefaultTableStyle.sty
TYPE=REPORT,
GRID=OFF,
TITLETEXT='Zero Usage Report',
$
TYPE= HEADING,$
TYPE= FOOTING,$
ENDSTYLE
ON TABLE SET HTMLCSS ON
ON TABLE NOTOTAL
END

maybe first put the data in an hold file and use the multiverb methode.