SSRS 中的多值参数不适用于全选：非布尔类型的表达式指定了需要条件的上下文，靠近“，”；

Question

如果您选择单个分支，则报告运行正常，但当您选择多个分支或“全选”时，报告会中断。

所以这是我添加到 SSRS 的查询，我确定问题出在 @Branch，但我不太确定在哪里，我正在运行 3 个与此类似的查询，但所有 3 个查询都中断了，所以我只需要其中一项的帮助即可完成其余的工作。

--Declare @ReportDate as Date = '2022-02-22'
--Declare @Capturer as varchar(50) = 'TestName'
--Declare @Branch as varchar(50) = 'TestBranch'

DROP TABLE IF EXISTS #PIA1;


SELECT CONVERT(char(10), CAST(GETDATE() AS date), 126) AS Date,
       CASE WHEN LEN(bc.Code) = 3 THEN '0' + bc.Code ELSE bc.Code END AS Code,
       '30200' AS Account,
       'E' AS VatCode,
       CASE
            WHEN cde.data20 LIKE 'SBV%'
             AND amount NOT LIKE '%.00' THEN 'Bulk Deposit Coins' + ' ' + CONVERT(varchar, Data55, 103)
            ELSE 'Bulk Deposit Unfit' + ' ' + CONVERT(varchar, Data55, 103)
       END AS Description,
       '' AS TransCode,
       'D' AS DC,
       cda.Amount AS Amount,
       'GL Movements' AS Category,
       'Bulk Banking To SBV' AS Reason,
       'FCS' + ' ' + bc.Branch AS [Client Name],
       @Capturer AS Capturer
INTO #PIA1
FROM comparabledata cda (NOLOCK)
     JOIN comparabledetail cde (NOLOCK) ON cda.ComparableDetailId = cde.Id
     JOIN Custom_Test_TestCodes bc (NOLOCK) ON cde.data1 = bc.Abbreviation
WHERE ComparisonAccountId = 1
  AND data55 = @ReportDate
  AND bc.Branch IN (@Branch)
  AND data20 LIKE 'SBV%'
  AND data2 LIKE 'Ned_Clearance%'
  AND iscancelled = 0
  AND IsDeleted = 0;


DROP TABLE IF EXISTS #PIA2;
SELECT CONVERT(char(10), CAST(GETDATE() AS date), 126) AS Date,
       CASE WHEN LEN(bc.Code) = 3 THEN '0' + bc.Code ELSE bc.Code END AS Code,
       '30100' AS Account,
       'E' AS VatCode,
       CASE
            WHEN cde.data20 LIKE 'SBV%'
             AND amount NOT LIKE '%.00' THEN 'Bulk Deposit Coins' + ' ' + CONVERT(varchar, Data55, 103)
            ELSE 'Bulk Deposit Unfit' + ' ' + CONVERT(varchar, Data55, 103)
       END AS Description,
       '' AS TransCode,
       'C' AS DC,
       cda.Amount AS Amount,
       'GL Movements' AS Category,
       'Bulk Banking To SBV' AS Reason,
       'FCS' + ' ' + bc.Branch AS [Client Name],
       @Capturer AS Capturer
INTO #PIA2
FROM comparabledata cda (NOLOCK)
     JOIN comparabledetail cde (NOLOCK) ON cda.ComparableDetailId = cde.Id
     JOIN Custom_Test_TestCodes bc (NOLOCK) ON cde.data1 = bc.Abbreviation
WHERE ComparisonAccountId = 1
  AND data55 = @ReportDate
  AND bc.Branch IN (@Branch)
  AND data20 LIKE 'SBV%'
  AND data2 LIKE 'Ned_Clearance%'
  AND iscancelled = 0
  AND IsDeleted = 0;

--*******************************************************************************************---
DROP TABLE IF EXISTS #FC2;
SELECT SUM(cda.Amount) * -1 AS B
INTO #FC2
FROM ComparableData cda (NOLOCK)
     JOIN ComparisonAccount ca (NOLOCK) ON cda.ComparisonAccountId = ca.Id
     JOIN ComparableDetail cde (NOLOCK) ON cda.ComparableDetailId = cde.id
     JOIN Custom_Test_TestCodes bc (NOLOCK) ON cde.Data1 = bc.Abbreviation
WHERE ComparisonAccountId = 1 --ISA
  AND Data55 = @ReportDate
  AND Data27 LIKE 'Withdrawal%'
  AND bc.Branch IN (@Branch)
  AND IsDeleted = 0
  AND IsCancelled = 0
  AND Data2 LIKE 'NED_Clearance%';




DROP TABLE IF EXISTS #FC1;
SELECT SUM(cda.Amount) AS A
INTO #FC1
FROM ComparableData cda (NOLOCK)
     JOIN ComparisonAccount ca (NOLOCK) ON cda.ComparisonAccountId = ca.Id
     JOIN ComparableDetail cde (NOLOCK) ON cda.ComparableDetailId = cde.id
     JOIN Custom_Test_TestCodes bc (NOLOCK) ON cde.Data1 = bc.Abbreviation
WHERE ComparisonAccountId = 1 --ISA
  AND Data55 = @ReportDate
  AND Data27 LIKE 'Deposit%'
  AND bc.Branch IN (@Branch)
  AND IsDeleted = 0
  AND IsCancelled = 0
  AND Data2 NOT LIKE 'NED_Clearance%';

INSERT INTO #FC1
SELECT B AS A
FROM #FC2;

DROP TABLE IF EXISTS #FC3;
SELECT SUM(A) AS Amount
INTO #FC3
FROM #FC1;

DROP TABLE IF EXISTS #PIA3;
SELECT *
INTO #PIA3
FROM (SELECT CONVERT(char(10), CAST(GETDATE() AS date), 126) AS Date,
             (SELECT CASE WHEN LEN(Code) = 3 THEN '0' + Code ELSE Code END AS Code
              FROM Custom_Test_TestCodes
              WHERE @Branch IN (Branch)) AS Code,
             '30100' AS Account,
             'E' AS VatCode,
             'Fit Cash to Treasury ' + CONVERT(varchar, @ReportDate, 103) AS Description,
             '' AS TransCode,
             'C' AS DC,
             Amount,
             'GL Movements' AS Category,
             'Fit Cash to ATM Recycling Account' AS Reason,
             (SELECT 'FCS ' + Branch FROM Custom_Test_TestCodes WHERE @Branch IN (Branch)) AS [Client Name],
             @Capturer AS Capturer
      FROM #FC3
      UNION ALL
      SELECT CONVERT(char(10), CAST(GETDATE() AS date), 126) AS Date,
             (SELECT CASE WHEN LEN(Code) = 3 THEN '0' + Code ELSE Code END AS Code
              FROM Custom_Test_TestCodes
              WHERE @Branch IN (Branch)) AS Code,
             '30150' AS Account,
             'E' AS VatCode,
             'Fit Cash to Treasury ' + CONVERT(varchar, @ReportDate, 103) AS Description,
             '' AS TransCode,
             'D' AS DC,
             Amount,
             'GL Movements' AS Category,
             'Fit Cash to ATM Recycling Account' AS Reason,
             (SELECT 'FCS ' + Branch FROM Custom_Test_TestCodes WHERE @Branch IN (Branch)) AS [Client Name],
             @Capturer AS Capturer
      FROM #FC3) a;

--******************************************************************************************--
SELECT DC,
       SUM(Amount) AS Totals
FROM (SELECT *
      FROM #PIA1
      UNION ALL
      SELECT Date,
             Code,
             Account,
             VatCode,
             Description,
             TransCode,
             DC,
             SUM(amount),
             Category,
             Reason,
             [Client Name],
             Capturer
      FROM #PIA2
      GROUP BY Date,
               Code,
               Account,
               VatCode,
               Description,
               TransCode,
               DC,
               Category,
               Reason,
               [Client Name],
               Capturer
      UNION ALL
      SELECT *
      FROM #PIA3
      WHERE Amount <> 0) a
GROUP BY DC;







--select dc, sum(amount) as Totals
--from #DCWC
--group by DC

**我遇到的问题如下：

在需要条件的上下文中指定的非布尔类型表达式，位于“,”附近。

Answer 1

这一点就是问题所在：

WHERE @Branch IN (Branch)

您在 #PIA3 查询中出现了 4 次。

使用多值参数，这将变成 Where 'one','two','third' IN (Branch) 这是非法的 sql。但使用单个值时，您会得到 'one' IN (Branch) ，这将起作用。

SSRS 通过使用“技巧”来执行多值参数 - 它不执行正确的参数化 SQL，而是使用字符串替换将逗号分隔的值列表放入 SQL 中。

但这仅适用于 Branch in (@Branch)

它不适用于 @Branch in (Branch)

从我可以看到的你的查询来看，如果你只是将它们交换过来会没事的。 @Branch 始终需要位于 IN 的右侧

Answer 2

这并不是真正的答案，但对于评论来说太多了……

就目前情况而言，您有很多子查询将返回多个值。

现在重点关注您插入#PIA3 的区域...
您的“代码”列将生成多行，并再次生成 [Client Name] 列。

我认为你需要重构每个部分。如果看不到您的数据，就很难理解您期望看到的内容。

我的猜测是......从 #FC3 开始，包含 1 行和 1 列。如果您选择超过 1 个分支，这样正确吗？我怀疑这必须按分支分开。从这里开始向后工作 #FC1 也需要按分支拆分。

因此 #FC1 将按如下方式创建

DROP TABLE IF EXISTS #FC1;
SELECT bc.Branch, SUM(cda.Amount) AS A
INTO #FC1
FROM ComparableData cda 
     JOIN ComparisonAccount ca ON cda.ComparisonAccountId = ca.Id
     JOIN ComparableDetail cde ON cda.ComparableDetailId = cde.id
     JOIN Custom_Test_TestCodes bc ON cde.Data1 = bc.Abbreviation
WHERE ComparisonAccountId = 1 --ISA
  AND Data55 = @ReportDate
  AND Data27 LIKE 'Deposit%'
  AND bc.Branch IN (@Branch)
  AND IsDeleted = 0
  AND IsCancelled = 0
  AND Data2 NOT LIKE 'NED_Clearance%'
GROUP BY bc.Branch;

#FC2 和 #FC3 也需要进行类似的更改

既然 #FC3 具有品牌列，您可以对 Custom_Test_TestCodes 表进行简单的联接。

这是我对构建 #PIA3 的代码的第一部分应该是什么样子的猜测

...
...
DROP TABLE IF EXISTS #PIA3;
SELECT *
INTO #PIA3
FROM (SELECT 
            CONVERT(char(10), CAST(GETDATE() AS date), 126) AS Date,
            CASE WHEN LEN(ctc.Code) = 3 THEN '0' + ctc.Code ELSE ctc.Code END AS Code,
             '30100' AS Account,
             'E' AS VatCode,
             'Fit Cash to Treasury ' + CONVERT(varchar, @ReportDate, 103) AS Description,
             '' AS TransCode,
             'C' AS DC,
             Amount,
             'GL Movements' AS Category,
             'Fit Cash to ATM Recycling Account' AS Reason,
             'FCS ' + ctc.Branch AS [Client Name],
             @Capturer AS Capturer
      FROM #FC3 f 
        JOIN Custom_Test_TestCodes ctc ON f.Branch = ctc.Branch 
      UNION ALL
      ...
      ...

这里有很多假设，所以它可能不是您想要的，但它应该让您了解问题和可能的解决方案。