BOXI R2 Relative Date (Oracle)

[system]

I am a WebI report writer. We use Oracle.

I have asked the Universe folks to include a relative date capablilty so that we can run scheduled reports based on:

Last Quarter-end
Last Month-end
Last Week (consistent day of week)
Yesterday

Without this functionality, the schedules need to be set up period by period with different date(s) for each instance. (way too much maintenance)

I’ve been told it’s not a high priority. so I have two quesitons

1-how hard is it to add this functionality to the Universe(s)
2-any thoughts on selling its value would be appreciated.

Thanks and best regards

---

LaurenBOXI (BOB member since 2009-09-15)

[system]

That is very easy.

"Yesterday’, for example, as a Predefined Condition, would be:

trunc(fact_table.the_date) = trunc(sysdate)-1

The others would be slightly more complex, but easily done with combinations of next_day/last_day/add_months.

Joe

---

joepeters (BOB member since 2002-08-29)

[system]

And this will help too:

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server. Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8. Yesterday cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) Today cast(convert(char(10),getdate(),23) as datetime) The following also works for today: dateadd(dd, datediff(dd,0,getdate()), 0) Start of Current Month cast(convert(char(7),getdate(),23)+'-01' as datetime) End of Current Month dateadd(d,-1…

---

Marek Chladny (BOB member since 2003-11-27)