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