Dynamic date filter in input query?

Lisa_M

Hi Chaps

I'm building a report to deploy to my user's desktops pending a delivery via OBIEE.  I need to provide them with the last three month's revenue data as part of a trending report.

Up until now, I've pulled the data directly from the data warehouse with an input tool, then used a date filter like this:  DateTimeAdd([DateTimeNow], -3, 'month') to get the last three months at the point at which the report is run.  It's not working EXACTLY as I'd like it, because I want it to pull the last three months before the month today is in - so for example, if I ran this today (26/01/2018), I should get Oct 17, Nov 17 and Dec 17.  But a bit of trial and error, and I'll figure that out.

The real issue is that it takes AGES to run, because the input tool is pulling ALL of the data, and THEN filtering.  And I have very impatient users (you think they'd be happy I agreed to do this - not fuss about running time, sheesh!).  So what I'm trying to figure out is if I can filter the data as part of the input tool.  I've tried this:

Date_Column >= DATEADD(MONTH, -3, GETDATE()) 

and

DATEDIFF(MONTH, Date_Column, GETDATE()) <= 3

But no dice - when I test the query, I get a data wrap OCI error.  Any suggestions how I could do this?  Any help very gratefully received!

Lisa

Lisa_M

Hi Zak

I figured this out a while back - I went with this in the end:

Where To_Date(To_Char(REPORTING_PERIOD), 'YYYYMM') Between Trunc(Add_Months(SysDate, -3), 'MM') And Last_Day(Add_Months(SysDate, -1))

This pulls a rolling three complete months of revenue data from the database (Oracle 11g) for my users - and them I'm doing some other interesting stuff where I'm then comparing that to the source of the data and analysing trending, which shows my user where (if) something weird is going on - ie if the revenue is trending down but the source is trending up, etc, etc.

Thanks anyway though.

Cheers

Lisa