I have a report that is has a multiple value parameter. It has available values (1, 2, 3, 4, 5, and blank). The blank value isn't NULL it is just an empty cell.
For testing purposes, there should only be one record showing up. The record has a blank value in the cell that I am using to filter on in my stored procedure.
In the parameter drop down, I see all of the options. If I choose the (Select All) option, I don't get the record I am wanting. However, if I choose just the blank value, I get the record I am wanting. I updated the record manually to "2". I choose (Select All) and I get the record. I choose only the "2" value and I get the record. If I remove the value again to make the value blank, I only get the record if I only choose the "Blank" option in the drop down.
I make a text box to output the value of the parameter and it is showing blank when it is selected either by itself or with other values.
What am I missing in either my stored procedure or in my report? Thanks in advance.
When you have a multiple select parameter, SSRS should send a comma separated list of values to your stored procedure. You are responsible for splitting these back out into something that you can join on.
In the report itself, you don't get the list of values in a nicely wrapped up string for displaying. I've had to use code to iterate over the values in the parameter.
I tested what SSRS is doing when you have a blank available value. I created a test report that had the six available options and then a stored procedure to force the selected values to be output:
CREATE PROCEDURE dbo.Test_RPT
(
@TestMultiSelect varchar(1000)
)
AS
SELECT @TestMultiSelect RVAL
In the report, I just had a single textbox that displayed this RVAL field.
If I put the blank option at the start, the output was 1,2,3,4,5. If the blank option was at any other location, it was included: 1,2,3,,4,5, 1,2,3,4,5,.