CODEWITHSUNDEEP
sql-serverstored-proceduresreporting-servicesssrs-2012
Aldrin
Aldrin

Reputation: 766

SSRS multi-select parameter can't capture values with comma

I have a multi-select parameter that has comma on it's list and my dataset is using a function split for my parameter since it's in a SP so my where clause looks like this:

WHERE [CURRENTPRODATT_DIV_NAME] IN (SELECT VALUE FROM DBO.FnSplit(@ProductDivision,','))

and for example, "SMART-UPS 1,5KVA" and "BACK-UPS" were ticked in the multi-select parameter, the multi-select will treat the first value as two different values which are "SMART-UPS 1" and 5KVA". So in my Split function it will show this result:

  Row Value
   1  SMART-UPS 1
   2  5KVA
   3  BACK-UPS

And since "SMART-UPS 1" and "5KVA" were not a valid value, I will not get the records under "SMART-UPS 1,5KVA".

Can someone give ideas on how to solve this problem? Any response will be greatly appreciated.

Upvotes: 1

Views: 1315

Answers (4)

Mossi
Mossi

Reputation: 96

The answer to this one can be quite simple: Just use a different seperator! (In this case i use ";" but you could use almost any symbol, which does not appear in your LOV.

When passing the Parameters, set the Paramter Value in the Dataset Properties to

=Join(Parameters!ProductDivision.Value, ";")

In your SQL-Code then adjust the where clause to

WHERE [CURRENTPRODATT_DIV_NAME] IN (SELECT VALUE FROM DBO.FnSplit(@ProductDivision,';'))

This should work for you.

Upvotes: 1

Stan Shaw
Stan Shaw

Reputation: 3034

You should store the values in a separate table. Show the description and make the value of the list-items their respective IDs. Then, you'll never have a comma in your string parameter.

If that's not an option:

Before building your string (within your application), replace all comma values with something that you know won't appear naturally.

For example, if you've selected the following values:

 1. SPK,5
 2. Joe
 3. Dave,Smith

You can iterate through those items and replace the commas with something like ten asterisks: "**********"

So your final string would be "SPK**********5,Joe,Dave**********Smith". You would also want to pass the replacement string as a second parameter (so you can change your app without having to modify your SP).

Then, you can use the following logic:

WHERE [CURRENTPRODATT_DIV_NAME] IN (SELECT REPLACE(VALUE, @SecondParameter, ',') FROM DBO.FnSplit(@ProductDivision,','))

This way, everything will be split correctly, since you've removed the commas. Then, when you select from that list, you just replace the funny character string (in this example, ten asterisks) with the original comma.

For the record, you should 100% do what I initially suggested. This is a terrible approach, but it will get you out of a jam.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

You could pass parameters as table parameter:

CREATE TABLE #t(ID INT IDENTITY(1,1), CURRENTPRODATT_DIV_NAME VARCHAR(100));

INSERT INTO #t(CURRENTPRODATT_DIV_NAME)
VALUES ('SMART-UPS 1,5KVA'), ('BACK-UPS');


DECLARE @t TABLE (val VARCHAR(100));
INSERT INTO @t(val) VALUES ('SMART-UPS 1,5KVA'),('BACK-UPS');

SELECT *
FROM #t
WHERE CURRENTPRODATT_DIV_NAME IN (SELECT val FROM @t);

LiveDemo

Output:

╔════╦═════════════════════════╗
║ ID ║ CURRENTPRODATT_DIV_NAME ║
╠════╬═════════════════════════╣
║  1 ║ SMART-UPS 1,5KVA        ║
║  2 ║ BACK-UPS                ║
╚════╩═════════════════════════╝

Upvotes: 0

CiucaS
CiucaS

Reputation: 2128

So my guess that the string you pass is a string like param,value,param,value and you want only the values from the string.

If you know that values can't be 'PARAMETER'. You could do like this 

declare @params varchar(max) = 'PARAMETER1,AAA,PARAMETER2,BBB'

SELECT VALUE  FROM dbo.FnSplit(@params,',')
WHERE VALUE  NOT LIKE 'PARAMETER%'

The output of this query is 

----------
AAA
BBB

Edit:

In case you have parameter as a value and you know that there always will be param,value,param,value string. You could use a CTE

declare @params varchar(max) = 'PARAMETER1,AAA,PARAMETER2,BBB'; 
WITH VALUE AS
(
    SELECT VALUE, ROW_NUMBER() OVER ( ORDER BY (SELECT 1)) as RN 
    FROM dbo.FnSplit(@params,',')
)

SELECT * FROM VALUE
WHERE RN % 2 = 0

So what you do here, you split the string and save the rownumber and you also know that values will always have an even Rownumber.

Upvotes: 0

Related Questions