Validating Date Prompt To be less than Current Date OBIEE

Question

I have a requirement to create two validations on the date prompt:

1) The From Date has to be less than To Date

2) The To Date has to be less than or equal to current date

I created a conditional analysis wherein From Date is < To Date, which works, but when I try to create an advanced filter wherein @To_Date <= Current_Date I am getting an error.

Error getting drill information: SELECT date'2016-08-24' saw_0 FROM "Workforce Management - Processed Time Cards Real Time" WHERE(date'@{To_Date}' <= (SELECT VALUEOF("CURRENT_DATE_REP_OTBI") FROM "Workforce Management - Processed Time Cards Real Time" FETCH FIRST 1 ROWS ONLY))

If anyone can help solve this, it'd be really helpful! Thanks

Answer 1

You need to add a default value when referencing presentation variables in logical SQL queries or formulas. Especially if these are dates.

I created an analysis based on the following LogicalSQL and it worked.

SELECT date'2016-08-26' saw_0 from "subject_area" WHERE (date @{to_date}{'2016-08-26'} < CURRENT_DATE)

Notice the following:

• The presentation variable @{to_date} goes with a default value (noted by the second curly brackets). This helps OBIEE to validate the query. Failing to add the default value will give you the "getting drill information" error.
• Instead of a session RPD variable, you can use CURRENT_DATE. It simplifies the query.

The above query will return the date in the SELECT clause, but if the to_date is greater than CURRENT_DATE will return no data.