5 Replies Latest reply on Jan 20, 2016 2:43 PM by Thomas Dodds

    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