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.