{"id":196,"date":"2007-07-31T01:44:00","date_gmt":"2007-07-31T01:44:00","guid":{"rendered":"\/lisa\/post\/2007\/07\/31\/Generalizing-date-related-report-parameters.aspx"},"modified":"2007-07-31T01:44:00","modified_gmt":"2007-07-31T01:44:00","slug":"generalizing-date-related-report-parameters","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2007\/07\/31\/generalizing-date-related-report-parameters\/","title":{"rendered":"Generalizing date-related report parameters"},"content":{"rendered":"

\nIn a recent MSDN forum question about how to Increment a Date in a Reporting Services subscription<\/a>, 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: \n<\/p>\n

<\/p>\n

\n

\n\tOne 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.\n\t<\/p>\n

\n\tLet'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.\n\t<\/p>\n

\n\tDon'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.\n\t<\/p>\n

\n\tA 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>.\n\t<\/p>\n<\/blockquote>\n

<\/p>\n

\nIt 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.\n<\/p>\n