Business Intelligence – Oracle

Oracle BI EE – Understanding Dates in Answers

Posted by Venkatakrishnan J on October 1, 2007

One of the typical queries that we generally get in the forums is how do we control the date format in BI Answers. Also, the general question is in what format do we ask users to enter dates into a dashboard prompt so that the filters are applied properly. If you are used to Oracle Date formats of DD-MM-YYYY or DD-MON-YYYY etc, just be aware that BI Answers treats the date formats a little bit differently. Lets try and understand this using a sample report. Lets create a sample report from the SH schema (assuming you have the Business Model and Presentation layer setup properly). This report would basically give us the amount sold in all the years.

Lets look at the report output.

As you see above, the date field by default has changed the format to include the time part of the date. Let us look at this in much more detail. Edit the column properties of the date column and go to Data Format tab.

As you see above, by default BI Answers has chosen the date format as FMT:timeStampShort. Now click on the date format and change it to custom. We will now try to understand the formats that we generally use. For example, we might want the format to be of the form mm/dd/yyyy. In order to do this we would have to enter the format as FMT:dateShort. This would display the date in the format that we desire.

The above picture shows the date fields with the new date format. There are lots of date formats that one can have. All these are documented in page 75 of the User Guide here.

Remember, if you have any date prompts, the format that you would have to enter should have to match the format that you have set in the data format tab of column properties in the report.

3 Responses to “Oracle BI EE – Understanding Dates in Answers”

  1. […] by Venkatakrishnan J on December 19, 2007 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 […]

  2. Jacob said

    Hi Venkatakrishnan ,
    could you please write how can I change the date format in the Title view of the Answers(Started Time field)

  3. Dimitris said

    Hi Venkatakrishnan ,
    If we put in our dashboard prompt a date promt and pass it to our report using presentation vasiarvle(always using calendar),how can we “take it” in order to influence a measure column of our fact table using the function filter by??
    tnks in advanve

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: