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] Leading zero in report EXL2K

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Leading zero in report EXL2K
 Login/Join
 
Member
posted
Hi,
I need to display one leading zero if the number is less then 1.

I have already searched other threads in the forum and all suggest to use:
 SET CENT-ZERO=ON 
.

I have tried but with no success.

I have a field /P13.8. I would like, in the excel report, to have 0,20 and not ,20.

If I click right on the cell, the format is #,00000000.

Thanks for any help

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



FOCUS 7.2.0S1
Mainframe - MVS 390
Excel, Flat file

Italy
 
Posts: 25 | Registered: June 23, 2009Report This Post
Expert
posted Hide Post
SET CENT-ZERO=ON
TABLE FILE CAR
SUM
SALES/P20.8
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END

Seems to work for me. The generated Excel format is #0.00000000.


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
Member
posted Hide Post
Hi Francis,
I use Focus on Mainframe, this could be the point.

If I open the file on the mainframe system I see in a cell:

 
<TD X:NUM class=xl100P13_8>-0.06250000</TD>            


The class xl100P13_8 is defined as:

 
.xl100P13_8                              
        àmso-style-parent:style0;        
        color:£000000;                   
        font-size:10.0pt;                
        font-family:"ARIAL";             
        text-align:right;                
        mso-number-format:"ç£.00000000"; 
        white-space:normal;              
        mso-font-charset:0;è    


Then I transfer the file with ftp (ascii mode) on the windows system to open it with excel.
If I save the file as htm, in the cell I see:

<td class=xl31 width=71 style='width:53pt' x:num="-6.25E-2">-,06250000</td>


The class xl31 is defined as:

 
.xl31
	{mso-style-parent:style0;
	color:black;
	mso-number-format:"\#\.00000000";
	text-align:right;
	white-space:normal;}


Is there any way to get the first zero in excel?

Thanks



FOCUS 7.2.0S1
Mainframe - MVS 390
Excel, Flat file

Italy
 
Posts: 25 | Registered: June 23, 2009Report This Post
Expert
posted Hide Post
The last time I used Focus on a mainframe we didn't have EXL2K as an output format so I'm sorry I can't help you. Hopefully someone with current mainframe experience will come forward.


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
Member
posted Hide Post
Saving file as FORMAT EXCEL, SET CENT-ZERO=ON works properly but, unfortunly, I need to save it as EXL2K.

I was wondering if it is possible to use the SET STYLE section to define the numeric format but I have found nothing.

Thank



FOCUS 7.2.0S1
Mainframe - MVS 390
Excel, Flat file

Italy
 
Posts: 25 | Registered: June 23, 2009Report This Post
Expert
posted Hide Post
I'm not sure that I understand the issue completely. But if you're looking for the following RESULTS:
COUNTRY     SALESCST    SALESINT    SALESMOD    SALESVAL
ENGLAND    .24435009           0    24435009        0,24
FRANCE    6.95478276           6    95478275        6,95
ITALY     2.33567987           2    33567986        2,33
JAPAN     7.37364145           7    37364144        7,37
W GERMANY	 9.76414075           9    76414075        9,76


... then perhaps, in the mean time you can consider the following CODE which basically concatenates the INT and the MOD of your P20.8 value (thru some funky code):
TABLE FILE CAR
SUM
COMPUTE SALESCST/D20.8 = IF COUNTRY EQ 'ENGLAND' THEN .24435009 ELSE 10*RDUNIF('D6.4')  ;
COMPUTE SALESINT/I1 = INT(SALESCST);
COMPUTE SALESMOD/I8 = ((SALESCST) * 100000000) - (SALESINT * 100000000);
COMPUTE SALESVAL/A24 = EDIT(SALESINT)||','||EDIT(EDIT(SALESMOD),'99');
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END


DISCLAIMER: This is nothing great. But, if I understand the desireed result, then it'll get the job done.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Member
posted Hide Post
Thank you Doug for your replay.

I have already thought your solution to concatenate the integer and decimal part. This could solve the problem but it is quit annoying for the client to have a numeric field in a text cell.
Furthermore, I have a lot of numeric fileds in the report that could be less then 1 and I should write more code...

Has someone saved excel file as "FORMAT EXL2K" in a Mainframe environment?
Did you have the same problem that I have?

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



FOCUS 7.2.0S1
Mainframe - MVS 390
Excel, Flat file

Italy
 
Posts: 25 | Registered: June 23, 2009Report This Post
Expert
posted Hide Post
You're Welcome ac1,

I think that it's a bit annoying as well. But, like I said "It'll get the job done". I normally comment code like this as needing to be redacted and come back to it when time permits. And pass it on as a New Feature Request when possible.

Just for clarification: Your client wants something like ".24435009" to be displayed as "0,24" (zero comma two four) not "0.24"?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
Doug, the comma for decimal is Continental Decimal Notation, achieved by

SET CDN=ON

Decimal is comma, thousands separator is dot.

The European way (and Quebec).


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
Expert
posted Hide Post
Thanks Francis, for the International clarification on this. I needed some clarification on it
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Member
posted Hide Post
Hi Doug,
the client wants to see 0,24435009. The comma is the decimal notation, as Francis said.

Anyway, it is strange that with "FORMAT EXCEL" I get 0,24435009 and with "FORMAT EXL2K" I get ,24435009.



FOCUS 7.2.0S1
Mainframe - MVS 390
Excel, Flat file

Italy
 
Posts: 25 | Registered: June 23, 2009Report This Post
Expert
posted Hide Post
It is strange, and may have something to do with FOCUS for mainframe. Try opening a case with Tech Support.


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
Member
posted Hide Post
Ok Francis.
I need to register to open a case, right?
I should ask the code licence to the client?... no so easy



FOCUS 7.2.0S1
Mainframe - MVS 390
Excel, Flat file

Italy
 
Posts: 25 | Registered: June 23, 2009Report This Post
Expert
posted Hide Post
ac1, yes, you need a site code. Perhaps the client has already registered with Tech Support and they have a User ID/Password...


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
  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] Leading zero in report EXL2K

Copyright © 1996-2020 Information Builders