SQL Server Reporting Services Multi-Value Parameter - Wildcard Usage


By:   |   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.

sample order report
part number
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 (*).

order number
sample order report
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:

report parameter properties

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:

order number
orders

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

sample order report

Multi Order

order number

Wildcard

part number
Next Steps


Last Updated: 2018-06-14


get scripts

next tip button



About the author
MSSQLTips author Kenneth Krehbiel 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.

View all my tips



Comments For This Article




Monday, June 25, 2018 - 1:46:06 PM - Kenneth Krehbiel Back To Top (76385)

Hi Dmitry,

I'm glad you found the tip useful. As to your question, I'm not sure exactly what SSRS passes to that parameter. You are correct that the same code will not work in SSMS, but I can confirm that it will work in SSRS. You could make a small modification to the code and use a Table Valued Parameter as you suggested. Then it should work fine (see below). From what I can tell though, SSRS passes a list of strings that are comma separted - rather than a single string that contains a comma separated list.

I hope this helps!

-Ken

DECLARE @Orders AS TABLE (OrderNo VARCHAR(5))

INSERT INTO @Orders VALUES ('12345'),('54321');

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')

SELECT * FROM @MyTable

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


Monday, June 25, 2018 - 12:13:38 PM - Dmitry Vakhrushev Back To Top (76383)

Kenneth, thank you for sharing this! When I select multiple orders in SSRS the @Orders parameter is in fact substituted with a comma separated list of orders. It's actually a STRING which you then pass to the IN(@Orders) part that actually expects a table. I couldn't make to work the code below. Could you explain how it worked in your solution? Thanks!

 ------------------------------------------------------------

DECLARE @Orders AS NVARCHAR(50) = '12345,54321';

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')

 

SELECT * FROM @MyTable

WHERE (OrderNo IN(@Orders) OR '*' IN(@Orders))

 ------------------------------------------------------------



download





Recommended Reading

Working With Multi-Select Parameters for SSRS Reports

SQL Server Reporting Services Using Multi-value Parameters

Add a Date Range Dataset in SQL Server Reporting Services

Multi-detail reports using sub reports in SQL Server Reporting Services

Multiple Row Grouping Levels in SSRS Report














get free sql tips
agree to terms