There was a question for me how we can change the date format mask when we choose a value from a calendar. It always shows date in format d.m.yyyy no mather what settings we have in localedefinitions.xml files. Only date order and date separator is populated from localdefinitions.xml (example, dateOrder is dmy and dateSeparator is -). So if you have been read my previous posts How to change date format mask in date dashboard prompts - drop-down list and calendar and Date between in filter and title when using presentation variable from calendar dashboard prompt or drop-down list in OBIEE you know that we used d.m.yyyy date format in calendar for selecting value from it, for default value from a repository variable and for parsing into presentation variable.
Javascripts are used for calendar prompts. To see which scripts are used here you must open your calendar dashboard prompt and see the source that is generated with view source. This will give you the order of scripts that are executed.
What now if I want to change date format mask in calendar so that it shows me for example 1-Jan-2009 format when I take a value from it? And also I want 1-Jan-2009 format to be the first value (default, from a repository variable) when I start this prompt.
Step 1 - default repository variable from initialization block
Leave this as in previous posts, default date is in character format dd.mm.yyyy and OBIEE will do implicit conversion to a date format which we have defined in dateShortFormat in localedefinitions.xml.
Step 2 - localedefinitions.xml
Depends on our locale settings these entries need to be modified:
dateShortFormat -> d-MMM-yyyy
This format is for default values.
dateSeparator -> -
This separator we expect after picking up the value from a calendar.
dateOrder -> dmy (like in previous posts)
This date order we expect after picking up the value from a calendar
Step 3 - view prompt source
Date short format, date order and short month names:
NQCShowCalendar function (on click):
NQCShowCalendar
(
document.getElementById('saw_11_6'),
document.getElementById('saw_11_4'),
null,
null,
false,
null,
nqcalmns,
nqdfmt,
nqdsep);
Step 4 - modify calendar.js javascript file to show a month abbreviation instead of month number
We use calendar.js from location\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla, not from location \OracleBI\web\app\res\b_mozilla.
These are default values and they are populated in NQCShowCalendar function:
RgMN array is populated with nqcalmns=new Array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') which we see in view source. Nqcalmns depends on locale settings (login language, localedefinitions.xml):
We need to call rgMN array in NQCSetDate function so we modify current code with the new one (just put in comment old part of the code):
Step 5 - save, restart presentation service and test
I made a dashboard page with report in Answers and a calendar dashboard prompt:
I put here in filter 1-Jan-1999 as default value so I need to alter session to a English language because my database is in Croatian. This I'll do in Administrator:
Initial start (prompt is filled with repository variable):
NQQuery.log:
*Note that at initial dashboard start, the default 01.01.1999 is going directly into presentation variable so that's way we see it in SQL in NQQuery.log. After we pick up a new date from a calendar or refresh the same we will see new format in NQQuery.log (1-Jan-1999).
Choosing another date from a calendar:
NQQuery.log:
If you run all these statements in database:
select 1 from times where time_id='6-Jan-1999'--our example
select 1 from times where time_id='06-Jan-1999'
select 1 from times where time_id='6-JAN-1999'
select 1 from times where time_id='06-JAN-1999'
select 1 from times where time_id='6-jan-1999'
select 1 from times where time_id='06-jan-1999'
you can see that in all cases Oracle use TIMES_PK index, so implicit conversion char to date is present according to NLS settings in session/database.
if someone knows easier way to change month number to a month abbrevation for a calendar date prompt like I described in this post or to any other format please let me know.
You can change date order and separator from a localedefinitions.xml but it's not what we want to (for example 1.1.1999, 1/1/1999, 1999/1/1, 1999-1-1, etc).
Javascripts are used for calendar prompts. To see which scripts are used here you must open your calendar dashboard prompt and see the source that is generated with view source. This will give you the order of scripts that are executed.
What now if I want to change date format mask in calendar so that it shows me for example 1-Jan-2009 format when I take a value from it? And also I want 1-Jan-2009 format to be the first value (default, from a repository variable) when I start this prompt.
Step 1 - default repository variable from initialization block
Leave this as in previous posts, default date is in character format dd.mm.yyyy and OBIEE will do implicit conversion to a date format which we have defined in dateShortFormat in localedefinitions.xml.
Step 2 - localedefinitions.xml
Depends on our locale settings these entries need to be modified:
dateShortFormat -> d-MMM-yyyy
This format is for default values.
dateSeparator -> -
This separator we expect after picking up the value from a calendar.
dateOrder -> dmy (like in previous posts)
This date order we expect after picking up the value from a calendar
Step 3 - view prompt source
Date short format, date order and short month names:
NQCShowCalendar function (on click):
NQCShowCalendar
(
document.getElementById('saw_11_6'),
document.getElementById('saw_11_4'),
null,
null,
false,
null,
nqcalmns,
nqdfmt,
nqdsep);
Step 4 - modify calendar.js javascript file to show a month abbreviation instead of month number
We use calendar.js from location\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla, not from location \OracleBI\web\app\res\b_mozilla.
These are default values and they are populated in NQCShowCalendar function:
RgMN array is populated with nqcalmns=new Array('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') which we see in view source. Nqcalmns depends on locale settings (login language, localedefinitions.xml):
We need to call rgMN array in NQCSetDate function so we modify current code with the new one (just put in comment old part of the code):
Step 5 - save, restart presentation service and test
I made a dashboard page with report in Answers and a calendar dashboard prompt:
I put here in filter 1-Jan-1999 as default value so I need to alter session to a English language because my database is in Croatian. This I'll do in Administrator:
Initial start (prompt is filled with repository variable):
NQQuery.log:
*Note that at initial dashboard start, the default 01.01.1999 is going directly into presentation variable so that's way we see it in SQL in NQQuery.log. After we pick up a new date from a calendar or refresh the same we will see new format in NQQuery.log (1-Jan-1999).
Choosing another date from a calendar:
NQQuery.log:
If you run all these statements in database:
select 1 from times where time_id='6-Jan-1999'--our example
select 1 from times where time_id='06-Jan-1999'
select 1 from times where time_id='6-JAN-1999'
select 1 from times where time_id='06-JAN-1999'
select 1 from times where time_id='6-jan-1999'
select 1 from times where time_id='06-jan-1999'
you can see that in all cases Oracle use TIMES_PK index, so implicit conversion char to date is present according to NLS settings in session/database.
if someone knows easier way to change month number to a month abbrevation for a calendar date prompt like I described in this post or to any other format please let me know.
You can change date order and separator from a localedefinitions.xml but it's not what we want to (for example 1.1.1999, 1/1/1999, 1999/1/1, 1999-1-1, etc).
Posts
