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

    Rolling dates in prompt


      Hi friends,


      Im in obiee


      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.