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     Decimal Value Does Not Display Correct When Regional Settings Equals Germany

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Decimal Value Does Not Display Correct When Regional Settings Equals Germany
 Login/Join
 
Member
posted
Dear Experts,

Tried to search the forums but could not locate a thread that addresses this issue.

Basically I have a table that exports the data into an excel spreadsheet. The user is located in Germany therefore the computer regional settings is set to Germany.

For some reason the decimal column D20.2 shows as month

Eg.

% Total
------
11.11

5.56

And in the Excel document it shows as

% Total

Nov 11

Mai 56

Example of code:


TABLE FILE CAR
PRINT COUNTRY
COMPUTE TEST/D10.2 = 5.56;
COMPUTE TEST2/D10.2 = 11.11;
ON TABLE HOLD AS EXCELREPORT FORMAT HTML
END
SET HTMLFORMTYPE=XLS
-HTMLFORM EXCELREPORT
-EXIT

to generate the excel as i required the gridline in the excel report.


I know it has to do with the regional settings but don't have a clue on how to solve it. Any help will be greatly appreciated.

Regards.

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


WebFOCUS 767 Server 64bit On Windows 2003 Server

WebFOCUS 767 Server for Developer Studio On windows XP

 
Posts: 18 | Registered: November 29, 2006Report This Post
Guru
posted Hide Post
i think its an excel issue- from my memory cells can often default to date format when copying and pasting data in there and think it may be defaulting to a date format behind the scenes- try opening up the sheet and reformatting the cells to a number; if that solves it you will need to either raise a case with IB or ask someone whose good at macros and stuff to see if they can help you with a work around- but im pretty sure its excel


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Report This Post
Member
posted Hide Post
Hi Nubi,

You are right. When I right click on the cell, it shows as CUSTOM MMM JJ . Wonder if there is a way to manually format that paticular column to be a general column.

Thanks anyways.


WebFOCUS 767 Server 64bit On Windows 2003 Server

WebFOCUS 767 Server for Developer Studio On windows XP

 
Posts: 18 | Registered: November 29, 2006Report This Post
Guru
posted Hide Post
Manually yeah- i'd just right-click and change the format to a Number with 2 Dec places; but automatically? i think you will need an excel macro...

can anyone else help?


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Report This Post
Guru
posted Hide Post
put this into google:


CUSTOM MM JJ

or go here:

jira

this forum which has a post which mentions Regional Settings and this excel problem- dunno if it will help though.


Developer Studio 7.64
Win XP
Output: mostly HTML, also Excel and PDF

"Never attribute to malice that which can be adequately explained by stupidity." - Heinlein's Razor
 
Posts: 285 | Location: UK | Registered: October 26, 2007Report This Post
Virtuoso
posted Hide Post
Would SET CDN=ON help in any way?
In my excel it then shows as 11,11 which will never be misunderstood for a date.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Master
posted Hide Post
If you hold the report FORMAT EXL2K does it do the same thing? What about if you change the compute to P10.2?


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Platinum Member
posted Hide Post
Hi Focus Guys,
These seem to be American Fellows on the Line.
ON TABLE SET CDN ON should be the solution.
We, Europeans, are fond of Commas instead of dots.
In the old days Focus was conscious of that and had the variable CDN to comply our old habits
Cordialement and Focusly
PS : glad that a platinum member put CDN on the line (Wish I could turn into a lead member)


Focus Mainframe 7.6.11
Dev Studio 7.6.11 and !!!
PC Focus, Focus for OS/2, FFW Six, MSO
 
Posts: 134 | Registered: November 06, 2007Report This Post
Virtuoso
posted Hide Post
BHS

converting your dates back to numbers in excel will not give you the wanted result.
Your format is MM YY but the internal number will be something like 30217.
Try converting it to a flat number and you will see what I mean.
The comma suggestion (CDN) might do the tric, but that also may cause a problem if your excel basic format is NOT setup with a decimal comma.

I would try the EXL2K format and see if you can get the grid in an other way.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Guru
posted Hide Post
Just another point, in Europe dates are shown as mm.dd.yyyy and not mm/dd/yyyy, therefor when Excel sees the'.' it thinks that it is looking at a date.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Member
posted Hide Post
Dear All,

The SET CDN = ON method worked well with the customer in Europe. Unfortunately the report is also currently being used by customers in the US. So had to scratch that idea off.

When I use export the excel document using EXL2K, the value is displayed correctly, however there were issues with the grid lines not being able to displayed. Search through the forums and looks like there is no solution to this unless you use a template or something like that.

So the final solution was to change the value to display 3 (D10.3) decimal values like 5.556. That seem to solve the issue plus the customer was ok with it.

Once again thanks to all that responded.

Regards.


WebFOCUS 767 Server 64bit On Windows 2003 Server

WebFOCUS 767 Server for Developer Studio On windows XP

 
Posts: 18 | Registered: November 29, 2006Report This Post
Expert
posted Hide Post
quote:
Just another point, in Europe dates are shown as mm.dd.yyyy and not mm/dd/yyyy, therefor when Excel sees the'.' it thinks that it is looking at a date.
Rsquared,

This is not true for all of Europe! In the UK, dates are generally dd/mm/yyyy or yyyy/mm/dd.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Virtuoso
posted Hide Post
So finally you admit that the UK belangs to Europe.....
You see so many time "the UK and Europe..." Big Grin




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Expert
posted Hide Post
quote:
So finally you admit that the UK belangs to Europe.....
Never said that Frank Wink Perhaps Europe belongs to the UK ... although France and Germany are trying very hard to have overall control Frowner

Favorite European joke?

French representative asking at European summit "Why do we always have to speak English?"

British representative "Because we fixed it so that you don't have to speak German!"

T

This message has been edited. Last edited by: Tony A,



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Guru
posted Hide Post
Well Tony,

I guess you Brits always haveto make things a little more difficult, but then as a Canuck I am used to the British way of doing thinhs, except ofr driving on the wrong side of the road. Be that as it may, then the problem with the date/amount would not be a problem with UK only Frnace/Germany etc.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Expert
posted Hide Post
quote:
driving on the wrong side of the road
So that's where I've been going wrong Wink

Being a "Canuck" have you the snow fall that I hear is pretty bad this year?

T
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Guru
posted Hide Post
Nah,

I live in the States now. I couldn't take the snow and cold. and the socialized medecine etc.

BTW Je suis un Quebecois.


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     Decimal Value Does Not Display Correct When Regional Settings Equals Germany

Copyright © 1996-2020 Information Builders