Multi-value parameter in SSRS not working with select all : An expression of nonboolean type specifi-CodePudding

The report runs fine if you select a single branch, but it breaks when you select more than one or 'Select All'.

So this is my query that I added to SSRS, I'm certain the issue is with the @Branch, but I'm not too sure where, I'm running 3 queries similar to this, but all 3 are breaking, so I just need help with one in order to do the rest.

--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

**The issue I'm having is the following:

An expression of non-boolean type specified in a context where a condition is expected, near ','.

CodePudding user response：

This bit is the problem:

WHERE @Branch IN (Branch)

You've got that 4 times in the #PIA3 query.

With a multi-valued param this will become Where 'one','two','three' IN (Branch) which is illegal sql. But with a single value you get 'one' IN (Branch) which will work.

SSRS does multi-valued parameters by using a 'trick' - instead of doing proper parameterised SQL, it just chucks the comma separated list of values into the SQL using string substitituion.

But that only works for Branch in (@Branch)

It wont work for @Branch in (Branch)

From what I can see of your query if you just swap them round it will be fine. @Branch always needs to be on the right hand side of an IN

CodePudding user response：

This is not really an answer but too much for a comment...

As it stands, you have lots of subqueries that will return more than one value.

Focussing on the area where you insert into #PIA3 for now... Your 'Code' column will produce multiple rows and again with the [Client Name] column.

You will need to refactor each part I think. Without seeing your data it's difficult to understand but what you expect to se.

My guess would be... starting with #FC3, that contains 1 row and 1 column. Is that correct if you chose more than 1 branch? I suspect this would have to be split by branch. Working backwards from here #FC1 would also need to be split by branch.

So #FC1 would be created as follows

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;

A similar change would be required for #FC2 and #FC3

Now that #FC3 has a brand column you can do a simple join to your Custom_Test_TestCodes table.

Here's my guess as to what the first part of the code that builds #PIA3 shoudl look like

...
...
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
      ...
      ...

There's a lot of assumptions here so it may not be what you want but it shoudl give you an idea as to the issues an possible solutions.