Hi,

I have a dilemma. I have a report that outputs to Excel. I want it so that when there are no lines in the report (&LINES EQ 0), Excel will not fire, instead, a No Records message is displayed on the html page.

Unfortunately, I have a WHERE TOTAL on the report. For some insane reason, if the report is empty because it doesn't meet WHERE TOTAL critera, the Report Headers are still displayed (similar to having EMPTYREPORT=ON) and the Excel fires. How do I fix this?

Sample Code (output to HTML for simplicity):

TABLE FILE CAR
SUM BODYTYPE
COMPUTE TEST/A4 = IF LST.RETAIL_COST GT 1000 THEN 'RETL' ELSE IF LST.DEALER_COST GT 1000 THEN 'DEAL';
BY COUNTRY
BY CAR
WHERE TOTAL TEST EQ 'RETL';
WHERE COUNTRY EQ 'NONE';
ON TABLE SUBHEAD
"Just Testing "
" "
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
	 JUSTIFY=CENTER,
	 BACKCOLOR='NAVY BLUE',
	 COLOR='WHITE',
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
ENDSTYLE
END
-RUN
-IF &FOCERRNUM NE 0 GOTO DBERR;
-IF &LINES EQ 0 GOTO NORECORDS;
-GOTO ENDFEX
-NORECORDS
-TYPE NO RECORDS
-GOTO ENDFEX
-DBERR
-TYPE CRITICAL ERROR
-ENDFEX

The example above produces the result I want - where the NO RECORDS message is displayed instead of the report headers.

If you change the WHERE CLAUSES to:

WHERE TOTAL TEST EQ 'NONE';
WHERE COUNTRY EQ 'ENGLAND';

Then I get the Report Headers even on &LINES EQ 0.

Methinks it's a webfocus bug... but that doesn't solve my dilemma...

Thanks!

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

I would do this with the help of a hold file. That will work as desired. The code I used:

TABLE FILE CAR
SUM BODYTYPE
COMPUTE TEST/A4 = IF LST.RETAIL_COST GT 1000 THEN 'RETL' ELSE IF LST.DEALER_COST GT 1000 THEN 'DEAL';
BY COUNTRY
BY CAR

WHERE TOTAL TEST EQ 'NONE';
WHERE COUNTRY EQ 'ENGLAND';

-*WHERE TOTAL TEST EQ 'RETL';
-*WHERE COUNTRY EQ 'NONE';
ON TABLE HOLD
END

-IF &FOCERRNUM NE 0 GOTO DBERR;
-IF &LINES EQ 0 GOTO NORECORDS;

TABLE FILE HOLD
SUM BODYTYPE
    TEST
BY COUNTRY
BY CAR

ON TABLE SUBHEAD
"Just Testing "
" "
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
$
TYPE=TITLE,
     STYLE=BOLD,
	 JUSTIFY=CENTER,
	 BACKCOLOR='NAVY BLUE',
	 COLOR='WHITE',
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
ENDSTYLE
END
-RUN
-IF &FOCERRNUM NE 0 GOTO DBERR;
-IF &LINES EQ 0 GOTO NORECORDS;
-GOTO ENDFEX
-NORECORDS
-TYPE NO RECORDS
-GOTO ENDFEX
-DBERR
-TYPE CRITICAL ERROR
-ENDFEX

Hope this helps ...

GamP is correct The only way that you will get what you want is to use an intermediate hold file.

It is a documented feature that using IF TOTAL or WHERE TOTAL will produce an empty report
when SET EMPTYREPORT=OFF is used.

The reason is that

quote:

the test for report lines for EMPTYREPORT is applied before the TOTAL condition is applied

Okay, Thank you very much guys!

I went ahead and did the hold file like y'all suggested.