Using Parameters with Missing Data

To follow this article, a user will need to have the “Manage Data Pipeline” and “Manage Reports” abilities, as well as basic SQL knowledge. This article also assumes familiarity with Parameters and Custom Report Param Types.

Overview

There may be cases when you want to use a parameter in Report Builder for a column that has missing data. For instance, you may want to use a parameter to filter to a visit location on a visits report where the visit’s location name is sometimes missing.

If the parameter type’s SQL is selecting its options from tables (as is the case for all default param types and often the case for custom param types), there is no built-in support for filtering by missing data. This can cause two issues:

  1. There will not be a “Blank” option in the parameter’s dropdown for users to choose.
  2. Even if the user selects “All” for the parameter, blank data will still be filtered out. This is because “All” does not mean all report records; it means all of the parameter options.

Solutions

There are a number of ways to resolve this problem, discussed below.

Solution 1: Clean up the data in the EHR

This may be a good opportunity to enter data into the EHR that was erroneously not entered originally. On a case by case basis, this may be the simplest option and it has the benefit of improving data quality for not just this report, but any report that uses this data.

Solution 2: Use Data Grid filters instead

A simple solution may be to use the filters provided by Data Grid views instead of using the parameter at all. Data Grid’s filter will always have an option of “(Blank)” whenever there are missing values in a report. Keep in mind that filtering done on Data Grid view will only apply to the selected report view.

Solution 3: Add a “Blank” option for the parameter

Note: if the original parameter on your report uses a default param type (it does not appear on the Custom Report Param Types Page in the configure menu), replace it with a new Custom Report Param Type before proceeding.

Step 1: Edit the Custom Param Type SQL

Underneath the original SELECT statement, add a UNION that provides an additional id and name for missing data.

  • The ID should not currently exist among the parameter’s options, and should be a value that will never occur naturally in the data in the future. Generally, negative numbers with varying digits are safe.
  • The name should be something like “(Blank)” or “(Missing)”. Adding a symbol such as a parenthesis in front of the name helps to ensure it will appear at or near the top of the parameter’s dropdown list for users.

In the following example, a new ID of -98765 with a name of “(Blank)” is added to a parameter’s original list of locations:

SELECT id, name
FROM locations
UNION
SELECT -98765, '(Blank)'

Step 2: Edit the Report’s SQL Query

Whenever the report’s SQL code compares the applicable field to the parameter, it will need to use the newly created ID value for this field instead of the original value of NULL. To do so, update these lines of code by replacing the applicable field with a COALESCE function with the newly created ID as a backup value for when the field is blank. Please note that you only need to make this edit where the field is compared to the parameter.

In the example below, the code will use the original ID from relevant_locations table if the ID is not NULL. However, if it is NULL, then the COALESCE function chooses the value of -98765 instead. This ensures that every row in the results has a value that can be compared to the selected parameter.

mceclip2.png