Issues with Passing Multi-value Parameters to a Drill through Report

0
votes

I have two reports were I pass multi-valued parameters to it's underlining data and both reports work very well independently. The parameter strings are being split using the function dbo.UTILfn_Split. When trying to drill from the main or Summary report into the sub or Detailed report all other parameters field in the report are populated except the multivalued parameter field. The parameter lists or value are listed in the detailed report but not selected and therefore cannot run the report even though the detailed report parameter property is set to allow multiple values. In both reports, the where clause is set "IN" not "=." How do I fix this?

1
reporting-servicesssrs-2008ssrs-2012ssrs-2008-r2ssrs-tablix
To clarify, in your parameter properties is Available Values set to "None" and "Allow multiple values" is unchecked? You are entering a single string of comma separated values? – StevenWhite
Available Value is set to all values from datasets and “Allow Multiple Values” is checked. Thank you – UpwardD
Strings are coming ID field and the idea is to be able to check and uncheck values that u want or don’t want. – UpwardD
OK, then you shouldn't need a Split function at all. Anyway, have you added the parameter to your subreport properties? If so, how are those properties set? If you're trying to Join/Split values, that may be causing the issue. – StevenWhite
Why should it be neccessary to pass multiple values for a single parameter to a drillthrough report? For example, if you want to drillthrough to the OrderDetails of an Order, you will pass the OrderID instead of passing the list of OrderDetailsIDs. – Wolfgang Kais

1 Answers

0
votes

In your Summary Report, when you pass the parameter to the sub or detailled report, the passed value parameter should be like this expression: 

=join(parameters!yourMultivaluedParameter.Value,",")

after that, you pass the name of the parameter to the corresponding parameter in the dataset Detailled report. In your SQL (SP), get the multivalues of the parameter by spliting it with your function like following, depending of the result of your function, for exemple: 

INNER JOIN dbo.SplitFunction( @yourMultivaluedParameter,',')  tmp on tmp.yourColumn = ...etc...

Hope it helps...