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

Post date: 22-Mar-2010 08:06:12

f 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.

With calendar dashboard prompt?

http://forums.oracle.com/forums/thread.jspa?threadID=906066&start=0&tstart=0

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

http://obiee101.blogspot.com/2009/09/obiee-patches-10341.html

complied from http://108obiee.blogspot.com/2009/03/how-to-change-date-format-mask-in-date.html

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

http://108obiee.blogspot.com/2009/03/how-to-change-date-format-mask-in-date.html

Changing date format mask in javascript for calendar dashboard prompt 

http://108obiee.blogspot.com/2009/04/changing-date-format-mask-in-javascript.html

Use to_char in the init block for default value, it's much easier to propagate it later, rather then using DATE or TIMESTAMP.

Regards

Goran

http://108obiee.blogspot.com

I had same problem on version 10.1.3.4.1, version before was without any problems.

I had prompt with DATE column and Calendar for date selection. When I pressed Go button next prompt, the date value (for example 2/5/2009 was changed to 2/5/2009 12:00:00 AM)

Maybe this is the solution, It works for me:

1) first problem was on physical layer CALENDAR.DATE colum had DATETIME format (I think that import sets this format to DATE columns as default), so I changed DATETIME column format to DATE.

2) I have still the same problem with CALENDAR.DATE column in prompt

3) If I add same DATE column to presenatation layer with different name (for example DATE_1) I can use this column in prompt and time is not added after pressing Go button and everything works fine, but this is not solution. Problematic column is referenced in many reports.

4) If I delete problematic DATE column from presentation layer and business layer and add new one with same name I have again problem with added time to my date column in prompt

5) And now the final solution: 

I created prompt with problematic DATE column, pressed Edit Formula button next the column. In the editor window was reference to source of column (CALENDAR.DATE) and I changed it to "CALENDAR"."DATE" surounded with commas

Now the problem disappeared and I can use DATE column with calendar selection without any problem.

I hope this helps to someone

I was working with a customer today who had brought in a new Oracle source into their OBIEE physical layer. The source had several tables with date columns that used the DATE datatype, and these tables were then used to create a simple dimensional model, like the one below.

Everything worked well, until they created a request with a filter on the date column. Remember that the column in Oracle is a DATE datatype, yet the filter added by Answers below adds a TIMESTAMP to the predicate.

Now the reason that this is an issue, is if you’ve got an index defined on the date column. If you let Answers generate the query as it is, you end up with SQL looking like this:

-------------------- Logical Request (before navigation):  RqList  distinct     Items.Order ID as c1 GB,     Items.Order Date as c2 GB DetailFilter: Items.Order Date = TIMESTAMP '2007-04-12 00:00:00.000' OrderBy: c1 asc, c2 asc  +++Administrator:480000:48000b:----2009/03/06 21:27:26  -------------------- Sending query to database named orcl2 (id: <<309826>>):  select distinct T6622.ORDID as c1,      T6631.ORDERDATE as c2 from      ITEMS T6622,      ORDERS T6631 where  ( T6622.ORDID = T6631.ORDID and T6631.ORDERDATE = TIMESTAMP '2007-04-12 00:00:00' ) order by c1, c2 

which will mean that your index won’t get used, unless you create a corresponding function-based index on the date column. It also screws up partition elimination as well, which can seriously impact the response time of your query.

The answer to this problem is to go back to the physical model in Oracle BI Administrator, and change the DATETIME datatype that OBIEE assigns to Oracle DATE datatypes by default, to an OBIEE DATE datatype, like this:

This altered datatype then propagates through to the business model and mapping layer, and then the presentation layer of the model. After restarting the Oracle BI Server just to be on the safe side, reloading the server metadata in Oracle BI Answers, I go to create the same report. To my surprise though, the filter prompt still adds a timestamp to my choice of date.

Cue several hours of fiddling with column formulas in Answers, wrapping the filter value in a CAST function and so on, all of which only partially solved the problem and usually ended up with either the predicate or the table column being wrapped in a function call, which again cancelled out the index. In the end, we just tried dropping the column from the business model and mapping layer, and then adding it back in again from the physical layer.

Now, after adding the new column to the presentation layer, saving the repository and then reloading the server metadata in Answers, when I create a new request in Answers the filter prompt just uses the date on its own.

Then, if I take a look at the SQL in the query log, we can see that the predicate is just a date. Checking the execution plan afterwards shows that the index is indeed being used.

-------------------- Logical Request (before navigation):  RqList  distinct     Items.Order ID as c1 GB,     Items.ORDERDATE as c2 GB DetailFilter: Items.ORDERDATE = DATE '2007-04-12' OrderBy: c1 asc, c2 asc  +++Administrator:300000:300005:----2009/03/06 21:46:19  -------------------- Sending query to database named orcl2 (id: <<12826>>):  select distinct T6622.ORDID as c1,      T6631.ORDERDATE as c2 from      ITEMS T6622,      ORDERS T6631 where  ( T6622.ORDID = T6631.ORDID and T6631.ORDERDATE = TO_DATE('2007-04-12' , 'YYYY-MM-DD') ) order by c1, c2 

So the moral of the story there is, if you’re bringing Oracle data in and it’s got DATE datatypes, make sure you correct the DATETIME datatypes that the import process assigns to them in the physical model, otherwise you’ll hit this same problem around superfluous TIMESTAMPs. If you’ve already built your logical model before you find this issue, make sure you delete and the re-add the date columns after you correct the physical model datatype, otherwise Answers will still go on using TIMESTAMPs even if you change the repository model and even refresh the Answers metadata. Simple once you know how, but it took us a good few hours to sort out.

The story didn’t end there though. The customer also set up a session variable that held, for each user, yesterday’s date. They populated this within an initialization block that selected against the SYSDATE pseudo-column, truncating it and taking 1 off to return yesterday’s date.

As you can see, again by default OBIEE assigns it a DATETIME datatype, which will cause the same problem as before if we try and use it in a filter. No amount of TO_DATE or CAST as DATE around the SQL would turn it into a plain old DATE datatype, so in the end we wrapped a TO_CHAR around it and converted it into the correct CHAR format for our database’s NLS_DATE settings, like this:

Now, if we include the session variable in a filter, like this:

The resulting SQL generated by the BI Server includes this CHAR version of the date directly in the query, which Oracle then implicitly converts to a date datatype because we’re using the correct date format for the database’s NLS_DATE setting.

-------------------- Logical Request (before navigation):  RqList  distinct     Items.Order ID as c1 GB,     Items.ORDERDATE as c2 GB DetailFilter: Items.ORDERDATE = '06-MAR-2009' OrderBy: c1 asc, c2 asc  +++Administrator:300000:300006:----2009/03/06 21:58:47  -------------------- Sending query to database named orcl2 (id: <<12934>>):  select distinct T6622.ORDID as c1,      T6631.ORDERDATE as c2 from      ITEMS T6622,      ORDERS T6631 where  ( T6622.ORDID = T6631.ORDID and T6631.ORDERDATE = '06-MAR-2009' ) order by c1, c2 

Checking the execution plan for this new query, it is indeed using the index we created. Now obviously, this is a “dangerous thing” as we’re making the assumption that our database, or more correctly database client software, will always keep using this same NLS_LANG setting, but given that I can’t see any other way of getting the BI Server to store the date session variable as a date rather than date and time, it’s a working solution that solved the immediate problem.

So, a day of fiddling around with dates, and thanks to John Minkjan, Venkat and Christian Berg whochipped in with some advice. If anyone else faces the same issue, hopefully this posting will be of use.

Click on the Advanced button of your filter, and explicitly CAST the Session Variable to a date

ie. Time.”Forecast Month End Date” = CAST (VALUEOF(”DW_PREVIOUS_FORECAST_MONTH”) AS DATE )

This will cause the following SQL using TRUNC which allows the DATE index to be used.

AND t497896.fscl_mnth_end_dt =

TRUNC (TIMESTAMP ‘2010-01-31 00:00:00′)

If you had checked my previous blog entry here, you would have noticed that i had briefly touched upon the date formats that you can use to get the dates to display properly in various desired formats. Today we shall look at 3 different Date related questions.

1.   How to customize Date formats that are getting displayed in Dashboard Prompts?

2.   How to generate a new date by adding years, months etc to an existing date?

3.   How to subtract 2 different dates to produce the difference in number of years, months etc?

Lets start with the first one. It is a pretty straight forward question. All that we want to do is to change the format of Dates that are displayed in the Dashboard Prompts. For example, lets start with creating a simple Dashboard Prompt on a Date Column.

As you see above, by default BI EE has taken the TIMESTAMP format of mm/dd/yyyy hh:mm:ss. But what we would like to do is to format this date field to a format say dd-mom-yyyy. Lets go to Answers and create a simple report. Now include the same date field that we used in the dashboard prompt.

Now go to the column properties and change the date format to dd-MMM-yyyy (it is case sensitive).

Now if you see below you find a Save button. Click on system wide default for this column. So what we are doing is we are basically saving this format across all the reports that use this column.

Once this is done (you dont have to save the report), go back to the dashboard. You would find that the display format of the Dashboard Prompt would have changed.

Lets go to the 2nd question. In order to address this we shall be using a function called as TimeStampAdd. Even though this is addressed clearly in the docs, i thought i shall blog about this since this can serve as some sort of example reference for these functions. Before going further lets understand some of the following constants that we shall be using in this.

   SQL_TSI_SECOND

   SQL_TSI_MINUTE

   SQL_TSI_HOUR

   SQL_TSI_DAY

   SQL_TSI_WEEK

   SQL_TSI_MONTH

   SQL_TSI_QUARTER

   SQL_TSI_YEAR

As the names indicate the above are constants that make TimeStampAdd understand that we are basically adding second or minute or hour etc to the supplied date field. Below is the actual syntax of the TimeStampAdd function.

TimestampAdd (interval, integer-expression, timestamp-expression)

   Where interval can take any of the above mentioned constants,

               integer-expression is the value that you want to add to the date

               timestamp-expression is the date field that you are adding the integer-expression to

Now lets try adding 5 months to the same date field as mentioned above. So, if we have 10-Jan-2005, by adding 5 months we must get 10-Jun-2005. The formula that we should use is TimeStampAdd(SQL_TSI_MONTH,5,Times.Calendar_month_end_date).

You can also use these functions against some arbitrary date values ( the date values have to be supplied in ‘yyyy-mm-dd’ format). For example, TimeStampAdd(SQL_TSI_MONTH,5,DATE ‘2000-10-10′).

Coming to the 3rd question above, the function that we have to use for this is TimeStampDiff. This function also uses the above mentioned constants. The syntax for this function is

TimestampDiff (interval, timestamp-expression1, timestamp-expression2)

   Where interval can take any of the above mentioned constants,

               timestamp-expression is the date field that you are subtracting

               timestamp-expression is the date field that you are subtracting from

For example, TimeStampDiff(SQL_TSI_DAY,Times.Calendar_month_start_date, Times.Calendar_month_end_date) will give the difference in days of the start and end dates.