0

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'))
Fatemeh Shakhsesalim
  • 51
  • 9
  • 1
    I hate when people say this **but** what's your query? The parser for SSRS seems to not like stuff that SSMS will let pass. There's probably some questionable syntax that SSMS converts but SSRS doesn't like. – Hannover Fist Apr 26 '16 at 21:13
  • 1
    Please show your SQL code ... at least the beginning part where you declare the parameter and the where clause. Also, how are you defining the parameter in SSRS ? Is it a text or are you using "Available Values" and translating the text to something else like an integer. A lot of places to look for the issue ... need more specifics. – J Greene Apr 26 '16 at 23:34
  • Hannover Fist and J Greene, I edit the question and hopefully I cover what you wanted. – Fatemeh Shakhsesalim Apr 27 '16 at 15:22
  • Why don't you just hard-code the parameter into the where clause? You've already hard-coded it into the query by setting its value when you declared it. What are you hoping to gain by keeping it hard-coded in the parameter declaration? – Jesse Potter Apr 27 '16 at 23:10

1 Answers1

1

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.

Jesse Potter
  • 827
  • 5
  • 20