CODEWITHSUNDEEP
t-sqlreporting-servicesssrs-2008-r2
user1816979
user1816979

Reputation: 531

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

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 r2 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 parameter 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?

Upvotes: 0

Views: 2738

Answers (1)

Mike D.
Mike D.

Reputation: 4104

In order to check the value of a multi-value parameter you need to use the IN operator.

IF 0 NOT IN (@reportID)

But it sounds to me like you're trying to check how many values have been assigned to the multi-value parameter and you can't really do that directly in the SQL query.

In your dataset properties go to the Parameters section and add a new parameter that's value is set by an expression. Then use this expression to set that parameter to the number of items in the multi-value parameter:

=Parameters!reportID.Count

You can then use that parameter in your SQL to check the number of values assigned to the multi-value parameter.

For more information about the Parameters collection in SSRS see this MSDN page: Parameters Collection References (Report Builder and SSRS)

Upvotes: 0

Related Questions