Business Intelligence – Oracle

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

Posted 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 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

10 Responses to “Oracle BI EE 10.1.3.3/2 – Dates, Dates and Dates”

  1. Sriram said

    In 10.1.3.2 version the date customising is not working for dashboard prompt. It is working only in column

  2. saybye said

    It works fine on my 10.1.3.2.1 installation!

  3. RamC said

    Is there any way to sort these prompts either in answers or the dashboards in descending order?

    I realize that if u choose a calendar picker then it chooses the current_date by default.. But if if want to do it in a drop down prompt like this, how do i accomplish this?

    I tried sorting the column in answers, but that doesnt seem to affect the prompts!

  4. Venkatakrishnan J said

    @Ram – Check my blog entry here http://oraclebizint.wordpress.com/2008/02/14/oracle-bi-ee-101332-changing-sort-order-in-dashboard-prompts/. It has an example.

  5. Sneha said

    Hi

    I have two columns with TIMESTAMP data type(One is In time and the other is Outtime).The values are as below.
    In Time Out Time
    14-jan-08 1:40:45 AM 14-jan-08 1:30:45 PM
    15-jan-08 3:23:54 PM 15-jan-08 5:23:54 PM
    16-jan-08 3:23:54 PM 16-jan-08 4:23:54 PM
    17-jan-08 3:23:54 PM 17-jan-08 7:23:54 PM
    18-jan-08 3:23:54 PM. 18-jan-08 10:23:54 PM.

    I need the out put as below
    11:50:00
    2:00:00
    1:00:00
    4:00:00
    7:00:00

    Also can you pls let me know how to sum the above.

    Thanks for your help

  6. Pallavi said

    hi sneha,
    this is the code please replace with in-time and out-time accordingly.it is very big code but it will solve the purpose.

    CAST ( FLOOR( TIMESTAMPDIFF( SQL_TSI_MINUTE , “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”, “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”) / 60) AS VARCHAR ( 7 )) || ‘ : ‘ || CASE WHEN EXTRACT( SECOND FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”) > EXTRACT( SECOND FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”) AND EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”) > EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”) THEN CASE WHEN 60 – EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”) + EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”) >= 60 THEN CAST ( ABS(60 – (60 – EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”) + EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”)) – 1) AS VARCHAR ( 7 )) ELSE CAST ( ABS(60 – EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”) + EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”) – 1) AS VARCHAR ( 7 )) END WHEN EXTRACT( SECOND FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”) > EXTRACT( SECOND FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”) AND EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”) < EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”) THEN CAST ( ABS( EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”) – EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”) – 1) AS VARCHAR ( 7 )) WHEN EXTRACT( SECOND FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”) < EXTRACT( SECOND FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”) AND EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”) = 60 THEN CAST ( ABS(60 – (60 – EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”) + EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”))) AS VARCHAR ( 7 )) ELSE CAST ( ABS(60 – EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”) + EXTRACT( MINUTE FROM “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”)) AS VARCHAR ( 7 )) END END || ‘ : ‘ || CASE WHEN ( TIMESTAMPDIFF( SQL_TSI_SECOND , “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”, “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”) / 60.0 – TIMESTAMPDIFF( SQL_TSI_MINUTE , “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”, “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”)) * 60 < 0 THEN CAST (60 + ( TIMESTAMPDIFF( SQL_TSI_SECOND , “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”, “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”) / 60.0 – TIMESTAMPDIFF( SQL_TSI_MINUTE , “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”, “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”)) * 60 AS VARCHAR ( 7 )) ELSE CAST (( TIMESTAMPDIFF( SQL_TSI_SECOND , “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”, “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”) / 60.0 – TIMESTAMPDIFF( SQL_TSI_MINUTE , “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CREATE_DT”, “Reuters Reports”.””.”MART_OWNER_UAT2″.”TROUBLETICKETFACT”.”CLOSURE_DT”)) * 60 AS VARCHAR ( 7 )) END

    regards,
    Pallavi

  7. Sanjeet said

    Hi Venkat,

    Can we remove the timestamp format from calendar feature. The thing is if we try to make any changes in the fx column, it changes the format of that particular field from date to varchar and the calendar feature is changed to an edit box.

    Please help me out in this. Thanks in advance.

    Regards,
    Sanjeet.

  8. Klara said

    Hi Venkat,
    it is a good article. But how can use a promted date in a column definition? How have I to format like this “case when DayColumn = PromptedDate”

    DayColumn is a column in the repository, PromptedDate is presentation variable set by the calendar. I can not solve this comparason.

    Please help me. Thanks in advince.

    Regards,
    Klara

    • Windoki said

      Klara:
      You can use the presentation variable (set it in the prompt design page).
      Use it in the fx like this: case when @presVarName = DayColumn …
      (If it is not working, than CAST function may help you)

      Regards,
      Windoki

  9. Daniflo said

    I have a solution.
    In prompt put 2 fields which are declared as date.
    for each of prompts take a calendar as control, define Presentation variable (DateFrom, DateTo)and give it a label whatever you like.
    Then create a request and on desired date filed make a filter
    DesiredDate is between @{DateFrom} , @{DateTo}
    Then you will get the same results as you have a prompt with between and you have two presentation variables which you can you in your reports.
    I hope that I helped someone.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: