vendredi 23 janvier 2015

How to pass multi-valued characters in SSRS Report?


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?





Aucun commentaire:

Enregistrer un commentaire