Business Intelligence – Oracle

Archive for October 1st, 2007

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.

Posted in All Posts, OBI EE Plus | 3 Comments »