Tuesday, March 3, 2009

How to change date format mask in date dashboard prompts - drop-down list and calendar

If you have ever asked yourself how to change the format mask of date dashboard prompt that used calendar control, here is the solution of this problem.

We know that we can use drop-down list and calendar control for the date dashboard prompt, and there is a file localedefinitions.xml in D:\OracleBI\web\config for editing locale settings. Just for my test, I'm using english-base (en) part of this file.

Editing dateShortFormat entry as in picture:

will give us desired date format for drop-down list:

But if we use a calendar:

we see that dateShortFormat entry doesn't have any influence on it:

Let's change now dateSeparator and dateOrder entries in localedefiniitions.xml to see what will happen. I want to see date in my user friendly format dd.mm.yyyy, with or without leading zeros (01.01.2008 or 1.1.2008), so I changed dateOrder to dmy and dateSeparator to zero (.).

Remember, dateShortFormat is still in dd-MM-yyyy form. We will see later how it affects on calendar.

After restarting presentation service, a new format mask is applied on calendar:

Ok, so far so good.

But what if we want to apply some default date value to calendar prompt? What is the format in which default value should be?

Let's go into BI Administrator and create initialization block and repository variable for test.

Variable rv_test_date_to_char is in character format ('dd.mm.yyyy').

We set rv_test_date_to_char as default value to a calendar prompt:

After preview, we can see that the default variable rv_test_date_to_char (character) is converted to a date format before getting into calendar prompt field using dateShortFormat dd-MM-yyyy entry in localedefinitions.xml:

We don't like this, because if you choose value from a calendar, you'll see a difference between these two formats, the default one and the one from a calendar:

Solution is to synchronize all formats.

dateShortFormat -> d.M.yyyy
dateSeparator -> .
dateOrder -> dmy

Don't forget to enter date format d.M.yyyy in dateFormats entry if it doesn't exist there:

After final test our repository variable is converted from character '01.01.1999' to a date 1.1.1999, before getting into calendar prompt field:

Now, if you choose same value from a calendar, you'll get the same format as before, and that's correct:

With this solution you'll have a full control of date format when you are using calendar so u can use default repository variable that is a character and later you can parse value from a date dashboard prompt to a presentation variable and use it wherever you want to (title, filter).

With above settings in localedefinitions.xml, if we now include default variable rv_test_date_to_char in drop-down list we will get:

No conversion of character 01.01.1999 to a dateShortFormat d.M.yyyy for drop-down list prompts?

So, if we like to see the same format in drop down list with the one we have in variable (character) here is a solution:

In edit column formula write to populate character format:

case when 1=2 then cast(TIMES.TIME_ID as char) else EVALUATE('TO_CHAR(%1,%2)' as varchar(20),TIMES.TIME_ID,'dd.mm.yyyy') end

Open SQL Results and add for test purpose:

SELECT case when 1=2 then cast(TIMES.TIME_ID as char) else EVALUATE('TO_CHAR(%1,%2)' as varchar(20),TIMES.TIME_ID,'dd.mm.yyyy') end FROM "Normal model" where TIMES.CALENDAR_MONTH_DESC='1999-01' order by TIMES.TIME_ID

After a preview we see that the data in drop-down list is in character format (like variable) and order by is correct.

We should use drop-down list only with constraint option (with selecting months as parent) because it's confusing to see to many month values in list.


Kishore Guggilla said...

hi goran ocko..

Excellent blog entry made by you..
Really Awesome.. and you got excellent stuff

keep rocking.. [:-)]

Ask and Ye Shall Receive said...

Thanks. your post was really helpful as I am currently working on something similar.

Is there a way to change the month from number to character.er month 3 to 'MAR'

Goran Ocko said...

Where you wan't to change it?

Ask and Ye Shall Receive said...

In the Dashboard prompt, when we choose the date from the calendar. I am able to change the date separator, and mm/dd/yyyy to dd-mm-yyy, but unable to change 'mm' to 'mon'

Goran Ocko said...

I try to get the same. When I choose date from a calendar it would be great to see date format that is not d.m.yyyy, which is the default format for calendar dashboard prompt now. I don't know the answer to your question at this moment. I think that there is a javascript files which we need to modify if we want to se dd-mon-yyy for example (after we pick up the value from a calendar). Localdefinitions.xml has no influence here. When you'are doing some preview of calendar dashboard prompt click on view source to see what kind of code is populated and which javascripts are called and from which location. This would be a good point to start, I supose. If you have any progress let me now. I will also try to find some solution how to change the format of choosen date from a calendar as soon as possible.

Goran Ocko said...

Here is the answer of the problem how to change date format mask in javascript for calendar dashboard prompt:


meda said...

Hi Goran,

i want in yyyy-mm-dd (2009-05-01)
this shud come when i wil select date from calendar



CSSO friends said...


Thanks for the detailed explanation of this issue on your blog.
Actually ran into an issue, if you could help me out.
We initially changed the format to dd/mm/yyyy and it worked fine with the localdefinitions.xml and NQSConfig.ini files.
Now we need to change the format to DD/MON/YYYY (so a 3 char month e.g. Jan, feb, Mar..etc) and i changed both the files but doesnt seem to work. Any help would be appreciated.


hathaichanok said...

How's nice Blog

Klára said...


It's a very good explanation. How is it in the newer BI version The written setting does not influence the apperance of the choosen date from a calendar.
Any advise would be helpfull.
Thank in advance.

Goran Ocko said...

What is exactly the problem in

With calendar dashboard prompt?


And maybe this can be fixed with DATE FORMAT ISSUE ON THE DASHBOARD PROMPT patch on version:



Klára said...

unfortunately I have just realized that the customer’s support service level has expired nearly, I can not read the patch (I have forwarded to my boss).
I have read the forum, I have made all the settings. The date choosen from a calendar looks like this : 2009-09-23 00:00:00 after pushing the Go button. The riport gives the right answer, I have used presentation variable and sql filter.

Thak you for your kindly help.