SSRS Multi-valued parameter

Question

ALTER PROC [dbo].[spIVDiscre]

@Value  varchar(10) = '1,2'
as

BEGIN
SELECT 
'0' as Value ,

pn.No_,
[Address Code]                 = pn.[Address Code],
[Contact]                      = pn.[Phone No_],
[Partner Name]                 = pn.[Name],
[Partner Name 2]               = pn.[Name 2],
[PledgeType] = pdl.[PledgeType],
[Address]            = pn.[Address]


INTO #Temp110
FROM dbo.[Product Line] pdl  WITH (NOLOCK) 
INNER JOIN [Pledges] pl   WITH (NOLOCK) 
ON pdl.[Pledge ID]=pl.ID
LEFT JOIN [dbo].[Contact] pn with (NOLOCK)
on pdl.[No_] = pn.No_
LEFT join [dbo].[Country_Region] co WITH (NOLOCK)  
on pn.[Country_Region Code] = co.Code
LEFT JOIN 
(
    SELECT 
    
    cast(Value as int) as Code,
    Value2 as Description
    FROM Config WITH (NOLOCK) 
    WHERE 
    Category='Children'
    AND SubCategory='Status'
    AND DATEDIFF(dd,GETDATE(),ISNULL(ObsoleteDate,DATEADD(dd,1,GETDATE())))>0
) childstatus
on c.StatusID = childstatus.Code


END





--------------------------------------- @ListA------------------------------------------



IF @Value='1' --ListA
BEGIN
INSERT INTO #Temp110
SELECT 
--iList = CAST (@iList as int ) ,
@Value as Value ,
pn.No_,
[Address Code]                 = pn.[Address Code],
[Contact]                      = pn.[Phone No_],
[Partner Name]                 = pn.[Name],
[Partner Name 2]               = pn.[Name 2],
[PledgeType] = pdl.[PledgeType],
[Address]            = pn.[Address]

   
FROM dbo.[Product Line] pdl  WITH (NOLOCK) 
INNER JOIN [Pledges] pl   WITH (NOLOCK) 
ON pdl.[Pledge ID]=pl.ID
LEFT JOIN [dbo].[Contact] pn with (NOLOCK)
on pdl.[No_] = pn.No_
LEFT join [dbo].[Country_Region] co WITH (NOLOCK)  
on pn.[Country_Region Code] = co.Code
LEFT JOIN 
(
    SELECT 
    
    cast(Value as int) as Code,
    Value2 as Description
    FROM Config WITH (NOLOCK) 
    WHERE 
    Category='Children'
    AND SubCategory='Status'
    AND DATEDIFF(dd,GETDATE(),ISNULL(ObsoleteDate,DATEADD(dd,1,GETDATE())))>0
) childstatus
on c.StatusID = childstatus.Code

WHERE  
(pdl.[Pledge Status] = '2' and c.StatusID ='4')
or (pdl.[Pledge Status] = '2' and c.StatusID ='7')  

END


-------------ListB---------------------

IF @Value='2' --ListB
BEGIN
INSERT INTO #Temp110
SELECT 
--iList = CAST (@iList as int ) ,
@Value as Value ,
pn.No_,
[Address Code]                 = pn.[Address Code],
[Contact]                      = pn.[Phone No_],
[Partner Name]                 = pn.[Name],
[Partner Name 2]               = pn.[Name 2],
[PledgeType] = pdl.[PledgeType],

[Address]            = pn.[Address]
  
FROM dbo.[Product Line] pdl  WITH (NOLOCK) 
INNER JOIN [Pledges] pl   WITH (NOLOCK) 
ON pdl.[Pledge ID]=pl.ID
LEFT JOIN [dbo].[Contact] pn with (NOLOCK)
on pdl.[No_] = pn.No_
LEFT join [dbo].[Country_Region] co WITH (NOLOCK)  
on pn.[Country_Region Code] = co.Code
LEFT JOIN 
(
    SELECT 
    
    cast(Value as int) as Code,
    Value as Description
    FROM Config WITH (NOLOCK) 
    WHERE 
    Category='Children'
    AND SubCategory='Status'
    AND DATEDIFF(dd,GETDATE(),ISNULL(ObsoleteDate,DATEADD(dd,1,GETDATE())))>0
) childstatus
on c.StatusID = childstatus.Code

WHERE  
(pdl.[Pledge Status] = '2' and c.StatusID ='3')
or (pdl.[Pledge Status] = '2' and c.StatusID ='6')  

END





select * from #Temp110 
where Value in (select Value2 from [fnDiscre] ('1,2,3', ','))

this is SPLIT() function

   Alter FUNCTION [dbo].[fnIVDiscre]
   ( @string varchar(4000))
Returns
@Result Table (Value varchar(100))
As
Begin
declare @len int, @loc int = 1
While @loc <= len(@string) 
Begin
    Set @len = CHARINDEX(',', @string, @loc) - @loc
    If @Len < 0 Set @Len = len(@string)
    Insert Into @Result Values (SUBSTRING(@string,@loc,@len))
    Set @loc = @loc + @len + 1
End
Return
End

I have a parameter @Value and set its value in the Dataset to =Join(Parameters!Value.Value,",").

it works whenever the user selects one Value , but doesn't work at all whenever multiple Value are selected. It runs, but it doesn't display any data. For example, if I select 'Value 1', it shows 'Value 1' info. But if I select 'Value 1' and 'Value 2 ', nothing shows up.

any ideas why this isn't working? thank so much

What version of SQL are you using? Is it safe to assume that is is SQL Server? On a side note, please look into with (nolock) statement on your joins.. have a look at this answer : - https://stackoverflow.com/questions/46902892/string-split-in-sql-server-2012 — Harry, Sep 29 '20 at 03:53
Only use NOLOCK, if you do NOT care about the accuracy of your result. i.e don't use it. — Mitch Wheat, Sep 29 '20 at 04:29
if I understand your question correctly. maybe this link may help you. https://www.mssqltips.com/sqlservertip/2866/sql-server-reporting-services-using-multivalue-parameters/ — Chanom First, Sep 29 '20 at 08:03
i think need to split the parameter for return but when i EXEC [SP] no data display. — ericso, Sep 29 '20 at 08:53

score 0 · Accepted Answer · answered Sep 29 '20 at 12:42

Unless I'm missing something....

You are passing a comma separated list of values to the stored proc, as suggested by you default parameter value

@Value  varchar(10) = '1,2'

You then check to see if the parameter value is either '1' or '2' but if you pass in '1,2' it never will be just '1' or just '2'.

You need to split the passed in string and then check the results of that. Incidentally you could probably use the built in string_split() function.

So something like this.

DECLARE @Value  varchar(10) = '1,2'

DECLARE @ParamValues TABLE (pValue varchar(10))
INSERT INTO @ParamValues
    SELECT * FROM string_split(@value, ',')

Your test would then change from IF @Value='1' --ListA to

IF EXISTS(SELECT * FROM @ParamValues WHERE pValue = 1) -- ListA
    BEGIN
        ... your exsiting code block
    END

and repeat for 2

hello Alan. thanks for your help. it is very useful for me – ericso Sep 30 '20 at 08:23 — ericso, Sep 30 '20 at 08:23

SSRS Multi-valued parameter

1 Answers1