Dates as Filters

Date Filters in OBIEE

Filtering dates by default in your Answers is a fairly common theme.
You also have the option to replace the IN with greater than or less than.

Yesterday:

From Your Analysis
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE))



Setting the Dashboard Prompt:
SELECT TIMESTAMPADD (SQL_TSI_DAY,-1,CURRENT_DATE) FROM "ReportDate"


I'll leave out setting the dashboard prompt with the next few examples as the syntax is similar and the changes from the filter can be worked out from the example above.


First Day of the Month:
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , CURRENT_DATE ))


First Day of the Year:
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFYEAR(CURRENT_DATE )-1) , CURRENT_DATE ))


First Day of the Quarter:
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY, -1*(DAY_OF_QUARTER(CURRENT_DATE )-1) , CURRENT_DATE ))


This is not an exhaustive list of what is possible, but should get you started.