SSRS Tip: Using wildcards in Report parameters

Searching and filtering free text data can be a problem due to human error. Typos, transposed characters and even data entry into the wrong field present a challenge when trying to report on the data. In a SQL query we can use LIKE and wildcards to try to work around these issues.

By using LIKE in the query or stored procedure called by an SSRS report, we allow the report user to leverage SQL wildcards to improve the results of the report.

With the ability to arrange the display of parameters in the newer versions of SQL data tools, we can provide some hints for the user about using wildcards.

Our sample report captures surgical implants/explants from the Meditech Client Server scheduling tables. The rendered output of the report is not important as we are looking strictly at the parameters in use.

SQL Server T-SQL Wildcards

More information on using wildcards and the LIKE predicate can be found at LIKE (Transact-SQL).

SQL recognizes several wildcards that can be used in search predicates using the LIKE keyword.

Sample report

Our sample report uses several free text parameters that can leverage wildcards. The parameters also allow the user to create custom lists using the semicolon (;) as a delimiter.

The four parameters on line 4 of the screen accept wildcards and delimited lists. Each parameter is also optional.

The parameters on lines 6 and 7 provide some hints for the user. We set the parameters to accept blank values with the text as the default value. While we cannot prevent the user from editing these parameters, the report simply ignores them.

We can create a list of catalog numbers with the wildcards described above to make our search more flexible:

The Lot Number, Serial Number, Batch Number parameters can be left blank or values can be entered to further refine the search.

The Stored Procedure

Inside our stored procedure we define the search parameters as VARCHAR(MAX)

Note that by allowing blanks in the report a value is passed in even if its empty (‘’).

First we create a temp table for each parameter:

Now populate each table using a split function:

Note You can append a % wildcard at the end of each string to automatically use the strings as a ‘beginning with’ function.

In the main query, we use LEFT JOIN and LIKE to capture matches:

Note that the Items in the wildcard lists are already forced into UPPER() case.

And then we do the final filtering in the WHERE clause with a CASE statement:

Note that by using this CASE statement we’re explicitly controlling the order of evaluation for the criteria in this section of the WHERE, this lets us check for no parameters being passed first, before do any more evaluation of the CASE statement.

Extra Credit

Consider if we can use COALESCE() instead of this case statement… it’s shorter to write, but does it return the same results?

And…

As ever, if you need help with Meditech DR index creation, reporting, extract or analysis please feel free to give your iatricSystems Account Executive a call or email info@iatric.com to discuss how we can help support your team!