Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 An expression of non-boolean type specified in a c

Author  Topic 

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-09-15 : 15:52:03
I am getting the error message, "An expression of non-boolean type specified in a context where a condition is expected, near ',' "
when running an ssrs 2008 2012 report.
The sql embedded in the dataset is:
IF @reportID <> 0
BEGIN
SELECT 'Students report 1' AS selectRptName, 1 AS rptNumValue
UNION
SELECT 'Students report 2', 2
UNION
SELECT 'Students report 3', 3
UNION
SELECT 'Students report 4', 4
UNION
SELECT 'Students report 5', 5
ORDER BY selectRptName
END
)

The sql runs fine in managment studio when I declare @reportID.

The sql runs fine with I comment out 'IF @reportID <> 0'.

The @reportID is a parmeter value that is passed to the applicable dataset.
The @reportID can have more than one value.

Thus can you show me sql and/or tell me what I need to do to solve the issue for me?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-15 : 16:44:41
This won't work if @reportID has more than one value. You'll get something like this passed to SQL.

If 1,2 <> 0
...
Which is of course not valid SQL ans will result in the very error you are seeing. You should try

If 0 not in (@reportID)
...
Go to Top of Page

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-09-15 : 18:35:31
Thus I am thinking of calling the following following function to split out the parameter values:

FUNCTION [dbo].[fn_splitString]
(
@listString VARCHAR(MAX)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
SELECT SUBSTRING(l.listString, sn.Num + 1, CHARINDEX(',', l.listString, sn.Num + 1) - sn.Num - 1) _id
FROM (SELECT ',' + LTRIM(RTRIM(@listString)) + ',' AS listString) l
CROSS JOIN dbo.sequenceNumbers sn
WHERE sn.Num < LEN(l.listString)
AND SUBSTRING(l.listString, sn.Num, 1) = ','
)

GO

Can you show me sql code on how to remove the @reportID <> 0 t-sql above and replace by calling the fn_splitString
function?

Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-16 : 07:32:12
You Dont need the function. In SSRs code the query as IN with your var in parents. At runtime, SSRs will substitute with either a single value or a list. Both should work just fine
Go to Top of Page

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2014-09-16 : 10:48:26
solved with IN with your var in parents
Go to Top of Page
   

- Advertisement -