SQL Server Reporting Services Multi-Value Parameter - Wildcard Usage

By: Kenneth Krehbiel   |   Updated: 2018-06-14   |   Comments (2)   |   Related: > Reporting Services Development

Problem

When using a multi-value SQL Server Reporting Services (SSRS) report parameter, it can be challenging to create a wildcard character used to run the report without respect for that parameter.  Now, if the parameter has available values supplied, this poses no issue as there is a ‘Select All’ feature for this exact purpose.  However sometimes, we need a parameter to accept multiple entries, without supplying a list of available values.  Maybe this is a list of Order Numbers.  Maybe this list will be copied in from an Excel file.  Maybe the available parameters change too often to be maintained.  There are several use cases for a multi-value parameter that doesn’t have available values populated.  And in this case, we still need a way to ignore this parameter in favor of other parameters on the report.

Solution

The solution is to select a wildcard character like * or % and then check to see if this wildcard character is in the list of values specified by the parameter.  This sounds rather simple, but the order of comparison is key as will be demonstrated below.

SSRS Multi-Value Parameter Background Info and Overview

Often times in SSRS report development, we have several parameters active on a report and want a way to ignore some parameters while shifting focus to other parameters.  With single value parameters, this is quite strait forward.  There are two common methods: using the NULL check box, and using a wildcard character.  Before we begin looking at those methods, here is the sample data I will be working with throughout this tip:

DECLARE @MyTable TABLE (OrderNo VARCHAR(MAX), PartNo VARCHAR(MAX), DueDate DATE)

INSERT INTO @MyTable VALUES
('12345', 'ASDFG', '20180101'),
('54321', 'FFFDD', '20180105'),
('12121', 'FAFAF', '20180115'),
('15555', 'GDDDG', '20180131'),
('43532', 'SSSAA', '20180204'),
('41414', 'SSSFG', '20180214'),
('33333', 'DFFDF', '20180228'),
('23245', 'ADGDA', '20180331'),
('01235', 'GGGGG', '20180420'),
('00001', 'FDSAS', '20180505')			

NULL Check Box Method:

The first and more common method is to allow null values for the parameter in the report, that way you can select the NULL checkbox which disables that parameter entry and supplies a NULL value to the query.  Here is what it looks like in the report and the underlying query.

SELECT * 
FROM @MyTable
WHERE (OrderNo IN(@Orders) OR @Orders IS NULL)			

The key to this method is the latter part of the WHERE clause where we check if the @Orders parameter is NULL.

Wildcard Method:

This method follows very similar to the previous method, but is worth noting because this is the method we will end up using for our multi-value parameter example.  For this method, we do NOT allow null values and instead opt for our own wildcard character, the asterisk (*).

SELECT * 
FROM @MyTable
WHERE (OrderNo IN(@Orders) OR @Orders = '*')			

Challenge with Multi-value Parameters

With these two methods at our disposal, it could be reasoned that allowing the parameter to accept multiple values would be a breeze.  However this is not the case as SSRS error messages follow quickly when either method is attempted.  Let’s look at each method and the errors that follow.

NULL Check Box Method:

This method is quickly determined unusable due to a restriction within SSRS.  A parameter cannot accept both multiple values AND Null values.  See error Message below:

Wildcard Method:

This method will appear to work at first, but fall short when the user actually tries to specify multiple Order numbers, rather than a single Order.  See below:

We can see the error message in greater detail here:

An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for dataset ‘WorkOrderDetail’.
An expression of non-boolean type specified in a context where a condition is expected, near ‘,’.			

Now this error message is quite vague and hard to troubleshoot.  The only real clue we get is that the error appears near a comma.  This should spark interest as our query did not have a comma.  Here is what happens during report processing that causes the error.

First the parameter @Orders is substituted with our comma separated list of orders.

SELECT * 
FROM @MyTable
WHERE (OrderNo IN('12345', '54321') OR '12345', '54321'= '*')		

Now it should be apparent where our issue is.  In the second part of our WHERE clause, we are comparing a list of values to a single value, in the wrong order.  That is why we are getting a comma error, because the list is out of place.

SSRS Multi-Value Parameter Challenge Resolved

The fix should start materializing for most readers.  All we have to do is swap the order of comparison in our query and be sure to use the IN operator rather than the equals (=) operator:

SELECT * 
FROM @MyTable
WHERE (OrderNo IN(@Orders) OR '*' IN(@Orders))	

Now when the parameter is replaced with the list of values, we get a query that is still syntactically correct and runs without error:

SELECT * 
FROM @MyTable
WHERE (OrderNo IN('12345', '54321') OR '*' IN('12345', '54321'))	

As proof, here are some screenshots of the report working as intended for single order, multi order, and wildcard entry:

Single Order

Multi Order

Wildcard

Next Steps

• Check out this tip Creating a multi-option parameter report for SQL Server Reporting Services

About the author

Kenneth Krehbiel is an avid and lifelong learner with 5 years of data analytics experience using SAS and SSRS. His focus recently has been BI with an emphasis in web development and agile solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips