Using Multi-Value Parameter not working after multiple edits
-
I've now read 15 different articles and it either doesn't work or get various errors.
My report using 1 stored procedure to pull the data. I've used this code in the SP as the where:
d.Phase IN(@Phase)
In my SSRS report, the dataset for the sp is setup as normal and I created a second dataset to show the values to choose from (Phases).
The Parameters for the report are pulled automatically from the stored procedure. I simply went to the @Phase one and change it to allow multi-value and changed the available values to be the second dataset and all works well for single value.
From what I read I need to use the join command on the parameter in the main dataset (ie not the dataset to display the values for the parameter) but that yields some other error.
I've always used Crystal Reports and have never had to do extra code or changes because it just works.
There's articles about creating functions, some say join.
-
You can't pass a comma-delimited variable to an IN() clause.
You need to modify the stored procedure to convert the comma delimited list of phases to a table, & join your table either directly to that table-valued function, or insert phases into a temp table & join to that.
If you're on SQL Server 2017+, you can use STRING_SPLIT. Otherwise, I'd recommend Jeff Moden's DelimitedSplit8K function discussed in https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function.
-
Our SQL is hosted and still only on 2016 so have no control over the version. I already have a separate DataSet created that is the source of the phases (which is a query from a table) so do I join to my dataset?
What I don't understand is SAP Crystal does this functionality without any new code. I can also write this into a query with multiple values in commas and it works in SSMS so for the life of me how no idea why SSRS can't understand this basic need ie allowing multiple values into a single parameter.
d.Phase IN(' 03100.',' 03300.',' 03200.',' 09100.',' 15100.',' 16100.') - works find in SSMS
-
STRING_SPLIT actually was introduced in SQL 2016.
It's not an SSRS problem -- SSRS does pass multiple values in a single parameter. If you run SQL Server profiler, you can observe that.
SQL Server IN clause cannot interpret a parameter or variable w/ a comma-delimited string of multiple values as multiple values -- it interprets it as one value because there is only one string. It works in SSMS because you are not trying to parameterize the IN() clause.
Prove it to yourself:
DECLARE @phases VARCHAR(255) = ' 03100., 03300., 03200., 09100., 15100., 16100.';
SELECT @phases;
CREATE TABLE #phases
(phase CHAR(7) NOT NULL PRIMARY KEY);
INSERT INTO #phases
(phase)
VALUES (' 03100.'),(' 03300.'),(' 03200.');
SELECT * FROM #phases
WHERE phase IN (@phases)
SELECT #phases.* FROM #phases
INNER JOIN STRING_SPLIT(@phases,',') phaseList ON phaseLIst.value = #phases.phase;
-- But a single string value parameter works because it actually is a value in the table:
DECLARE @phases VARCHAR(255) = ' 03100.';
SELECT * FROM #phases
WHERE phase IN (@phases)Side note: Do your phases really have leading spaces? Trailing periods?
-
Yes they have leading and trailing spaces and it's literally in every single table of the database. It's many of the key fields too. I think I'll stick with Crystal Reports for now.
-
Kept googling and googling and found this and it works. https://www.youtube.com/watch?v=htpoAyuWw5o
-
I'm glad that video better clarified what I tried to demonstrate in my example, and that you now have a working solution.
-
Yes and I do thank you. The STRING_SPLIT pointed me in the right direction.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply