I’ve always had a conflicting relationship with Microsoft SQL Server Reporting Services.Sometimes, I love it, but some other times, it makes me so angry, I want to punch it in the face…It is super easy to create a basic report (I’ve done it plenty of times) but with the right design skills – which I obviously don’t have – , you can create jaw-dropping reports that will definitely capture the attention of your audience.
Unfortunately, rendering the report is only a small part of the report experience. To bring the report to life, you need to select values for your report parameters, and this is where Reporting Services kind of lets you down.
I’ve witnessed this problem many times over. Every time it pains me to see that the report rendering experience is hurt by how we fumble to select the correct parameter values, especially when scrolling through a long list of values.
Life is a journey, not a destination
Ralph Waldo Emerson
No matter how nice the final report is, if you struggle with the report parameters, you’ve ruined it. It takes some major distracting from the speaker to recover from that failure.
This post explores two ideas to make it easier to select parameters.
Use cascading parameters
A large number of items to display in the parameter dropdown (i.e. potential values for the parameter) can degrade the report experience for 2 reasons:
- The report manager freezes while it retrieves a large number of items from the data source. This link provides some tips on speeding up data retrieval: http://www.mssqltips.com/sqlservertip/1904/optimize-report-parameter-dropdowns-in-sql-server-reporting-services/
- And if you have a large number of items to display in your dropdown, selecting the right item will be painful because you’ll have to scroll through a long list of values to find the one you want.
Here is a screenshot of the AdventureWorks demo report that ships with Reporting Services.
Notice the length of the stores list? You would think that the product would provide some kind of mechanism to filter the list. Sadly, no .. And there is no way to increase the size of the dropdown to see more items… So, good luck finding “The Bike Shop” in the list…
When possible, you should try to use cascading parameters to reduce the number of items to choose from. For example, you could cut down on the number of items by allowing a user to first select a state and *then* display the list of stores in the selected state.
Fewer items to display, more chances you’ll quickly find the information you want !
Use subreport navigation
The second trick I’ve used many times requires a little bit more thinking (and redesign of the report flow). But it’s definitely worth the time because it has the added bonus of greatly enhancing the user experience.
The idea is to break down the report in two parts:
- A first report that displays a high level view of the data
- A second report (which is called as a subreport) that focuses on the details of the data you want to analyze
I really like the subreport navigation, because it focuses the user attention on the data itself, not on the way you select parameters.
Using the previous example, the first report would display the list of stores, filtered by state.
All you have to do is simply click on the store name. The selected store is then passed as a parameter to the subreport.
Easy and intuitive ! No more searching for a specific item in a dropdown with a long list of parameter values…
How about you?
What’s your experience with long lists of report parameter values? Have you run into this problem before?
Contact me on Twitter at @PatriceTruong