🪄SSRS (& Paginated Reports): Poor Design of Parameters

Introduction

The handling of parameters in SSRS (and Paginated Reports) can become a problem for reports' usability when certain beast practices are not considered for populating the controls behind the parameters. This post attempts to address some common cases.

Dropdowns Constrained on the Source Query 

Typically the values needed in dropdown parameters are stored in tables or are predefined like in the previous post though it's not always the case. Supposing that there's no table to store the countries, I have seen reports in which the developers identified the values by using queries like the following:

-- Countries
SELECT DISTINCT SIC.CountryRegionCode Id
, SIC.CountryRegionName Name 
FROM Sales.vIndividualCustomer SIC 

Just imagine that the above query has hundred of millions of records. Even if the query was optimized, it might take minutes to run. 

It is true that the query limits the displayed values only to the ones used, however as soon the volume of data increases this can slow down the report considerably to the degree that the reports becomes unusable. Unfortunately, some developers favorize this approach also when there is a table available. Just imagine that to run a report with complex logic the logic will be run a few times to populate the selection controls for parameters before actually being able to run the report. This slowly puts a burden on the source system and when similar reports are run, this can bring the source system to its knees. In extreme situations they might not be able to run the report at all (e,g., when the report timeouts). 

Building the queries for parameter's population in this way adds also some synchronization issues between the parameters, that are not so easy trackable as the values are not available for selection.

Therefore, to fix this, if no tables are available in the source system for the dropdowns then it's a good idea to create some tables independently of the source system and populate them periodically.  One can do this with a script that runs with data refresh or even maintain the values manually. This works best for data warehouses, but also for OLTP solutions when reports are built on top of the databases behind.

If the users needed indeed only the used values, one can introduce flags in each table which will show whether the value is used or not. 

Too Many Values

If a dropdown has more than a few hundred values then consider having the free text instead of a dropdown. It's much easier to type some values that searching for them in a list. It's true that SSRS lacks a search functionality, which would make such searches easier. One can use in theory a passthrough report, however this has limited functionality. 

Wildcards Everywhere

Implementing wildcards in each text attribute is in general not a good idea. Please note that  the wildcards used in front of a search value doesn't make use of the available indexes. 

Too Many Dependent Dropdowns

There are structures which involve more than two dependencies of dropdowns on one another (e,g,, the user needs to select the Country before selecting a Zip code). Each dropdown implies a roundtrip to the data source and back, and upon case the user has to wait a considerable time until the parameters are filled. This in combination with the first mentioned issues can increase the damage. 

Building such dependencies might still be a good idea when compared with the alternative, showing in the dependent control all the values available. 

Use one Table with Dropdown Values

Oracle e-Business Suite and probably other applications store the values for dropdowns in a single table across the system or per business area. That's useful for maintaining the parameters though it can affect the performance when the number of records in such tables is high. Usually it shouldn't be the case, but it happens (e.g., financial dimensions, having values for each supported language).

Populating the dropdowns should happens with minimal wait, otherwise the users will complain. The retrieval from the database might be fast, though one needs to consider also how long it takes rendering the data, especially when searching for a specific value. In many cases, the table(s) behind should be optimized for the purpose. On the other hand, having individual tables for the dropdowns with many values could be a better approach. 

Frankly, this way of storing the dropdown values has a bigger impact when the tables are involved in joins.

Hardcoding Values for Dropdowns

Instead of hardcoding the same values used in dropdown across multiple reports one should consider using a table, misusing a view or even stored procedure for storing the respective values. The overhead of an additional roundtrip to the database bight be negligible when considering the overhead of maintaining the values across multiple reports. 

Populating the dropdowns through stored procedures could upon case offer better performance in the detriment of usability (e,g,, being able to select a subset of the data without using parameters). Besides the Ids and Names needed to populate the parameter(s), one can maintain further attributes that can be further used in filtering. 

Post reviewed on 29-Sep-2023.