sqlsql-serverfilteringssrs-2014

SSRS 2014 date range parameters not working with an OR clause


I have a query in SSRS 2014 that has an OR clause between a date range parameter and a like parameter. The date ranges are nullable as well as the like parameter.

The problem I am having is the data is not being filtered by date when the OR clause is included. If I remove the OR clause then the results are filtered by the date. I am not sure if I am being very clear so please ask for clarification if needed.

I need to be able to filter by date range or other fields or by date range and other fields.

    SELECT [OrdNum]
      ,[EcommOrdNum]
      ,[SalesOrdPO]
      ,[cust_num]
      ,[cust_seq]
      ,[CorpCustNum]
      ,[CorpName]
      ,[CustName]
      ,[Address]
      ,[Address2]
      ,[City]
      ,[State]
      ,[Zip]
      ,[ShipVia]
      ,[Item]
      ,[Qty]
      ,[Price]
      ,[OrderDate]
      ,[ShipDate]
      ,[SerialNum]
      ,[Shipping]
      ,[Tracking]
      ,[OrderStat]
  FROM [CRM].[dbo].[SalesOrders_Rpt]
WHERE SalesOrders_Rpt.CorpCustNum = '    321654'
AND (SalesOrders_Rpt.OrderDate >= @StartDate + '00:00:00.000' and SalesOrders_Rpt.OrderDate <= @EndDate + '23:59:59.000'
OR SalesOrders_Rpt.SalesOrdPO like  '%'  + @SalesOrdPO + '%')

Any help is greatly appreciated.


Solution

  • If you are allowing optional parameters in your query then you must allow the optional value in your select clause OR'ed with an actual value, if one is provided.

    WHERE SalesOrders_Rpt.CorpCustNum = '    321654'
    AND 
    (
        (
            SalesOrders_Rpt.OrderDate >= ISNULL(@StartDate,'01/01/1901') + '00:00:00.000' 
            and 
            SalesOrders_Rpt.OrderDate <= ISNULL(@EndDate,'12/31/2200') + '23:59:59.000'
        )
        AND
        (
            (SalesOrders_Rpt.SalesOrdPO like  '%'  + @SalesOrdPO + '%')
            OR
            (@SalesOrdPO IS NULL)   
        )   
    )