[SOLVED] SSRS 2014 date range parameters not working with an OR clause

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 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)   
    )   
)