Displaying date formats for two different countries

Mark.Thompson

Consider if you will a date column, displayed three times on an Analysis.

Column #1 should show the date values as 2001/01/31, 2001/02/28, 2001/03/31, and so forth.  Easy.

Column #2 should show the date values in the format MMM English.  That's easy too.  English is my default locale.

Column #3 should show the date values in the format MMM of the date in Italian.

Without using a case statement, but sticking strictly to data formatting rules (perhaps with a custom format on the data format tab), is there a way to accomplish this:

2001/01/31     Jan     Gen

2001/02/28     Feb     Feb

2001/03/31     Mar     Mar

2001/04/30     Apr     Apr

2001/05/31     May     Mag

2001/06/30     Jun     Gui

2001/07/31     Jul     Lug

2001/08/31     Aug     Ago

2001/09/30     Sep     Set

2001/10/31     Oct     Ott

2001/11/30     Nov     Nov

2001/12/31     Dec     Dic

Basically the question is: Is it possible to format a date column to display month names and day names for a specified locale, while other date columns are displayed "normally", based on the default system locale?

Joel

Hi Mark T

If your data source is Oracle, you could use the EVALUATE function and use something similar to this to achieve your requirment.

Mark.Thompson

Thank you, Joel.  Interesting option.  I like it.  Unfortunately, it doesn't meet the criteria.  I need the field to be a date field that is DISPLAYED in Italian.  Using EVALUATE would change it to a character string field. 

Why do I care?  Because I am attempting to use range-based fragmentation, and still allow my users to filter on the Month column.  The Month column has to be a date, not a character, so that I can use it as a range condition in the fragmentation specification.  For some users, they want to select the English version of the column.  Others will select the Italian version of the column.  But both columns would refer back to the same logical date column.  So whether English or Italian, it's actually still a date under the covers, and could therefore be used for range-based fragmentation.

That's why I need the answer to be completely contained within the allowable parameters of date column formatting. 

Joel

In that case, @Mark T., you could possibly achieve this by enclosing the logic above in a TO_DATETIME function.

Mark.Thompson

Thanks, Joel.  I had thought about that, and that would work perfectly IF there was an option in TO_DATETIME or TO_DATE that would allow me to control the locale of the formatted string.  For the column in my local language (English), formatting the date of 7/15/2012 to MMM returns 'Jul'.  For the Italian version of that column, I want MMM to return 'Lug'.   It seems that what I want is to be able to explicitly pick an NLS_DATE_FORMAT for a given column.  Unfortunately, the documentation for TO_DATE states: "The default date format is determined implicitly by the NLS_TERRITORY initialization parameter or can be set explicitly by the NLS_DATE_FORMAT parameter."  Since both of those parameters are set at the system level, it seems that I would always get English, or always get Italian, unless there is some hidden parameter to TO_DATE that would allow me to specify a different NLS_TERRITORY or NLS_DATE_FORMAT for just that specific column.

I like the way you think.  And thank you again for the ideas.  Sorry to keep bashing them.

Thomas Dodds

You a lookup ...that accepts a 'key' for the month, a value for the language you want (optional) and returns the description in the language of your choice. 

Here's a nice write up on it - http://gerardnico.com/wiki/dat/obiee/lookup_table

Referenced is the Oracle Doc on multilingual:  http://docs.oracle.com/cd/E14571_01/bi.1111/e10541/deploylocal.htm#BIESG1760

Mark.Thompson

Thomas, I think this looks promising.  This is a completely different idea than what I thought I needed.  But it may indeed serve the purpose for which I need it, which is really all that matters to me anyway.  I will test it at the first available opportunity, probably the first part of next week.  Thanks!!

Thomas Dodds

Mark,

This is how Oracle implements in BI Apps for multi-lingual ... common key, language flag, descriptions in native 'tongue'.  Retrieves even the English via the lookup!   So the star model looks like it should, but the translation tables are not joined to their corresponding dimension, they are leveraged as look-ups in the BMM layer.   If I recall correctly, the DENSE/SPARSE will affect the join criteria that get's built for the SQL going to the database (DENSE = inner; SPARSE = left outer).  <- I may have amnesia on that point, but off the top of my head it 'sounds' correct! 

Mark.Thompson

One thing I'll be curious about when I test this: The "driving" column is a DATE, and the "displayed as" column will be CHAR.  Because I'm trying to do a range-based LTS fragmentation, I will need to use the DATE column in the fragmentation rule.  It will be interesting to see if the whole thing works - if the correct fragment is used in the SQL - when an Italian user selects Lug-2009 and an English user selects Jul-2009.  If it does - if OBIEE is just showing us the string Lug-2009 but OBIEE is really thinking the date 31-Jul-2009 - then this will be ultra-cool. 

There will be three questions to answer: 

(1) In the BMM layer, will the Month column show a datatype of DATE, or a datatype of CHAR?  I anticipate it will show DATE, which is what I want. 

(2) If the datatype of the Month column is indeed DATE, can a DATE field be used as the "driving" column in a lookup</code> table?</p><p>(3) Does the return from a <code>LOOKUP function have to return the same datatype as the "driving" column?  For example, if a DATE field can be used as the "driving" column, can the "displayed as" column be CHAR?  I'm not so sure about this one. There may be some technical reason why they would have to be the same - DATE-to-DATE, CHAR-to-CHAR.

We shall see...

Thomas Dodds

You are better served by having a true common key for the date independent of language to drive the lookup ... (YYYYMMDD) 20162019 would be a natural choice.

Then you would have 2 logical columns, one that does a lookup for the 'EN' date description and one that looks up the italian description -- OR you could leverage the users preferred currency (session variable) to show the data (one single column) based on their choice.

Thomas Dodds

here's the section on multilingual data ... http://docs.oracle.com/cd/E23943_01/bi.1111/e10541/deploylocal.htm#BIESG1757