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] MISSING ON not working with Across Reports

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] MISSING ON not working with Across Reports
 Login/Join
 
Gold member
posted
I am trying to put zero where the values are missing in my Across Report and getting blank space(.) in the report output.SET NODATA can not be used because of Empty values are needed for two are more columns in my report.Is there any idea to put zero instead of blank space?

Example:
SET NODATA=' '
TABLE FILE CAR
SUM
COMPUTE SALES/I11M MISSING ON=IF SALES IS MISSING THEN 0 ELSE SALES;
ACROSS COUNTRY
BY CAR
END

in the above example the sales column should have zero for missing data and others need blank space for Missing data.

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


Webfocus AppStudio 8201
Windows
 
Posts: 53 | Registered: May 09, 2016Report This Post
Platinum Member
posted Hide Post
If you want 0 where data is missing then use
SET NODATA='0'
You have set it to blank hence you get a blank when data is missing.
 
Posts: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report This Post
Member
posted Hide Post
I think OP is asking only the Sales column MISSING values needs to be '0' and other columns MISSING values should be blank. The SET NODATA='0' would take care of the other columns, I am also interested to see on how to set NODATA for a specific column differently.


WF 8201M/Windows2012
 
Posts: 1 | Registered: May 24, 2017Report This Post
Virtuoso
posted Hide Post
In the below sample I have assigned 'X' as the default missing displayed data but I have set a different value for missing seats.

Retail_MIS exist only to illustrate a missing value

SET NODATA='X'
DEFINE FILE CAR
RETAIL_MIS/D7 MISSING ON=IF RETAIL_COST LE 10000 THEN MISSING ELSE RETAIL_COST; 
SEATS_MIS /I3 MISSING ON=IF SEATS LE 2 THEN MISSING ELSE SEATS; 
SEATS_NEW /I3 MISSING ON=IF SEATS_MIS IS MISSING THEN 123 ELSE SEATS_MIS;
END
TABLE FILE CAR
SUM RETAIL_COST
    RETAIL_MIS
    SEATS
    SEATS_MIS
    SEATS_NEW
ACROSS COUNTRY
BY CAR
WHERE COUNTRY EQ 'ENGLAND';
END

If you look at the Triumph row you will see that its RETAIL_MIS is displayed with the default missing value and the SEATS_NEW with the overwrite assigned one (where SEATS_MIS as the default one).
So as final report, you should only print RETAIL_MIS and SEATS_NEW. The other are included to show the original value only BEFORE manipulation.


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
Platinum Member
posted Hide Post
Try this code, it will help you to get desired output,

 SET NODATA=''
DEFINE FILE CAR
SALES/D7 MISSING ON=SALES;
COUNTRY/A10 =STRIP(10, COUNTRY, ' ', 'A10');
END
TABLE FILE CAR
SUM RETAIL_COST
   SALES
ACROSS COUNTRY
BY CAR
ON TABLE SET  ASNAME ON 
ON TABLE HOLD AS XYZ FORMAT ALPHA
END



DEFINE FILE XYZ
Rec_Type/A55 = 'ENGLAND';
D_ACROSS_SORT/I2 = 1;
END
-RUN

TABLE FILE XYZ
PRINT  RETENGLAND 
 SALENGLAND 
 CAR
 Rec_Type
 D_ACROSS_SORT
ON TABLE HOLD AS EN
END



DEFINE FILE XYZ
Rec_Type/A55 = 'FRANCE';
D_ACROSS_SORT/I2 = 2;
END
-RUN

TABLE FILE XYZ
PRINT   RETFRANCE 
 SALFRANCE
 CAR
 Rec_Type
 D_ACROSS_SORT
ON TABLE HOLD AS FR
END


DEFINE FILE XYZ
Rec_Type/A55 = 'ITALY';
D_ACROSS_SORT/I2 = 3;
END
-RUN

TABLE FILE XYZ
PRINT   RETITALY 
 SALITALY 
 CAR
 Rec_Type
 D_ACROSS_SORT
ON TABLE HOLD AS IT
END


TABLE FILE EN
SUM
     EN.EN.RETENGLAND/I11C AS 'Retail Cost'
     EN.EN.SALENGLAND/I11C AS 'Sales'
BY  EN.EN.CAR AS 'Car'
ACROSS D_ACROSS_SORT NOPRINT  AS ''
ACROSS Rec_Type AS 'Country'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,
	 ENDSTYLE
MORE
FILE FR
MORE
FILE IT
END 


WF Production :- WF:8.0.0.4, 8.1.05 App-studio/Developer Studio(8.1.x) ,
8.2.0.1M , 8.2.0.2 (App-Studio8.2.x),
InfoAssist/+, InfoDiscovery
Output format:-AHTML, PDF, Excel, HTML
Platform:-Windows 7, 8,10
 
Posts: 186 | Location: Infobuild India | Registered: August 28, 2015Report This Post
Gold member
posted Hide Post
Thanks for your reply Martin.It's working fine with less or greater than of sales condtions.But if i go with the following conditions it repeat only x for all columns

SET NODATA='X'
DEFINE FILE CAR
SALES_MIS /I3 MISSING ON=IF SALES EQ MISSING THEN 0 ELSE SALES;
SALES_NEW /I3 MISSING ON=IF SALES_MIS EQ MISSING THEN 0 ELSE SALES_MIS;
END
TABLE FILE CAR
SUM
SALES -* This column should provide blank space for missing values
SALES_NEW -* This column should provide zero for missing values
ACROSS COUNTRY
BY CAR
END


Webfocus AppStudio 8201
Windows
 
Posts: 53 | Registered: May 09, 2016Report This Post
Gold member
posted Hide Post
I got a final solution by simplifying the code of Chaudhary.Thanks Chaudhary.

SET NODATA=''
DEFINE FILE CAR
SALES/D7 MISSING ON=SALES;
END

TABLE FILE CAR
SUM
RETAIL_COST -* Zero for Missing Data
SALES -* Blank space for Missing Data
BY CAR
ACROSS COUNTRY
ON TABLE HOLD AS XYZ FORMAT ALPHA
END

TABLE FILE XYZ
PRINT
*
END


Webfocus AppStudio 8201
Windows
 
Posts: 53 | Registered: May 09, 2016Report This Post
Platinum Member
posted Hide Post
quote:
I got a final solution by simplifying the code of Chaudhary.Thanks Chaudhary.

quote:
SET NODATA=''
DEFINE FILE CAR
SALES/D7 MISSING ON=SALES;
END

TABLE FILE CAR
SUM
RETAIL_COST -* Zero for Missing Data
SALES -* Blank space for Missing Data
BY CAR
ACROSS COUNTRY
ON TABLE HOLD AS XYZ FORMAT ALPHA
END

TABLE FILE XYZ
PRINT
*
END


Durai, By using this you will not be getting the Across column(country) header .


WF Production :- WF:8.0.0.4, 8.1.05 App-studio/Developer Studio(8.1.x) ,
8.2.0.1M , 8.2.0.2 (App-Studio8.2.x),
InfoAssist/+, InfoDiscovery
Output format:-AHTML, PDF, Excel, HTML
Platform:-Windows 7, 8,10
 
Posts: 186 | Location: Infobuild India | Registered: August 28, 2015Report 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] MISSING ON not working with Across Reports

Copyright © 1996-2020 Information Builders