Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2006Report 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: 2127 | Location: Customer Support | Registered: April 12, 2005Report 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, 2006Report 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, 2006Report 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: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report 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: 5694 | Location: United Kingdom | Registered: April 08, 2004Report 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, 2006Report 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.


WebFOCUS 8.2.06
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report 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, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders