Sql-server – How to pass multi-valued characters in SSRS Report

sql serverssrs

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.

My Problem

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.

Solutions Tried

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?

Best Answer

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()...

Related Question