Extra Pages

▼

Wednesday, April 1, 2009

OBIEE Between prompt using the calendar control

Sometimes you need a between prompt on a date but the fields but you can't use the normal is between because of the complex filter. Here is a possible workaround. In the repository create a dummy table called Dates with two columns Date1 and Date2 based on the current date.

image

Next create a prompt and put the result in two presentation variables:image

Next create the complex filter:

image

Till Next Time

at

10 comments:

  1. eejimkosApril 2, 2009 at 2:03 AM

    hi....
    how about choose two periods of time for a specific measure value using calendar???

    ReplyDelete
  2. Goran OckoApril 3, 2009 at 10:38 AM

    This comment has been removed by the author.

    ReplyDelete
  3. Goran OckoApril 3, 2009 at 10:44 AM

    Hi John.

    The first part of the filter condition (before OR, between part) can be also fixed with two dummy date dashboard prompts:

    1. cast('01.01.2009' as date)
    and
    2. cast('01.02.2009' as date)

    Both are calendar and they are filling presentation variables so u need to use convert this filter to SQL for Calendar Date column to write manually between condition.

    For example:

    Caledar Date between '@{p_date_from}{some default value}' and '@{p_date_to}{some default value}'

    Regards,
    Goran

    http://108obiee.blogspot.com/

    ReplyDelete
  4. John MinkjanApril 3, 2009 at 11:14 AM

    Hi Goran,

    Your right, I like doing it in the repository to avoid problems with casting conventions on the server. Both work.

    regards

    ReplyDelete
  5. SamJune 25, 2009 at 7:38 PM

    Do you know of a way to default the Start Date Calendar prompt to be say, 3 months before the current date? And default the End Date Calendar prompt to the current date?

    Thank you very much

    ReplyDelete
  6. John MinkjanJune 26, 2009 at 8:18 AM

    @Sam,

    Set the default to SQL:

    SELECT CASE WHEN 1=0 then "D0 Time"."T00 Calendar Date" else TIMESTAMPADD(SQL_TSI_MONTH, -3,CURRENT_DATE) end FROM "Sample Sales"

    ReplyDelete
  7. AnonymousNovember 4, 2010 at 11:58 AM

    Hi, good post.
    If I want the First Day of the Current Month and the Last Day of the Current Month?
    For example: I'm on 03/11/2010, so I want it shows 01/11/2010 and 30/11/2010. Thank you very much

    ReplyDelete
  8. AnonymousNovember 4, 2010 at 12:11 PM

    I got it and I want to share it with you:
    First Day:
    SELECT CASE WHEN 1=0 then Time."Start date" else TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE)* -(1) + 1, CURRENT_DATE) end FROM WEB_REPOSITORY

    Last Day:
    SELECT CASE WHEN 1=0 then Time."End Date" else TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))) end FROM WEB_REPOSITORY

    ReplyDelete
  9. John MinkjanNovember 10, 2010 at 2:26 PM

    Thanks for sharing

    regards

    John

    ReplyDelete
  10. RupeshNovember 10, 2010 at 7:59 PM

    Hi Gurus,

    I have created one logical column and casted as an integer

    CAST ( EXTRACT( HOUR FROM DW.TABLE2.COL2) AS INTEGER )

    and attached along with date in page prompt level with greter than condition
    when i selected 10 then it is showing data related to 10 AM only and not showing time after 10 AM and PM as well
    http://forums.oracle.com/forums/thread.jspa?threadID=2127783
    it is really really urgent

    thanks

    ReplyDelete

Note: Only a member of this blog may post a comment.