sqlreporting-servicesparameterspasting

Need users to insert values in a Reporting Services Report

I need users to be able to paste a list of values (all for one column) into a reporting services report.

This list will then be inserted into a temp table and that temp table left joined to my query.

The resultset I am looking for is a count of NULLs to indicate how many of those values (they're IDs) are missing in the database and how many are present. From there, it will also be returning the details of the available IDs.

I can do the second part, but am struggling to be able to enter a list (say, 50 rows) into a temp table via Reporting Services.

The end user does not have access to SQL and will preferably need to do little adjustment to their values (adding brackets etc.) before pasting into the Report Parameter.

Any ideas?

Solution

    1. Create a table valued function that takes two parameters a varchar(max) string and a delimiter. In the function parse the string on the supplied delimiter into rows. 

      create FUNCTION [dbo].[ufn_MakeTableFromLIst]
(
@LIST varchar(max), 
@DELIMITER char(1)= ','
)

RETURNS @RETURN_TABLE TABLE (Param varchar(4000))

AS 
BEGIN

Declare @POS int,
@PIECE varchar(4000)

Set @LIST = ltrim(rtrim(@LIST)) + @DELIMITER
Set @POS = charindex(@DELIMITER, @LIST, 1)

-- parse the string into a table
if REPLACE(@LIST, @DELIMITER, '') <> ''
begin

WHILE @POS > 0
begin

SET @PIECE = LTRIM(RTRIM(LEFT(@LIST, @POS - 1)))
IF @PIECE <> ''
    begin

    INSERT INTO @RETURN_TABLE (param) VALUES (CAST(@PIECE AS varchar(4000)))

    end

SET @LIST = RIGHT(@LIST, LEN(@LIST) - @POS)
SET @POS = CHARINDEX(@DELIMITER, @LIST, 1)

END
End
RETURN

    2. Create two parameters in you report to accept a delimited string and the delimiter. The client will then be able to supply you with a format that they have available as long as they have a consistent delimiter in the string.

    3. Do your left or right join and count up your nulls.