Scheduled Reports & User input in Prompts

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:

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

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):

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

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

…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 🙂