none
SSRS 2008 R2 multivalue parameters ignores blank value

    General discussion

  • I found an annoying bug in my report today.  My Stored procedure was returning larger counts than my report.  And I found the issue and the workaround, and felt I should share it with you.  I have a multivalued parameter which is a set of varchars.  One of the valid strings is blank.  When I selected all, including the blank, which shows first, It did not include the conts for blank.  When I select only blank, i get those counts.  They matched the difference between my tested SP and my report!  So much for the journey.  Here is my workaround:

    in the dataset parameter set expression to:

    =Join(Parameters!ProgramList.Value,",") & "," & Parameters!ProgramList.Value(0)

    It works!  Somehow the ,a,b,c fails whereas the ,a,b,c,  succeeds.  and since there must always be at least one value you are simply putting it into your list twice.  So it works even when blank is not selected.

    I think a lot of anguish is being caused by the issues with MVPs and I hope they get cleaner in the next release.  I also use my own (well, from the web with revisions) function to display the selections made.

    Add this to report/report properties/code 


    Function ArrToStr(mvp as parameter) as String
      dim str as String = ""
      dim x as Integer
      dim delim as String = ""
    For x = 0 To mvp.count -1
                str = str & delim & mvp.Value(x).trim()
    delim = ", "
    Next
    Return str
    End FunctionEnd Function


    End Function

    Wednesday, July 24, 2013 3:49 PM

All replies