SSRS MultiValue Single Select Gives 'Index was outside the bounds" error, but MultiSelect Does Not

Question

Have an issue I am trying to solve and would very much appreciate your help. I have a multivalue report parameter in an SSRS 2008 report I created. When I select more than one value from the dropdown list, the report runs fine. However, when I only select one value in the dropdown list I get a "The value expression for query parameter @XXXXXX contains and error: Index was outside the bounds of the array.

I know it must be something I am overlooking, being new to SSRS reports and multivalue parameters, or others would have seen the same thing. I looked through the other postings here, but wasn't able to find the same issue.

Here is ReportParameter I am defining:

  <ReportParameter Name="reader">
  <DataType>String</DataType>
  <DefaultValue>
    <DataSetReference>
      <DataSetName>dsReaders</DataSetName>
      <ValueField>ReaderName</ValueField>
    </DataSetReference>
  </DefaultValue>
  <Prompt>Readers</Prompt>
  <ValidValues>
    <DataSetReference>
      <DataSetName>dsReaders</DataSetName>
      <ValueField>ReaderName</ValueField>
      <LabelField>ReaderName</LabelField>
    </DataSetReference>
  </ValidValues>
  <MultiValue>true</MultiValue>
</ReportParameter>

Here is the QueryParameter that uses it:

                <QueryParameter Name="@ReaderName">
                <Value>
                  =IIF(Parameters!reader.Count = 1, Parameters!reader.Value, ("'" + Replace(Replace(Join(Parameters!reader.Value, "~"), "'", "'''"), "~" ,"','") + "'"))
</Value>
                <rd:UserDefined>true</rd:UserDefined>
              </QueryParameter>

Another thing I can mention is that I was using SSRS 2008 RTM and it always gave me the error on the value parameter in this query parameter. After upgrading to SQL 2008 SP3, now it varies on the value parameter for other query parameters I have defined. Seems odd, but I know it is telling me something.

Is this a known bug in SSRS 2008 and/or SQL 2008 (non-R2)? Or something I might be missing in my report definition.

Answer 1

Try Value(0) on the true part of the IIF().

Answer 2

I've had exactly same issue. I did a workaround by joining all parameters values into one string and then checked if the value was there like this:

=iif(Array.IndexOf(Split(Join(Parameters!Detail.Value, ",").ToString(), ","), "0")>=0, "paramater with 0 value is checked", "parameter with 0 value is not checked")

If array.indexof returns -1 then your value is not in the list, otherwise it returns the position of the desired value.