Rolling dates in prompt

Saro

Hi friends,

Im in obiee 11.1.1.7

I need to have a prompt with a rolling dates of 20 with effective from sysdate. Like for example

if sysdate is 17-1-2016 then i have to display the next 20 dates in the prompt dynamically like starting from 18-1-2016 to 06-02-2016(6th feb). So in that way the dates will be incrementing according to the sysdate

I can achieve the same in the back end through the following query

SELECT TO_CHAR ((SYSDATE+1) - 1 + ROWNUM, 'DD-MON-RRRR') dl_date
     FROM all_tables
     WHERE ROWNUM <=` 20

I do tried the same in the BI, using the variable prompt by substituting with SQL results like below

SELECT EVALUATE('to_char(%1,%2)' AS char, (CURRENT_DATE+1) - 1 + RCOUNT(1), 'DD-MON-RRRR')
FROM "<Subject Area>"
WHERE RCOUNT(1) <= 20

But the above results dint work, as the output in the prompt doesnt display any value and it keeps loading.

Where do i miss, and how to accomplish the above scenario.

Thanks in advance.

Regards,

Saro

Pedro F

Assuming you have a date column you can use, just create a dashboard prompt with that column and in the choice list values choose the option "SQL Results" and use the following query (built the example in sampleapp V506).

SELECT

   "A - Sample Sales"."Time"."T00 Calendar Date"

FROM "A - Sample Sales"

WHERE

("Time"."T00 Calendar Date" BETWEEN TIMESTAMPADD(SQL_TSI_DAY, +1, CURRENT_DATE) AND TIMESTAMPADD(SQL_TSI_DAY, +20, CURRENT_DATE))

This will get you the list of 20 days after the current day.

Saro

Hi Pedro,

Thanks for the reply. I dont have any date column, as of now im displaying the default sysdate in it which i have created using variable prompt. In that case what can i do without a date column.

Regards,

Saro

Thomas Dodds

You need a Time dimension table joined to your fact table ... OBIEE won't make up data

Saro

Thanks Thomas, for the reply. Will make a time dim to deal with my scenario:-)

Regards,

Saro

Thomas Dodds

A time dimension should be built so it is conformed across all your facts ... then you can relate factual data across time.  IE:  number of positive tweets rising over time and sales dollars rising over time