Categories
Setting relative dates in a filter for an OTBI Analysis
Summary
In an OTBI Analysis is there a way to add into a relative date range into a filter?
Content
I need an Analysis with a filter to pick up dates within the last week. I don't see a way to do this in the normal filter properties and I'm getting errors when i add the below code into "Convert this filter to SQL" Is there a way to do this either with the regular filter or with the Advanced SQL filter?
BETWEEN (SYSDATE - 7) AND SYSDATE
Answers
-
Hi Tim,
you can look at the TIMESTAMPADD function. use a negative number (I think it should be -7 in your case) and the sysdate. use this in the in the filter.
Thanks
Manoj0 -
hi Tim,
I think this should work for you
"date variable" > TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)
date variable is the date field that you are filtering for. just put this in the sql filter. Hope this helps
Thanks
Manoj0 -
SYSDATE is CURRENT_DATE in OBIEE for your future reference
0 -
Thanks Manoj, using a filter on date with the SQL expression of TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE) nailed it.
1 -
Hi Tim,
glad to hear, it worked for you. instead of the day you can use these variables
SQL_TSI_SECOND,SQL_TSI_MINUTE,SQL_TSI_HOUR,SQL_TSI_DAY,SQL_TSI_WEEK,SQL_TSI_MONTH,SQL_TSI_QUARTER,SQL_TSI_YEAR for different time periods. You can see worked examples in the OBIEE help https://reporting-bichf06.taleo.net/analytics/olh/l_en/calendar.htm#CHDJDGBE . Hope this helps.
thanks
Manoj0 -
FILTER("Requisition Status - Historical"."Req. Historical Status Start Date" USING (("Requisition Status - Historical"."Req. Historical Status Start Date" IN (TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)))))
I am getting an error on the formula above. Not sure what I am doing wrong. Can you provide instructions on where and how to enter the formula and direct me to the training on formulas/filters?
"date variable" > TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)
0 -
Nicole, what worked for me was to add the string in the SQL Expression field and set Operator as "is greater than"
1 -
Thank you Tim, that's what I was doing wrong. Woohoo!!!!
0 -
Manoj,
Do you know how I can capture this date (range) for display in the report output header?
Thanks,
Tim
0 -
hi Tim,
Do you want the period to be displayed as header of the column? or as the header of the report?
thanks
Manoj0
