I am trying to get an empty tab to show when no data exists for the value in my BY statement. I tried the EMPTYREPORT = ANSI and it did not work. Any ideas?

This message has been edited. Last edited by: FP Mod Chuck,

Tracie

Try EMPTYREPORT = ON

This did not work either.

My BY and where statement:
BY BDCA_CODE
WHERE BDCA_CODE IN (&BDCA1, &BDCA2, &BDCA3);

I am doing an excl report that shows each BDCA by tab by using SET COMPOUND BYTOC in my footer.

So in the instance where no data exists for any of the 3 parameters (BDCAs) above, I would like a tab to still be created saying no records found along with the other 2 tabs with the data if found.

Currently it creates the tabs for the data found.

SET EMPTYREPORT = ON works for the entire missing report, not for part of it.

Here is an option where programming is necessary, but may be flexible and may need some customization to answer your needs

-SET &CNTRY1 = 'ENGLAND';
-SET &CNTRY2 = 'JAPAN';
-SET &CNTRY3 = 'CHINA';
-SET &NBPARM = 3;

TABLE FILE CAR
SUM RETAIL_COST/D8 AS 'RETAIL_COST'
BY TOTAL COMPUTE FND     /A1  = 'Y';
BY COUNTRY/A10 AS 'COUNTRY'
WHERE COUNTRY IN (&CNTRY1.QUOTEDSTRING, &CNTRY2.QUOTEDSTRING, &CNTRY3.QUOTEDSTRING);
ON TABLE HOLD AS EXTDATA
END
-RUN

TABLE FILE EXTDATA
BY COUNTRY
ON TABLE HOLD AS EXTCNTRY
END
-RUN

-SET &NBCNTRY = &LINES;
-SET &HASDFLT = 'N';

-IF &NBCNTRY EQ &NBPARM THEN GOTO NODEFAULT;

-SET &J = 0;
-REPEAT FNDCNTRY FOR &I FROM 1 TO &NBCNTRY
-DEFAULTH &CNTRY      = ''
-DEFAULTH &FND        = ''
-DEFAULTH &FND&I.EVAL = ''
-READFILE EXTCNTRY
-SET &FND&I.EVAL = TRIM_(BOTH, ' ', &COUNTRY);
-TYPE -->&FND&I.EVAL<--
-FNDCNTRY

-REPEAT CNTRYFND FOR &X FROM 1 TO &NBPARM
-SET &CNTRYFND = 'N';
-REPEAT CHECKFND FOR &Y FROM 1 TO &NBCNTRY
-SET &CNTRYFND = IF &CNTRY&X.EVAL EQ &FND&Y.EVAL THEN 'Y' ELSE &CNTRYFND;
-CHECKFND

-IF &CNTRYFND EQ 'Y' THEN GOTO CNTRYFND;
-SET &J = &J + 1;
-SET &MISCNTRY = &CNTRY&X.EVAL;
TABLE FILE CAR
SUM COMPUTE RETAIL_COST /D8 = 0;
BY COUNTRY NOPRINT
BY TOTAL COMPUTE FND     /A1  = 'N';
BY TOTAL COMPUTE COUNTRY /A10 = '&MISCNTRY.EVAL';
WHERE READLIMIT EQ 1;
ON TABLE HOLD AS EMPTYDATA&J.EVAL
END
-RUN
-CNTRYFND

TABLE FILE EMPTYDATA1
SUM RETAIL_COST
BY FND
BY COUNTRY
ON TABLE HOLD AS EMPTYDATA
-REPEAT MRGFILE FOR &K FROM 2 TO &J
MORE
FILE EMPTYDATA&K.EVAL
-MRGFILE
END
-RUN
-SET &HASDFLT = 'Y';
-NODEFAULT

TABLE FILE EXTDATA
SUM RETAIL_COST
BY FND
BY COUNTRY
ON TABLE HOLD AS RPTDATA
-IF &HASDFLT EQ 'N' THEN GOTO NOEMPTY;
MORE
FILE EMPTYDATA
-NOEMPTY
END
-RUN

DEFINE FILE RPTDATA
RETCST /A20V = IF FND EQ 'N' THEN 'No Data Available' ELSE FPRINT(RETAIL_COST, 'D8', 'A20');
END
TABLE FILE RPTDATA
SUM RETCST AS 'Retail Cost'
BY COUNTRY AS 'Country'
ON TABLE PCHOLD AS 'MyReport' FORMAT XLSX
ON TABLE SET COMPOUND BYTOC
ON TABLE SET STYLE *
TYPE=DATA,
  COLUMN=RETCST,
  JUSTIFY=RIGHT,
$
ENDSTYLE
END
-RUN

quote:

WHERE BDCA_CODE IN (&BDCA1, &BDCA2, &BDCA3);

Is this field character or numeric? If character then bound the variables in quotes -

WHERE BDCA_CODE IN (&BDCA1.QUOTEDSTRING, &BDCA2.QUOTEDSTRING, &BDCA3.QUOTEDSTRING);

T

Thanks Martin! Yes Tony, it has quotes around it.

You could do something like this:

-SET &Region1 = 'East';
-SET &Region2 = 'Midwest';
-SET &Region3 = 'West';

 SET ASNAMES=ON, HOLDMISS=ON

 TABLE FILE GGSALES
 SUM UNITS
     DOLLARS
  BY REGION AS 'REGION'
     ROWS '&Region1' OVER '&Region2' OVER '&Region3'
  BY ST
  BY CITY
  ON TABLE HOLD AS REPDATA
 END

 TABLE FILE REPDATA
 PRINT UNITS
       DOLLARS
    BY REGION
    BY ST
    BY CITY
 ON REGION SUBHEAD
 "No data Available for Region <REGION"
 WHEN UNITS IS MISSING AND DOLLARS IS MISSING
 ON TABLE SET COMPOUND 'BYTOC 1'
 ON TABLE PCHOLD FORMAT XLSX
 END

It appears to be a bug that on the no data tab 'East' it incorrectly fills in a state and city.

I ended up doing a left outer join on the validation table and the table with the data. The left outer brought back the value with no data, and it printed a tab with just the headers! So I guess this will work for now. Thanks everyone!