Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Empty Tab - No Data
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Empty Tab - No Data
 Login/Join
 
Platinum Member
posted
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,


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Tracie

Try EMPTYREPORT = ON


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2070 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
This did not work either.


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2394 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5692 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thanks Martin! Yes Tony, it has quotes around it.


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
 
Posts: 189 | Registered: October 19, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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!


tbj
Prod WF 8.1.05,Test WF 8.1.05, WINDOWS 7 Platform, Oracle 12
Excel, PDF, Alpha
 
Posts: 132 | Location: Chapel Hill, NC | Registered: October 24, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Empty Tab - No Data

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.