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     [CLOSED] Report fails if empty row returned

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Report fails if empty row returned
 Login/Join
 
Platinum Member
posted
My report is returning hours worked for the week for employees. The hours all have different pay codes; REG, PER, SCK, etc.

Each paycode has its own row in the report.

If one of the rows has no data, like no one has any SCK (Sick time) for the week, the entire report fails:

"No HTML Output!"

0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0.


This error is a true statement as there is 0 records in the table (it is actually a view), for sick time since no one used any.

How do I make the report skip this row if it is empty and continue with the rest of the report?

Also, since my report is built in a grid, the entire report is in the SUDHEAD.

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


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Expert
posted Hide Post
You may want to look into "EMPTYREPORT".

SET EMPTYREPORT = ON


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Francis Mariani:
You may want to look into "EMPTYREPORT".

SET EMPTYREPORT = ON


I saw this on age 24-23 of the big book, but it says:

"This applies to tabular reports, not to free-form reports".

I am building a free-form report that exists entirely in the SUBHEAD. Can I use EMPTYREPORT in my case?


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Guru
posted Hide Post
Greg,

Can you show the code, please.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by RSquared:
Greg,

Can you show the code, please.


First there is a header row that contains the days of the week:

ON PS_WHATEVER SUBHEAD

"SUN<+0>MON<+0>Tue<+0>"


Then the report has 9 lines for the 9 different paycodes:

"<PS_LN1_PAYCODE <PS_LN1_SUN <PS_LN1_MON <PS_LN1_TUE" 
This is Regular Time

"<PS_LN2_PAYCODE <PS_LN2_SUN <PS_LN2_MON <PS_LN2_TUE "
This is Paid OT

"<PS_LN3_PAYCODE <PS_LN3_SUN <PS_LN3_MON <PS_LN3_TUE "
This is Personal Time

The report runs with line 2 commented out. But with line 2 in, and since no one has any Paid OT, the report returns 0 results.

When I set EMPTYREPORT to ON, I just get the header of the report in the browser, with no data at all.

If the row (or the view that creates the row) has no data, I just want to skip it, and have line 3 move up a slot in the report.

Here is a scan of the manual version I am trying to mimic:

http://www.occompt.com/ttr.jpg


Greg


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Expert
posted Hide Post
COMPUTE TOT_RG/P13.2 = PS_LN1_SUN+PS_LN1_MON+PS_LN1_TUE+PS_LN1_WED+PS_LN1_THU+PS_LN1_FRI+PS_LN1_SAT; NOPRINT
COMPUTE TOT_OT/P13.2 = PS_LN2_SUN+PS_LN2_MON+PS_LN2_TUE+PS_LN2_WED+PS_LN2_THU+PS_LN2_FRI+PS_LN2_SAT; NOPRINT
ETC...

or

You "may" have to additionally set a FLAG:
COMPUTE OT_FLAG/A1 = IF TOT_OT NE 0 THEN 'Y' ELSE 'N'; NOPRINT

  
ON PS_WHATEVER SUBHEAD

"SUN<+0>MON<+0>Tue<+0>"


Then the report has 9 lines for the 9 different paycodes:

"<PS_LN1_PAYCODE <PS_LN1_SUN <PS_LN1_MON <PS_LN1_TUE" 

WHEN TOTAL TOT_RG NE 0;
This is Regular Time


"<PS_LN2_PAYCODE <PS_LN2_SUN <PS_LN2_MON <PS_LN2_TUE "

WHEN TOTAL TOT_OT NE 0;

or

WHEN OT_FLAG EQ 'Y';
This is Paid OT 


"<PS_LN3_PAYCODE <PS_LN3_SUN <PS_LN3_MON <PS_LN3_TUE "
This is Personal Time


You get the idea...

Hope this helps..

Tom

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
After I could not get the computes to work I inquired with my DBA as to why. He said the values (hours) I see in the view, say 7.6 are no longer decimal values. He is converting them in the view to varchars.

He said I just need to check the first field "LN2.PAYCODE" for a value (POT, paid overtime). If there is no vaule (NULL), THEN NOPRINT.

This line:

IF LN2.PAYCODE EQ 'NULL' NOPRINT

gives me a parsing error.


prod: WF 7.7.03 platform IIS on Windows 2007, databases: Oracle, , MSSQL

 
Posts: 133 | Location: Orlando, FL | Registered: August 04, 2005Report This Post
Expert
posted Hide Post
Yep, I don't think anyone puts a NOPRINT on data tests...Well, I thought not.

Hold the data, excluding blank or MISSING PAYCODE, then, report from the HOLD file...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Guru
posted Hide Post
Greg,

This isa bit convoluted for a labeling report but it could be modified and used for your problem
/*
LINE2/A255=IF NameAndTitle GT '' THEN NameAndTitle_UPPER ELSE
IF REPRESENTING GT '' THEN REPRESENTING_UPPER ELSE
IF COMPANYNAME GT '' THEN COMPANYNAME_UPPER ELSE
IF TRADINGASNAME GT '' THEN TRADINGASNAME_UPPER;
LINE2IND/A1=IF NameAndTitle GT '' THEN 'N' ELSE
IF REPRESENTING GT '' THEN 'R' ELSE
IF COMPANYNAME GT '' THEN 'C' ELSE
IF TRADINGASNAME GT '' THEN 'T';
LINE3/A255=IF (REPRESENTING GT '' AND LINE2IND NE 'R') THEN REPRESENTING_UPPER ELSE
IF (COMPANYNAME GT '' AND LINE2IND NE 'C') THEN COMPANYNAME_UPPER ELSE
IF (TRADINGASNAME GT '' AND LINE2IND NE 'T')THEN TRADINGASNAME_UPPER ELSE
IF ADDRESS1_UPPER GT '' THEN ADDRESS1_UPPER;
LINE3IND/A1=IF (REPRESENTING GT '' AND LINE2IND NE 'R') THEN 'R' ELSE
IF (COMPANYNAME GT '' AND LINE2IND NE 'C') THEN 'C' ELSE
IF (TRADINGASNAME GT '' AND LINE2IND NE 'T')THEN 'T' ELSE
IF ADDRESS1_UPPER GT '' THEN 'A';
LINE4/A255=IF (COMPANYNAME GT '' AND LINE3IND NE 'C') THEN COMPANYNAME_UPPER ELSE
IF (TRADINGASNAME GT '' AND LINE3IND NE 'T')THEN TRADINGASNAME_UPPER ELSE
IF (ADDRESS1_UPPER GT '' AND LINE3IND NE 'A') THEN ADDRESS1_UPPER ELSE
IF ADDRESS2_UPPER GT '' THEN ADDRESS2_UPPER ELSE
CITY_UPPER ||''|STATE1 |' '|ZIPCODE1;
LINE4IND/A1=IF (COMPANYNAME GT '' AND LINE3IND NE 'C') THEN 'C' ELSE
IF (TRADINGASNAME GT '' AND LINE3IND NE 'T')THEN 'T' ELSE
IF (ADDRESS1_UPPER GT '' AND LINE3IND NE 'A') THEN 'A' ELSE
IF ADDRESS2_UPPER GT '' THEN '2' ELSE 'Z';
LINE5/A255=IF LINE4IND EQ 'Z' THEN '' ELSE
IF (TRADINGASNAME GT '' AND LINE4IND NE 'T')THEN TRADINGASNAME_UPPER ELSE
IF (ADDRESS1 GT '' AND LINE4IND NE 'A' AND LINE4IND NE '2' AND LINE4IND NE 'Z') THEN ADDRESS1_UPPER ELSE
IF (ADDRESS2 GT '' AND LINE4IND NE '2' AND LINE4IND NE 'Z') THEN ADDRESS2_UPPER ELSE
IF LINE4IND NE 'Z' THEN CITY_UPPER ||''|STATE1 |' '|ZIPCODE1 ELSE '';
LINE5IND/A1=IF LINE4IND EQ 'Z' THEN 'Z' ELSE
IF (TRADINGASNAME GT '' AND LINE4IND NE 'T')THEN 'T' ELSE
IF (ADDRESS1 GT '' AND LINE4IND NE 'A' AND LINE4IND NE '2' AND LINE4IND NE 'Z') THEN 'A' ELSE
IF (ADDRESS2 GT '' AND LINE4IND NE '2' AND LINE4IND NE 'Z') THEN '2' ELSE
IF (CITY GT '' AND LINE4IND NE 'Z') THEN 'Z' ELSE 'X';
LINE6/A255=IF LINE5IND EQ 'Z' THEN ' ' ELSE
IF (ADDRESS1 GT '' AND LINE5IND NE 'A' AND LINE5IND NE '2' AND LINE5IND NE 'Z') THEN ADDRESS1_UPPER ELSE
IF (ADDRESS2 GT '' AND LINE5IND NE '2' AND LINE5IND NE 'Z') THEN ADDRESS2_UPPER ELSE
IF LINE4IND NE 'Z' THEN CITY_UPPER ||''|| STATE1 |' '|ZIPCODE1 ELSE '';
LINE6IND/A1=IF LINE5IND EQ 'Z' THEN 'Z' ELSE
IF (ADDRESS1 GT '' AND LINE5IND NE 'A' AND LINE5IND NE '2' AND LINE5IND NE 'Z') THEN 'A' ELSE
IF (ADDRESS2 GT '' AND LINE5IND NE '2' AND LINE5IND NE 'Z') THEN '2' ELSE
IF (CITY GT '' AND LINE5IND NE 'Z') THEN 'Z' ELSE 'X';
LINE7/A255=IF LINE6IND EQ 'Z' THEN '' ELSE
IF (ADDRESS2 GT '' AND LINE6IND NE '2' AND LINE6IND NE 'Z') THEN ADDRESS2_UPPER ELSE
IF LINE6IND NE 'Z' THEN CITY_UPPER ||''|| STATE1 |' '|ZIPCODE1 ELSE '';
LINE7IND/A1=IF LINE6IND EQ 'Z' THEN 'Z' ELSE
IF (ADDRESS2 GT '' AND LINE5IND NE '2' AND LINE5IND NE 'Z') THEN '2' ELSE
IF (CITY GT '' AND LINE5IND NE 'Z') THEN 'Z' ELSE 'X';
LINE8/A255=IF LINE7IND EQ 'Z' THEN '' ELSE
IF LINE7IND NE 'Z' THEN CITY_UPPER || ' '|STATE1 |' '|ZIPCODE1 ELSE '';
LINE8IND/A1=IF LINE7IND EQ 'Z' THEN 'Z' ELSE
IF (CITY GT '' AND LINE5IND NE 'Z') THEN 'Z' ELSE 'X';
/*

Bob


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report 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     [CLOSED] Report fails if empty row returned

Copyright © 1996-2020 Information Builders