Setting relative dates in a filter for an OTBI Analysis

Tim Welsh

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

Manoj Cheruvathoor-17353

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
Manoj

Manoj Cheruvathoor-17353

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
Manoj

RichardChan

SYSDATE is CURRENT_DATE in OBIEE for your future reference

Tim Welsh

Thanks Manoj, using a filter on date with the SQL expression of TIMESTAMPADD(SQL_TSI_DAY, -7, CURRENT_DATE)  nailed it.  

Manoj Cheruvathoor-17353

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
Manoj

Nicole Hicks

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) 

Tim Welsh

Nicole, what worked for me was to add the string in the SQL Expression field and set Operator as "is greater than"

Nicole Hicks

Thank you Tim, that's what I was doing wrong.  Woohoo!!!!

Tim Welsh

Manoj,

Do you know how I can capture this date (range) for display in the report output header?  

Thanks,

Tim

Manoj Cheruvathoor-17353

hi Tim,

Do you want the period to be displayed as header of the column? or as the header of the report?

thanks
Manoj

Tim Welsh

Hi Manoj, in the report header, not column header.  Thanks!

Manoj Cheruvathoor-17353

hi Tim,

I tried to get the Period in the report header. don't think there is a way. One question do you schedule this report  and mail it or just run it from the OBI.

Thanks
Manoj

Tim Welsh

Manoj,  It's scheduled and delivered via email in html format.  

Murali Gangidi

Hi Manoj,

Do you know how can this be used in Prompts. I have a Date Prompt and need to default the date as (CURRENT_DATE-1) because the report will be scheduled and can run adhoc when needed to input the dates by the user.

I tried this with SQL Expression in Prompts but didn't work.

 

SELECT
CASE WHEN 1=0 THEN
"Cash Management - Bank Statements Real Time"."Time"."Report Date" ELSE timestampadd
(SQL_TSI_DAY,-1,CURRENT_DATE) END
FROM "Cash Management - Bank Statements Real Time"

 

When i tried this with Variable Expression and it's working correctly defaulting to CURRENT_DATE, but need (CURRENT_DATE)-1

Attached screenshot

 

Murali Gangidi

Do you know how can this be used in Prompts. I have a Date Prompt and need to default the date as (CURRENT_DATE-1) because the report will be scheduled and can run adhoc when needed to input the dates by the user.

I tried this with SQL Expression in Prompts but didn't work.

 

SELECT
CASE WHEN 1=0 THEN
"Cash Management - Bank Statements Real Time"."Time"."Report Date" ELSE timestampadd
(SQL_TSI_DAY,-1,CURRENT_DATE) END
FROM "Cash Management - Bank Statements Real Time"

 

When i tried this with Variable Expression and it's working correctly defaulting to CURRENT_DATE, but need (CURRENT_DATE)-1

Attached screenshot

Farooq H Syed

thank for sharing

Kamran Butt-161785

Hi Murali,

I have a similar requirement in which I need the date prompt to show last year's date. Did you have any luck with this?

Thanks!

-Kamran