Focal Point
Decimal Value Does Not Display Correct When Regional Settings Equals Germany

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7921098272

February 22, 2008, 12:42 AM
BHS6868
Decimal Value Does Not Display Correct When Regional Settings Equals Germany
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

February 22, 2008, 04:04 AM
nubi
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
February 22, 2008, 04:08 AM
BHS6868
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

February 22, 2008, 04:29 AM
nubi
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
February 22, 2008, 04:33 AM
nubi
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
February 22, 2008, 04:49 AM
GamP
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
February 22, 2008, 08:49 AM
PBrightwell
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
February 22, 2008, 09:34 AM
Baillecl
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
February 25, 2008, 01:52 PM
FrankDutch
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

February 25, 2008, 01:58 PM
RSquared
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
February 25, 2008, 09:00 PM
BHS6868
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

February 26, 2008, 02:28 AM
Tony A
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 
February 26, 2008, 07:11 AM
FrankDutch
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

February 26, 2008, 08:13 AM
Tony A
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 
February 26, 2008, 09:39 AM
RSquared
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
February 26, 2008, 10:10 AM
Tony A
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
February 26, 2008, 03:06 PM
RSquared
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