时间戳

首页 > 解决方案 > SSRS 多值参数

sql - SSRS 多值参数

问题描述

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', ','))

这是 SPLIT() 函数

   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

我有一个参数@Value，并将其在数据集中的值设置为 =Join(Parameters!Value.Value,",")。

只要用户选择一个 Value ，它就可以工作，但只要选择多个 Value 就根本不起作用。它运行，但不显示任何数据。例如，如果我选择“值 1”，它会显示“值 1”信息。但是，如果我选择“值 1”和“值 2”，则什么都不会显示。

任何想法为什么这不起作用？非常感谢

标签： sqlreporting-services

解决方案

除非我错过了什么......

根据默认参数值的建议，您正在将逗号分隔的值列表传递给存储的过程

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

然后检查参数值是“1”还是“2”，但如果传入“1,2”，它永远不会只是“1”或“2”。

您需要拆分传入的字符串，然后检查结果。顺便说一句，您可能可以使用内置string_split()功能。

所以像这样。

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

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

然后您的测试将从更改IF @Value='1' --ListA为

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

并重复2

推荐阅读