I have a SSRS report that has 3 data sets feeding off one data source. The main dataset is a stored procedure that is aggregating some data based on a set of parameters powered by the other two datasets.
The main stored procedure powering this report has 4 parameters. One is an ID for the type of data, two are for start and end dates and the third is just a flag parameter. The flag parameter is the multi-valued parameter where I want to pass multiple flag values that are also VARCHAR values.
In my stored procedure for my @Flag parameter, I have the common:
WHERE [Flag] IN (@Flag)
Then of course the @Flag parameter on the SSRS report set to allow 'Multiple Values' that are also populated from a query that is pulling those @Flag values from a dimension table.
In most cases when dealing with INT values, using the same technique works. However, when I'm dealing with character values, it fails. If I choose one flag, the report works magically. If I select more than one flag, it doesn't seem to pass the flags correctly to the stored procedure and no results return.
When testing the multi-value flag directly in the stored procedure:
WHERE [Flag] IN ('A', 'B', 'C')
The stored procedure works correctly. So the problem is not the stored procedure, but how SSRS is passing the multi-valued values to the @Flag parameter.
I tried doing the following adjustments to the SSRS dataset for this @Flag parameters:
=join(Parameters!<your param name>.Value,",")
AND this one too:
=SPLIT(JOIN(Parameters!<your param name>.Value,","),",")
These all work on single values, but never multi-values.
What am I missing here?