Create current date variable at universe level

[Former Member]

   Hi Everyone

I have created report regarding to daily car sales. However there is records registered for further months, 2015 December for instance.

Actually they are error records, but I can't delete them since data transfer is realizing automatically and updates hourly.

That is why I have decided to use prompt for report: date is less or equal to current date. But couldn't find way how to create current date variable.

Please help with this.

Regards...

[sateesh_kumar1 · Accepted solution]

Hi Ofeliya,

What is your back end database .

you can try with below kind of date filter.This will work for Oracle back end.

orders.ship_date = case when @prompt('Enter shipping date','D',,mono,free) = '1900-01-01' then sysdate else @prompt('Enter shipping date','D',,mono,free) end

Dave’s Adventures in Business Intelligence » Using a “Magic Date&#...

[Former Member · Accepted solution]

Hi Sateesh

I use SQL  database.

I want to create variable like select getdate() as currentdate like in SQL.

is this possible at universe level?

[Former Member · Accepted solution]

Yes.

Create an object called Today as:

cast(convert(char(10),getdate(),23) as datetime)

Note that it will not parse because it does not reference a table. Don't associate it with a table just to get it parsing as you will then have that table in every query that uses the Today object, whether you want it or not!

It will work fine when you use it in your query. In your example, create a condition of Ship Date Less Than Or Equal To Today where Ship Date and Today are the names of the two objects. This will then translate into SQL as:

WHERE orders.ship_date <= cast(convert(char(10),getdate(),23) as datetime)

This will parse because the table orders is in the statement.

I hope that is clear.

Regards,

Mark

[Former Member · Accepted solution]

Mark you are great! This is exactly what I wanted to do.

Also thank you for perfect explanation.

Regards

Ofeliya