Scheduling a Report to Run in BOXI, which uses Current Date

building reports webintelligence
1

Hello, I hope I have come to the right place. I am trying to set up a report in BOXI which will automatically run each week at a certain time.

I understand we need to set up two separate queries, one which displays the current date, and the other (with the bulk of the queries and info for the report) which references the 1st query (the one displaying the current date).

I would want all the data to go from the current date all the way back to January 1, 2012 (Year to Date).

However, when I try to drag the “Current Date” field into the “Result Objects” section. It can only be dragged into the “Query Filters” section for some reason.

Can someone please help me set this up?

DPG (BOB member since 2012-08-09)

2

You need to create a dimension in universe that refers to current date

summoner :hong_kong: (BOB member since 2008-12-18)

3

How can I do that? Does “Current Date” have to be in the “Results Objects” section?

Or could the “Current Date” just be in the “Query Filters” section, with another field in the “Query Filters” section? I actually tried doing that, with the week number displaying in the “Results Objects” section, but it didn’t work…

DPG (BOB member since 2012-08-09)

4

I may be looking at this too simply, but if I wanted the year to date results, I’d ignore Current Date altogether and set up a query filter something like this:

[Date Object] is greater than or equal to 1/1/2012

That would return all the data up to the latest date available in the database…

mrsbelley :us: (BOB member since 2010-09-22)

5

Hi DPG,

There are 2 different ways to do this.

  1. Once you create a Report with the date Promt. Go to view sql and edit the sql to take the prompt condition and use Trunc(sysdate) and save the report. When ever it runs it uses the sysdate from DB.

If you use the Current date from BO there might be some issues. If you running a report in USA for USA Date and if the server is located in AUS then it would take the wrong date.

[Date] = Trunc(sysdate)

  1. Create a new variable in the Universe saying Trunc(sysdate) and use that in the report.

Hope it helps you…

sandeepmunagala :us: (BOB member since 2010-09-07)

6

I do not prefer this action as you need custom sql. It becomes difficult for maintenance in later stage

Another approach is to create a filter instead. That way you could minimize the mistakes during report setup

summoner :hong_kong: (BOB member since 2008-12-18)