nls_date_language, english monthname

building semantic layer
1

Hi,
we are working with BOXIR3.1, with German BO Preference Properties (Product locale , prefered viewing locale)

My problem ist the Monthname, we want to get a monthname in english and not in german.

The Universe Object Monthname is of Type date/time and has an Object format property “Mmm”.
So I get the german Montnames, how can I cange these Objects into an english Monthname ?

To change the BO Preferences is no option, because we have other Fomating in germany which should be hold (like 00.000,00 for a formatmask for numberfields and not 00,000.00)
To change the Object type, i.e. String, make a case structure and give static month text is no option, because these the Object has not the correct sort order.

I tried to give the connectinit a execute immediate ‘alter session set nls_date_language=AMERICAN’; but without any effect in BO.
( when I try this in an sql session, it works fine)

Has anybody a solution how I can achieve an english Monthname with german BO Preferences ?

Many thanks in advance
Peter

hape :de: (BOB member since 2009-11-09)

2

This can be achieved easily if you have a calendar_dim table. You just need to have an English month column. Or create a database function to decode German months to English months. Or, we can use the least efficient way of coding this within a universe object.

Uvee :uk: (BOB member since 2005-10-26)

3

Thanks for the reply, but I don’t think to solve my Problem with your proposals

Column: would be a Char and has not the date sort behavior.
DB Function; see above.

The efficient way as universe Object, HOW ?

My way:
Universe Object i.e.
to_date(‘01’||DWH.DIM_MONAT.MONAT||‘1999’,‘DDMMYYYY’,
‘NLS_date_LANGUAGE = American’)
and Object Format ‘Mmm’.

The result is a german Mai and not May.

I assume that BO has a language layer where all Formating will be done. So that the Universe settings (see above) have no effect.

Regards,
Peter

hape :de: (BOB member since 2009-11-09)

4

I didn’t mean using formatting to change locale. You need to create an ‘Englishmth’ object with a decode statement like:

decode( DWH.YOURDATABSEMONTH,'Januar','January',
'Februar','February',
...
...
'Dezember','December','Not valid')

This ‘Englishmth’ has to be used in place of your ususal month using the @select(Englishmth).

What I also said was that this is not efficient because this has to be created in each universe.

Come to think of it, this written as a database function might be the best solution.

Uvee :uk: (BOB member since 2005-10-26)

5

Bi Uvee,
as understand your decode approach, I’ll get an Universe Object of CHar-type .

This Object can’t be sorted like a date Object but as a Char !
This is not what I want.
I’m not aware how to get a date column which shows a text and is sorted as a date Object in BO.

I’m using the Universe Object format option, so the Object is shown as a text but sortted as a date.

The Problem seems to be that The Unv.Object should be a date but with American Formating.

Thanks for your effort,
Peter

hape :de: (BOB member since 2009-11-09)

6

Maybe have a look and see whether Translation Manager can help?

Mak 1 :uk: (BOB member since 2005-01-06)