8

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?

7

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 case this helps anyone else: the join expression (=join(Parameters!<your param name>.Value,",")) can be added to the dataset reading the parameter, under the "Parameters" tab: there is an "fx" button for adding expressions, to the right of the "Parameter Value" drop-down. – Doug_Ivison Aug 29 '17 at 22:35
  • Also, instead of creating a UDF to parse, if the database value and parameter values are reliably unique and never subsets of each other, then I skip parsing and just use CHARINDEX: for example, if the code to read the parameter in embedded SQL was: WHERE <Table>.[<Column>] IN (@MultiValueParm), then the code in the stored procedure could be WHERE CHARINDEX(<Table>.[<Column>], @MultiValueParmAsText) > 0 – Doug_Ivison Aug 30 '17 at 18:10
  • Great Q&A, thanks! FYI, SQLFool.com appears to be down. The ParseString() function can be found at: github.com/MichelleUfford/sql-scripts/blob/master/dev/… – J Weezy Jan 19 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.