SSRS free Text Parameter that can accept multiple values with comma separated and with NULL as default

Question

Hi All,

I was looking for SSRS free Text Parameter that can accept multiple values with comma separated and with NULL as default. I am using the Select Statement and not Stored Procedure.

I know one option to create a function to split the comma-separated value but I am not allowed to create a function here in the source system.

The below code is working fine but would like to know how other people handle this:

DECLARE @VDespatchID VARCHAR(MAX)
SET @VDespatchID = @DespatchID+','
CREATE TABLE #ListOfDespatchID(DespatchID VARCHAR(20))
WHILE CHARINDEX(',', @VDespatchID) > 0
BEGIN
DECLARE @VDespatchIDStr VARCHAR(MAX)
SET @VDespatchIDStr = SUBSTRING(@VDespatchID, 1, (CHARINDEX(',', @VDespatchID) - 1))
INSERT INTO #ListOfDespatchID(DespatchID)
VALUES(@VDespatchIDStr)
SET @VDespatchID = SUBSTRING(@VDespatchID, CHARINDEX(',', @VDespatchID) + 1, LEN(@VDespatchID))
END

SELECT * FROM Table1 WHERE (DespatchID IN (SELECT DISTINCT DespatchID FROM #ListOfDespatchID) OR @DespatchID IS NULL)

Answer 1

Hi @Shivendoo Kumar ,
I'm not sure if you want to set a multi-value parameter in SSRS. If the parameter value comes from a string, you can split it into individual values using TSQL statements and add them to columns. Then set the parameters in ssrs by setting multi-value parameters.
For more information, please refer to: Add a multi-value parameter to a Report.
Specifically, it is divided into 2 steps:

1. Assign value to parameter
   Create a new data set 2 (a column containing the required values of the parameter), create a parameter, check the multi-value parameter, select "Get values from query" in the Available Values option, select data set 2 from the drop-down menu and select the desired parameter Column name
2. Filter by parameters
   Select data set 1, right-click the properties, and select the "Filters" option to filter data set 1.
   If I misunderstood your meaning, please feel free to correct me.
   Best Regards,
   Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.