SSRS - Multi Value Parameter Report Problem-CodePudding

I have a quite strange / unusual request for an SSRS report. The client wants to be able to paste in large lists of ID numbers from an Excel sheet column (normally < 100 values but can be as many as 20,000 values) as a search parameter in the report.

Now, I know how to pass multi value parameters from SSRS to a stored proc etc, that's not the issue. The issue is with this requirement to literally paste in a list of IDs into the multi value parameter input box and then limit the dataset based on that list (rather than pre-populate the Multi Value parameter with a list of values based on a query / SP as you normally would)

My question is what would be the best method / approach to this problem as I have never had a similar ask in many years of SSIS development? I need to make the solution as "self-service" as possible too, so as easy as running an SSRS report from report manager ideally. I know I could just import the Excel data into a table in the database and join to that etc but ideally would like something the user can run without the need for any tech input to import data or run SQL through SSMS to get the datset.

CodePudding user response：

When you copy/paste from excel its just a tab delimited string. You can configure a string parameter to allow multiple values, and then in the expression editor, split the string by tab values or by line breaks.

Here's how:

In the parameter properties check the 'Allow multiple values' checkbox on the general tab
Then in the datasource properties click the 'fx' (expression) button next to the parameter on the parameters tab
In the expression editor that appears, type the following:

=replace(split(Parameters!MyParameter.Value,vbCrLf),"\x0009","")

This will split the string by line breaks, then strip the tab characters out. After that you can treat it like any multi-valued parameter.

CodePudding user response：

If you have a typical multi-value parameter setup then you can just copy/paste the column from excel directly in, it will automatically put each cell row copied from excel on a new line.

So if your query looked something like

SELECT * FROM myTable WHERE EmployeeName IN(@empName)

and empName was you report parameter, if no available values are configured you'll get an empty list when you click in the parameter field, just copy paste direct from Excel and it will work.

I'm not sure if there would be any limits or how good performance would be especially if copying thousand of values but certainly for a reasonably small number of items this will work.

The only other way I can think of that means no real extra work for the user would be to have them drop the workbook into a specified folder (maybe with a subfolder based on their SSRS username, then use openrowset to read the contents either directly into a dataset or better still, into a permanent table with their username and the parameter value on each row.

The openrowset statement could sit at the top of your main dataset query

Then your query could do something simple like

DELETE FROM myParamValueTable WHERE UserName = @UserID
INSERT INTO myParamValueTable
SELECT * FROM (OPENROWSET .....) xl

SELECT * FROM myTable t 
    JOIN myParamValueTable p 
    ON t.EmployeeName = p.EmployeeName
    WHERE p.UserName = @UserID