Using currentdate() on webi query

[Former Member]

Hi.

there is a requirement for all the reports in WEBI documents to be restricted by a date filter. So this date field needs to be restricted using CurrentDate() ...system variable. Is this possible at all in WEBI? If yes, how can this be used?

Thanks!

[former_member59613 · Accepted solution]

The easiest way to do this might be to create a filter in the Universe (UNX) within the IDT tool.

Here are some high level steps:

1) Edit the Business Layer of your universe and add a new Filter

2) Use something like the following in the WHERE clause of the filter:

convert(DATE, table.dateobject) = convert (DATE, {fn CURDATE()})

3) Save the filter and use it within your Webi documents that you want to filter on the Current date.

You could also edit the SQL of your Webi documents and customize the Where clause to include a similar Where clause that filtered on your date dimension.

For SQL Server, I used CURDATE() to get the current date, for your database, it might be a different DB function.  You'll have to check your documentation.

Other than that, there isn't an out of the box way to do this that I can think of.  I know there have been a few ideas (enhancements) submitted around this here:

https://ideas.sap.com/SAPBusinessObjectsWebIntelligence

I'd recommend finding one that matches your need and up-voting it.  Or adding a new idea if you don't find one that fits your needs exactly.

Thanks

JB

[Matthew_Shaw · Accepted solution]

I'd just add that you should set the filter to be a mandatory filter. That way the filter is always applied.

[former_member59613 · Accepted solution]

Good tip Matt!  Thanks.

[Former Member]

Thanks JB... we went with the getting the date on the Universe itself ...editing the SQL for the webi was also a good option, will keep that in mind for future.