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     Retaining Leading zeros in excel format

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Retaining Leading zeros in excel format
 Login/Join
 
<Jagan>
posted
Hi,

I am not able to display leading zeros in my report heading. Here I am giving some sample code, can anybody look into this and let me know the solution?

EMPNO and EMPNAME are the fields I am displaying in report heading.


EMPNO is alphanumeric field and defined as EMPNO/A3 in MASTER FILE DESCRIPTION.


TABLE FILE EMPFILE
HEADING
"TEST REPORT "
"Employee Number : <1 EMPNO <10 EMPNAME"

PRINT SALARY AS '' IN 1
DEPTNO AS '' IN 20
BY EMPNO NOPRINT
ON TABLE PCHOLD FORMAT EXL97
END

When I run the above code , EMPNO and NAME are displaying in seperate cells in my report heaind second line. But the LEADING ZEROS IN EMPNO field is suppressing.

If my EMPNO is 012, it is displaying as 12.



WHEN I USE FORMAT EXL2K, ZEROS ARE DISPLAYING.
IS THERE ANY SOLUTION FOR EXL97?
 
Report This Post
susannah
Expert
posted Hide Post
Jagan, excel is doing that do you, not focus.
it takes its best guess at a format.
The only way to trick it is to create a field that is alpha, maybe _012 or .012 or try '012



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
GinnyJakes
Expert
posted Hide Post
TABLE FILE EMPDATA
HEADING
"TEST REPORT "
"Employee Number : <1 <PIN <10 <LASTNAME"

PRINT SALARY AS '' IN 1 
DEPT AS '' IN 20
BY PIN NOPRINT
ON TABLE PCHOLD FORMAT EXL97
END


This works for me.

If you are going to continue to post, please update your profile signature with your product suite, releases, and platforms so that we can better help you.

Also, please use examples on IBI-supplied demo files which can be found in directories like ibisamp, ibinccen, or ibidemo. You can almost always find one that is suitable for your needs.

Also 2, the code you posted won't display the contents of field names in the heading, just text. You forgot your spot markers.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
<Jagan>
posted
Hi Susannah,

Thanks a lot your help, I have added one space to the field and displayed it.
It is working fine now.

I have another field,I am displaying it in my report body,which is defined as mentioned below in my master file description.

FIELD=FEACI_T005I_AM_BENEFIT_NET,
ALIAS=E276, USAGE=P7.2, ACTUAL=P3

If the value is 170.00, it is displaying as 170 in excel 97.

Is it possible to convert the above field into alpha and add one space to the right side of that field? so that i can display as 170.00.

or any other way?

Thanks a lot in advance...
 
Report This Post
GinnyJakes
Expert
posted Hide Post
Look up the FTOA subroutine.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
<Jagan>
posted
Hi Ginny,

I am using the below code but the output alpha field is displaying as stars (*********).

Is there any problem with syntax or we can not convert packed deciaml fields?

TABLE FILE FEARESPH01
PRINT FEACI_T005I_AM_BENEFIT_NET AND COMPUTE
BENEFIT_NET/A9 = FTOA(FEACI_T005I_AM_BENEFIT_NET, '(P7.2)', BENEFIT_NET);
BY FEACI_T001I_CO_AGENCY_FK NOPRINT
END

**************OUTPUT REPORT RESULTS-----

PAGE 1

FEACI_T005I_AM_BENEFIT_NET BENEFIT_NET
174.00 *******
277.00 *******
227.00 *******
277.00 *******
249.00 *******
227.00 *******
 
Report This Post
<Jagan>
posted
Hi Ginny,

I have used PTOA instead of FTOA and working fine for me.
 
Report This Post
GinnyJakes
Expert
posted Hide Post
For future reference when using FTOA, the output alpha field always needs to be quite a bit longer than what you would expect from the numeric format. That is why you got the stars. The output field needs room for the decimal point, the sign, and a couple of others for good measure.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
susannah
Expert
posted Hide Post
jagan, its ok to change a numeric code to alpha for output in excel,
but if you start messing with measures, just because you want to fake-format the values,
your users will not be able to operate on those alpha fields. The fields will look like numbers but they arent...your users can't add them, can't use them in formulae...nada
that's a sure way to get a slap-up-side-the-head from your user base.
imho



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Doug
Expert
posted Hide Post
Yes Jagan, as Susannah said: You can't do the math on these converted values. However, math can be performed on these values after a "convert to number" on each cell (or range). That's normally too much work for the users.
If they are going to go through that much work to change the alpha to numeric, why not just leave the leading zeros off in the first place and let them reformat those fields to display leading zeros in Excel.




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 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     Retaining Leading zeros in excel format

Copyright © 1996-2020 Information Builders