CODEWITHSUNDEEP
ssrs-2008-r2
Fatemeh Shakhsesalim
Fatemeh Shakhsesalim

Reputation: 51

the query works fine in SQL server, but not in SSRS

the report query has a parameter that can take one of two values: ActiveCustomers or AllCustomers and based of what user picks the data get filtered.

My problem here is the query works fine in SQL server, but is not working in SSRS unless I hard code the value for the parameter in the data set query or in the where clause that do the filtering use "like" instead of "=".

the SSRS parameter is defined as type TEXT and has available values "ActiveCustomers" and "AllCustomers" and is defaulted to take "ActiveCustomers". I already checked the spellings are correct and consistent.

My question here is why the SSRS is not working with "=" and I have to use "like".

here is how I declare the parameter and the where clause from data set query:

  declare @Show nvarchar(32) = 'ActiveCustomers' --or 'AllCustomers'

and the where clause that filters data is:

where  (Order like '%2016')
     and ((@Show = 'ActiveCustomers' and Order.[Status] <> 'Closed') or (@Show = 'AllCustomers'))

Upvotes: 0

Views: 1494

Answers (1)

Jesse Potter
Jesse Potter

Reputation: 847

An easy work-around is to save the query as a stored procedure. Then have SSRS execute the stored procedure. That has gotten me around complicated query issues before.

Upvotes: 1

Related Questions