Search code examples
oracle11gobiee

Default Prompt to first day of current month on OBIEE

I have a Analysis created in OBIEE 11G, by default I want it to have the default values in: is between Calendar Date [First day of current month] - [Yesterday]. For the first day i use the following (but didn't work):

TIMESTAMPADD (SQL_TSI_DAY, (DAYOFMONTH (CURRENT_DATE) * -1) + 1, CURRENT_DATE)
Solution

  • Here you go ChiHam!

    First, I created an analysis and filtered for the desired date column as "is prompted" ("Canonical"."Date" in this example).

    http://i58.tinypic.com/29vjqye.jpg

    Then, I setup a Dashboard Prompt (I chose to put it on a dashboard page, but you should be able to simply go to the "Prompt" tab if you're only running the analysis. Use a column prompt, select the "is between" operator, and select "choice list" for user input. Now the tricky part! Open the options using the small plus sign at the bottom left. Select "SQL Results" for Choice List Values and insert this SQL (you will need to change "Table"."Date" and "Subject Area" to your own date column and subject area):

    SELECT "Table."Date" FROM "Subject Area" UNION SELECT TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE) FROM "Subject Area"

    Now to set the defaults! Select "SQL Results" for Default Selection as well. Insert in the first text box the beginning of the month date using this SQL:

    SELECT TIMESTAMPADD (SQL_TSI_DAY, (DAYOFMONTH (CURRENT_DATE) * -1) + 1, CURRENT_DATE) FROM "Subject Area"

    Insert yesterday's date using this SQL into the next text box:

    SELECT TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE) FROM "Subject Area"

    http://i57.tinypic.com/294hpuv.jpg

    Then I put both objects (Dashboard prompt and analysis) onto a dashboard page.

    htt p://i57.tinypic.com/35i0opi.jpg (delete 'space' in link, sorry posting limitations)

    The results were my dashboard prompt was defaulted to the correct dates as of today (5/24) and my analysis was already run based of the dates without having to hit apply because the analysis is set to "is prompted".

    htt p://i58.tinypic.com/efs9pz.jpg (delete 'space' in link, sorry posting limitations)

    Hope this answers your question!