In a recent MSDN forum question about how to Increment a Date in a Reporting Services subscription, I described how I approach the problem of a report that has been designed with one or more datetime parameters, and now has to be run on a schedule. Here's an excerpt from that answer, which I thought I'd expand on a bit here, because my actual method goes further:
One way I often have to handle this is to have params that are date based (for interactive use) and also an alternative set of parameters that are "DaysOffset" based. The latter are of integer type, and are typically used for subscriptions. They all work of DATEDIFF() with GETDATE() internally in my sprocs.
Let's say I have a report that shows shipping orders. It might have two params, StartDate and EndDate, and an integer param ShipDateDaysOffset. If a user says "I want to receive the shipping orders report once a week with all the orders that shipped that week", I can set up the args as NULL, NULL, -7 in the subscription.
Don't necessarily hide these integer offset params from your interactive users. You'll be surprised at how often they'll go for the convenience of using a number of days offset, rather than thinking about dates. You need to make sure they understand that these are *alternatives* — and which params (usually the dates) take precedence if both sets are filled out.
A good prompt for this type of param is something like "Days from now". But where the event typically took place in the past (like in my example) you can use a prompt such as "Days ago" instead, to avoid users having to type a negative number to get it right, and just set up the DATEDIFF() the other way around <s>.
It turns out that it's possible to categorize a large percentage of the kinds of date-related questions that people are likely to ask in a relatively small set of English-language constructs. I like to do this without regard for what the dates actually represent, and even add in some non-date concepts in a generic way. Once you've done this, you can let users figure out what their questions are, by themselves, pretty flexibly. Here's a rough list of the types of questions we tend to see, including (in brackets) a "token" for each type, which I'll explain below.
- [DATEOFFSET] A date value appears <N> number of days ago, or <N> days in the future.
- [DATEBETWEEN] A date value appears between two date literals.
- [DATEPENDING] A date value appears exactly on a certain date, or is pending <N> number of days from now. NB: The latter is really the same as [DATEOFFSET] but having two offset types allows us to set one in the past ("days ago") and one in the future ("days from now") without negative numbers.
- [NOTYET] One date value has appeared <N> or more days ago, or earlier than a particular date literal, and a second activity date is still NULL ("has not yet occurred").
- [DATEGAP] There are <N> or more days between two activity dates. Managers love this one! We try to keep them from using it punitively <g>.
- [STATUS-STILL] One date value has appeared <N> or more days ago, or earlier than a date literal, and a non-date status value has not moved forward.
Users can pick the type of question they're trying to ask
from this generalized set, and apply it to any dates in the system
that happen to interest them. My sproc builds the appropriate query, using the token-type to figure out the relationship between the date and offset values that have been passed. It adds in a large number of output columns and filters provided in later steps of this wizard (the ASP.NET wizard control class is really nifty for this kind of stuff).
It's not terribly elaborate, and I'm sure lots of people have done something similar, but I haven't seen any interfaces that categorize date expressions in quite this way, so I thought I'd mention it.
The report result of this generic interface, of course, is not very elaborate either. It's just a generic XSLT that dumps the data out for them in a couple of pre-determined target types. (If anybody is interested I will post an example of this.)
But users think of this interface as a kind of sketch pad. Since they can "save their query" at the end (in an XML format, naturally <g>), we're half-way through the requirements discussion already. Since they can edit the date and offset parameters in their recalled query on the fly when they want to "re-run the report", sometimes just the generic output is fine to give them quick ad-hoc answers. Other times, they re-arrange the Excel output from the generic report and we use that as the "sketch" of the full blown custom report. Works out well for everybody.
Yeah, all right, not everybody. Not all users like to think at all. Can I help it if I enjoy making life more fun for those that do <g>?