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     Field format in EXL2K

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Field format in EXL2K
 Login/Join
 
arsalan
Silver Member
posted
There's a link on my WebFocus html page that creates an excel sheet (EXL2K). In one of the fields in excel, the total is displayed at the bottom by using: ON TABLE COLUMN-TOTAL AS 'TOTAL' UNITS
If I manually select all the values in that column and add them up it is slightly different than what is calculated by WF.

This field is declared as D12.2 in master file but all the values are displayed as integer in excel.
I believe this is because of the internal behavior of WF for EXL2K that rounds up to the nearest integer value and then dispaly it in excel.

Is there a way to retain their formatting i.e. to dispaly values in decimal in excel?

I tried converting D12.2 to D12 and both the totals came out to be the same but I want it the other way round i.e. in decimal.

Thanks.
 
Posts: 34 | Registered: June 19, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
A Dnn.n field should display as nn.n in HTM, PDF, EXL2K, it won't be transformed to an integer unless the program does that.

TABLE FILE CAR
SUM WHEELBASE
BY COUNTRY
ON TABLE SUBTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END


or
TABLE FILE CAR
SUM WHEELBASE
BY COUNTRY
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT EXL2K
END

WHEELBASE is defined as D6.1 and displays as such in Excel.


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
arsalan
Silver Member
posted Hide Post
Francis,

Let me correct my previous statement, actually the field is declared as D12 not D12.2
I'm not sure that it contains decimal values in datasource or not.
In the file I could not see any code that transforms the field.

But I get two different values of sums in excel, manually and by WF.

Any suggestions would help.
Thanks.
 
Posts: 34 | Registered: June 19, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
This sounds completely different than your original question.

It is quite possible that the data has decimal values, because, even though D12 does not have any decimal places showing, they are used for the internal calculations.

Hopefully this example illustrates this for you. P does not behave the same way as D.

DEFINE FILE CAR
M1A/D12.2 = 10.45;
M2A/D12.2 = 9.25;
M1B/D12 = M1A;
M2B/D12 = M2A;
M1C/P12 = M1A;
M2C/P12 = M2A;

M3A/D12.2 = M1A + M2A;
M3B/D12 = M1B + M2B;
M3C/P12 = M1C + M2C;
END
TABLE FILE CAR
SUM
M1A M2A M3A
M1B M2B M3B
M1C M2C M3C
BY COUNTRY
-*ON TABLE PCHOLD FORMAT EXL2K
END


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
arsalan
Silver Member
posted Hide Post
Thanks Francis,

That example did make many things clear to me.
Also, I found out, that field was created from two Dnn.n fields and rounding to the nearest integer value, that's why the two sums were different. If I print it as D12.2 field the sums come out to be same.
 
Posts: 34 | Registered: June 19, 2007Report This Post
Waz
Expert
posted Hide Post
Have you tried 
 ON TABLE PCHOLD FORMAT EXL2K FORMULA
as this will let Excel to the totalling


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 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     Field format in EXL2K

Copyright © 1996-2020 Information Builders