Dates are likely the interactive parameters that are included most often in SSRS reports. What’s happening now? How does that compare to what happened then? These are some of the questions we ask, and date parameters help us to answer them. Often I see date parameters being used in a way that works, but does not offer a very good user experience. By following a few simple ideas, you can design the date parameters in your SSRS reports to provide a faster, easier, and more intuitive reporting experience for your users.
Seperate Large Datasets into Smaller Chunks
Do you have an SSRS report that runs for a specific user-definable day? Don’t create a single dropdown parameter for your user to select one day out of years worth of days. That list could be huge! For a normal computer-based user a large dropdown can become a serious inconvenience. For users of smaller touch-enabled devices it can make reports nearly impossible to work with.
Try to steer away from creating any single parameter that includes more than 30 or 40 items. If you have a large number of items, break them up into smaller, cascading sets. In dates, for example, consider making your first parameter a year or a year/quarter. Then your second parameter can can allow more detailed date selection within the boundaries of the first selection. It’s much easier to make detailed selections based on this cascading method than having to pick through a single large list.
When you’re creating a dataset using the query designer in SSRS and you’re working against an Analysis Services cube, you can include hierarchies as parameters. When you do this, a hidden dataset is automatically created for the hierarchy parameter. It is set up in such a way that your parameter dropdown will have a “visually cascading” hierarchy effect. It’s a pretty cool effect, and can partially alleviate the problem of long dropdown lists by breaking things up visually. But date hierarchies are usually still too long to make this very useful, and should at least be broken up at a high level first.
Keep the Dates Relevant to the Report
Let’s say you typically include one or several cascading datasets in your SSRS reports that provide the data for date selection parameters. Don’t use a generic collection of date datasets in all of your reports. If you do so, your parameters will likely contain many dates in the past, present, and future that are not a part of the primary dataset being used by the report. Users will have to wade through all of these extra options to find what they need, which can be cumbersome.
Link your date datasets to your main fact datasets in your SSRS reports when possible. By doing this your dates will be much more relevant to the report. Users will have the ability to only select date parameters that actually exist in the data they’re interested in. Less options to select from in lists and the knowledge that all of the selectable dates will return useful information makes for a better user experience.
In addition to only showing dates relevant to the important report data, only show dates at a level relevant to the operation of the SSRS report. If it’s a weekly report, only allow a detailed date selection down to a ‘week-ending date’ level, not to an individual date. Similarly, if it’s a monthly or yearly report, only allow selection at those respective levels. You want to make it as clear as possible what you’re reporting against. This helps to avoid confusion, especially for a user new to the company or new to using the report that is not familiar with exactly what the report is intended to display.
Sort Your Dates in Descending Order
While it’s not always intuitive to work with things sorted in descending order, it can be useful for dates. In SSRS reporting, it’s usually the case that the default date or date period a user will be running a report for is the current date. Having this date at the top of your date list makes sense. Then, if a user needs to run the report for an older date period, it will likely be one of the next ones in the list. Keeping the most often run dates at the tops of the lists makes them more accessible and will be appreciated by your users.
There’s an additional benefit in SSRS that comes from sorting your date parameters in descending order. If you’ve set up your date datasets to only show valid values, the top date in the parameter list will be your most recent date. If this is the case, you can simply set your default value for the parameter to the same dataset. The default value will pick up the first date from the dataset. In this way, you have easily set your SSRS report to report off the most current date by default, without having to do any additional setup to derive your default date!
Share Your Thoughts
By thinking ahead and keeping the user’s experience in mind, you can create more useful date parameters in your SSRS reports. Have any questions about what was mentioned here or use any other nifty date parameter options? Feel free to share your ideas.