Current data in Universe as a object

building semantic layer
1

I want to add the current date as a object in the universe.
I would also like the date of yesterdat as a object in the universe

If use use the currentdate() it does’nt work.

fmonsma (BOB member since 2007-07-30)

2

If its Oracle then use this

 sysdate

SQL server

 {fn CURDATE()}

haider :es: (BOB member since 2005-07-18)

3

Hmm… I got a “table Helpdesk.Date”. I’ve got a query which need to give the results for yesterday. I don’t want to change the report every day with my own hands.

If I fill in "{fn CURDATE() -1 } in Designer field “select” it doesn’t work.
If I fill in "{fn CURDATE -1(Helpdesk.Date)} it doesn’t work
If I fill in sysdate it doesn’t work either. It gives a SQL error.

I tried currentdate(Helpdesk.Date) -1 and didn’t work…:S

fmonsma (BOB member since 2007-07-30)

4

Please tell what database are you using.
You cannot go on doing trial and error for all date functions across different databases

haider :es: (BOB member since 2005-07-18)

5

if i understand correctly you want a report to generate a query that is something like,

SELECT … FROM … WHERE Helpdesk.Date = yesterday

You have two options: one is two create a Predefined Condition in the universe and define WHERE clause:

Helpdesk.Date = SYSDATE - 1 (oracle syntax)

Then you can add this predefined condition to query
Alternative is to create a new object called say ‘Yesterday’ where its SELECT is SYSDATE - 1. This will fail parsing in universe but will still work. If using this technique make sure you set the object properties so that it can only be used in a condition and not in a result. In the report query you then add a filter Helpdesk Date = Yesterday (both sides are objects)

agulland :uk: (BOB member since 2004-03-17)

6

Database Designer FAQ:

Dave Rathbun :us: (BOB member since 2002-06-06)