Oracle BI EE 10.1.3.3/2 - Dates, Dates and Dates

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.

      

About these ads