I found a solution. I did not properly split the values in the stored procedure with a UDF.
To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using:
=join(Parameters!<your param name>.Value,",")
This is basically going to join multiple values into an array and pass it through the @Flag
parameter. The next step is adding SQL to the stored procedure to receive and digest the values correctly so it reads the values with the IN
clause.
Google search any UDF string parser online. There are many to choose from. I used dba_parseString_udf
from Michelle Ufford http://sqlfool.com.
Once I had my UDF installed, I can now alter my IN
clause to receive the new multi-valued parameter being passed by SSRS as follows:
WHERE [Flag] IN (SELECT * FROM dba_parseString_udf(@Flag, ','))
Therefore, SSRS will pass the following value:
@Flag = 'A,B,C'
Then my UDF will parse that string out correctly to:
A
B
C
And populate my @Flag
parameter correctly with SELECT * FROM UDF()...
In the screenshots you're setting the expression for the Default values which will only change the values on the initial display of the report. This will not do anything to keep a user from changing those values, so you can easily get a render request with all parameters populated with something other than the default.
I'd suggest making it clear to your report users what is happening with these dates, don't do magic with their parameters behind the scenes. To do this, create an option in each parameter set (you can do something like UNION and hardcode the label and key) to ensure that report execution not only does what you expect, but makes this is clear to any other developer or user.
The user might select something like:
Param1 - "Not Used"
Param2 - 20151202
Param3 - "Not Used"
You can force this behavior with cascading parameters. You can run into issues with updates to cascading parameters though.
Setup like this:
@Param1
SELECT -1 As Key, 'Not Used' As Label
UNION
SELECT Key, Label FROM DATES
@Param2
SELECT -1 As Key, 'Not Used' As Label
UNION
SELECT Key, Label FROM DATES
WHERE -1 = @Param1
@Param3
SELECT -1 As Key, 'Not Used' As Label
UNION
SELECT Key, Label FROM DATES
WHERE -1 = @Param1
AND -1 = @Param2
This ensures that you can only select the "Not Used" option for param2 if param1 selected anything except "Not Used". You can still set the default to be not used for all of these.
Best Answer
You're running into this problem because of the difference in how SSRS passes multi-value parameters when using a text query vs. using a stored procedure for your dataset.
When using a text query, the parameter is actually replaced with a literal list of strings before executing the query, e.g.
PDPOID IN (@PDPOID)
is translated toPDPOID IN ('aaa', 'bbb', 'ccc')
(assuming you've selected 'aaa', 'bbb', and 'ccc').For a stored procedure, the selected values are turned into a comma-separated list, and passed as a single string/character parameter. So if your stored procedure has a parameter named
@PDPOID
, then the parameter would be set to the single value 'aaa,bbb,ccc'.The easiest way to make this work is to change your report to use a stored procedure for the query, then use the
STRING_SPLIT
function (or a suitable homebrew string splitting function if you're running a version older than SQL Server 2016). For example: