SSRS 2013 Shell passing multi value string parameter

Do you have a split function in your database? It will be a user defined function.

You need to use the split function.

Hi,

Like this:
=SPLIT(JOIN(Parameters!<your param name>.Value,","),",")

is this for ALL like:
Dataset name expression
Drillin report from main report:
Text Box Properties
Drillin Report:
Dataset name expression.

Ok, will do thanks

Hi,

Ok works when user selects 1 severity.  But when user selects 2 then I get error msg:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'Totals'. (rsErrorExecutingCommand)
Must declare the scalar variable "@SEVERITY".

Please help and thanks

what is the code for your stored procedure

also, what is result of

SELECT * FROM dflt.f_Delimited_Split_to_Table_LN('A,B,C',',')

Select allOpen in new window

when you run this in SSMS

Hi,

In the manual it is mentioned:
In the declarations section, add these lines:
declare @SEV as table (severity varchar(50))
insert into @SEV select [Data] FROM dflt.f_Delimited_Split_to_Table_LN(@DB_SEVERITY,',')
--where @DB_SEVERITY is the severity parameter passed into the SP.

In the join section, add this line:
inner join  @SEV as sev on sev.severity = vn.Severity


There is no Where statement associated with @DB_SEVERITY, is this correct?  Should there be a where statement like:
Where vn.Severty In @DB_SEVERITY

On the select question:
Sorry kind of new to this but how do I run that section?
If I try setting the @SEVERITY parm like:
@SEVERITY Varchar(255) IN ('Critical', 'High') there is a syntax on IN
Testing with just 1 selection is easy:
@SEVERITY Varchar(255) = 'Critical'

Please help and thanks

Hi,

If I change all to:
=SPLIT(Parameters!SEVERITY.Value, ",")

I get error:
An error has occurred during report processing. (rsProcessingAborted)
The Value expression for the query parameter ‘@SEVERITY’ contains an error: Conversion from type 'Object()' to type 'String' is not valid. (rsRuntimeErrorInExpression)


Please help and thanks

Hi,

Also I should mention that this (see image below) works for the Main Report.  The problem is somewhere in the Drillin process, something is missing.

On all Main reports (that do not have a drillin(Child report)) where I have done this severity process it works fine.  But now I am dealing with a drillin and this is where I am having issues.  It is either how I am passing from Main - Drillin Or the Drillin report is not taking in the parm correctly or both.  
But for testing the drillin SP I need some correct syntax on:  @SEVERITY IN ('Critical', 'High') or even Set @DB_SEVERITY IN ('Critical', 'High'). these both give me syntax errors on IN..

Please help and thanks

Hi,

Ok so that is already done:
Delimited_Split_to_Table_LN(@DB_SEVERITY,')

And that puts me back to the same issue.. of using the Joins in the Main to Drillin report Or the Drillin report on the way it takes the parm...Or both

Please help and thanks...

Hi,

This link says to use the SPLIT in the report:
https://dataqueen.unlimitedviz.com/2013/10/how-to-use-a-delimited-string-in-a-multi-valued-parameter/

But anyways like I mentioned the main report works fine, when using the Join.  I am having issues with the drillin. on the drillin if I pass 1 parm it works fine, but not when passing 2 or more...

Please help and thanks...

oh, maybe I should restate the last post.  

The issue is when I am passing more then 1 value within @SEVERIY like: 'Critical', 'High' from the dropdown on the main report to the drillin report.  Like mentioned it works fine on the Main report.

Please help and thanks

Ok,

So far I have the drillin working at the totals levels.  but not the severity level.

I currently have:
=Split(Join(Parameters!SEVERITY.Value, ","),",")
in all levels.  This means that this Multi-Value parameters works for the totals level but not for the severity level.
also in the header of the drillin I have:
=Join(Parameters!SEVERITY.Value, ",")
which is good for the totals because more then 1 value is being sent.  but when there is only 1 value on the main report I still get more then one value.  I noticed that this is pulling from the Parameters so I tried Fields and I received error..

Please help and thanks

Actually this works fine now I had to do multiple scripts and all depends on the testing on which one works:
=Join(Parameters!SEVERITY.Value, ",") <== this works maybe for a count on Total level
=Split(Join(Parameters!SEVERITY.Value, ","),",")  <=== this works for a grand Total Total level
=Parameters!SEVERITY.Value <== this works sometimes???
=Fields!severity.Value <=== this works for a SEVERITY Level (single level)