0

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?

UpwardD
  • 739
  • 4
  • 12
  • 36
  • 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 Oct 05 '18 at 17:26
  • Available Value is set to all values from datasets and “Allow Multiple Values” is checked. Thank you – UpwardD Oct 05 '18 at 18:58
  • 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 Oct 05 '18 at 19:01
  • 2
    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 Oct 05 '18 at 22:32
  • 1
    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 Oct 06 '18 at 12:10
  • Cause it's a multiple value param, so you need to use Join function when you pass it to the Detailled report. – Amazigh.Ca Oct 11 '18 at 16:06

1 Answers1

0

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

Amazigh.Ca
  • 2,753
  • 2
  • 11
  • 8
  • Thanks, Amazigh for the reply. I did use the JOIN function against the Detailed report but the issue is the square bracket box is not tick and so the report cannot run. Am I missing something? – UpwardD Oct 11 '18 at 16:25
  • Check the default value of the parameter. is it mandatory in tour case that the parameter will be visible? generaly in the detailled report, the passed parameters from the summury are hidden. I develope too muche subreports and no parameter visible in all of them – Amazigh.Ca Oct 11 '18 at 17:03
  • The default value is set to allow “”(blank) – UpwardD Oct 11 '18 at 17:04
  • put all your subreport parameters hidden, and without default values, anaway, the parameters values are necessarily passed from sammury! – Amazigh.Ca Oct 11 '18 at 17:10