getDate in universe to display current date in report?

[Former Member]

Hi All,

Can anyone help me in getting the currentdate data in the report, or even yesterdays data.

I am using SQL Server 2008, SAP BO 4.0 SP3.

I have written trunc(getdate()) in select clause, but it showing error as cannot prepare the statement, parse failed.

Please help me in this.

Thanks a lot in advance,

Santhosh.

[Former Member · Accepted solution]

Over on the BOB Forum, I've created a sticky topic that explains all about relative dates and how to create them.

They will not parse in the universe design tool or IDT because the parsing algorithm looks for table.object or owner.function type constructs. getdate() in SQL Server and sysdate in Oracle don't have these constructs and therefore fail to parse. That doesn't matter, just build them - I'd recommend building all the objects listed for SQL Server in the link below. They will work fine in queries when used with any table-based object. They are handy to use when scheduling reports.

The sticky topic:
http://www.forumtopics.com/busobj/viewtopic.php?t=152613

[Former Member]

Hi Santhosh,

I see that you use trunc function and I assume that you expect it to remove the time component of the getdate() function. You could achieve that in SQL Server using many methods. You can read this article - http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-serv...

[amitrathi239]

Hi,

use these syntax while creating current and yesterday dates in universe objects.

Current date Object=GETDATE()

yesterday Date Object=DATEADD(d,-1,GETDATE())

Amit

[Former Member]

Hi Former Member

I have tried it, still showing error as cannot prepare the statement.
Can you please correct me. below is the screenshot.

Thanks,

Santhosh

[Former Member]

You will need to define this in either a pre-defined condition or in the where clause of an object

[Former Member]

I have done with Where clause, can you please educate me on pre-defined condition also,

Please describe on that too.

[Former Member]

I had written it in Where clause as below image, it is not showing in report.

Can you please correct me

[Former Member]

Hi Santhosh,

You may want to go through the videos in this link (http://scn.sap.com/docs/DOC-8461) to learn more about how things work in IDT.

[amitrathi239]

Hi,

You need to create two objects in universe.One for current date and one for yesterday date.

getdate() will not parse in the universe but it will work in the report.

After that in the webi report query filter you can add the condition like

Created date is equal to Current date object.

With this your report will fetch the data for current date only.

or create predefined condition in universe .

Amit