Often when you schedule a report, you’ll use a “Current date” object to ensure the data returned is from the date the report was scheduled.

But sometimes you want your end users to be able to refresh this report and enter their own date parameters – but it is setup to use a current date object so that you don’t need to enter a date each time you schedule the report. How can we get the best of both worlds?

There are many solutions which usually involve some kind of universe work in the back end, but here’s a nice easy one which requires no universe work at all.

I’m using Island Resorts Marketing as an example, but you can use anything that includes a date object.

Create a query just returning the Reservation Date:

/wp-content/uploads/2014/01/1_359846.jpg

Then, in the query filters section, drag the same object in three times, and arrange it like so:

/wp-content/uploads/2014/01/2_359847.jpg

Make sure you setup the two prompts as Optional. This is very important or this process will not work – they have to be optional prompts! In the above example I’m using a hard coded date in the middle field, but this can just as easily be a Current Date object (there isn’t one in Island Resorts Marketing, and I’m too lazy to add one – sorry).

Now, test your report. Leave your prompt blank and you will return the hard coded date (or object if you have it setup):

/wp-content/uploads/2014/01/3_359848.jpg/wp-content/uploads/2014/01/4_359855.jpg

Now, do it again but this time enter a date into your optional prompt:

/wp-content/uploads/2014/01/5_359856.jpg/wp-content/uploads/2014/01/6_359857.jpg

There you have it – when you don’t enter a value it will use whatever you have in the middle date filter.

/wp-content/uploads/2014/01/2_359847.jpg

…and when you do, it will use whatever you enter into the prompt.

Therefore, when you schedule the report, you just leave the optional prompt blank to use the default from the middle filter. When users refresh the report, they can enter a date and it will return data for the date they enter.

Simple eh? You can apply this mechanism to anything, it doesn’t have to be dates.

Hope you find useful 🙂

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Ganapathy P

    Hi,

    it is giving correct value,showing error plz help

    if date is not given,it has to show current date but it is showing error, find the error screen shot

    /wp-content/uploads/2014/01/currdate_369003.jpg

    (0) 
        1. Neil Mitchell-Goodson Post author

          No, you will need to create a “Current Date” object in your universe and specify this in the middle filter (EQUAL TO>OBJECT FROM THIS QUERY), if you want to default to the current date when no input is entered.

          (0) 

Leave a Reply