In Oracle BIEE - Trying to Subtract Number from a Prompt Variable on a Filter

Question

I have created a query in SQL that has date filters. I want to move this to Oracle BIEE so that a user can be prompted to enter a date and then the query runs to give the desired output.

Within Oracle BIEE I have created a dashboard and a dashboard prompt which connects to the report. In the Dashboard prompt I have created a presentation variable called "VAR1_EFFDATE" and I want to filter on dates equal to the "VAR1_EFFDATE" or equal to "VAR1_EFFDATE" - 1 day. The following is what I have at the moment:

WHERE b.EFFDATE between @{VAR1_EFFDATE-1}{'01-OCT-20'} and @{VAR1_EFFDATE}{'31-OCT-20'}

The second part "@{VAR1_EFFDATE}{'31-OCT-20'}" seems to be working at it picks the "VAR1_EFFDATE" that the user has entered and not the default ({'31-OCT-20'}). However, on the first part, it always selects the default ({'01-OCT-20'}) and the "{VAR1_EFFDATE-1}" just gets ignored. I have tried using @{TIMSTAMPADD(SQL_TSI_DAY, -1, VAR1_EFFDATE)}{'01-OCT-20'} and it doesn't seem to want to pick the prompted value minus one day either.

Answer 1

I was able to figure this out for those that are interested:

trunc(b.EFFDATE) in ((@{var_effdate}{trunc(sysdate)-1})-1 , (@{var_effdate}{trunc(sysdate)-1}))

Instead of having a static date of 01-OCT-20 and 31-OCT-20 I used the current date (sysdate).