title: Parameter Case statement in Where clause
body: <p>Need to separate orders if they are 'Millwork' or 'Not Millwork'. A parameter allows the user to select the order type when generating their report.</p>
  
  <p>Moved the parameter Case statement from the Select to the Where clause per <a href="https://stackoverflow.com/questions/6545664/" title="using case expression column in where clause">Stack entry #6545664</a></p>
  
  <p>The whole report returns data successfully in SQL Server but not in Report Server.</p>
  
  <p>@OrderClass is the parameter problem. It returns two values: MillworkOnly and NonMillwork</p>
  
  <pre><code>--Parameter @OrderClass
  SELECT DISTINCT 
  CASE 
  WHEN XGENERALDESCRIPTION LIKE 'MILLWORK%' THEN 'MillworkOnly'
  ELSE 'NonMillwork'
  END
  FROM SPV3SALESDOCUMENT
  </code></pre>
  
  <p>This statement below returns the main report data. It runs successfully in SQL Server 2012 as is but only runs successfully in Report Server when the Case statement at the end of the Where clause is commented out (as shown).</p>
  
  <p>Suspecting the issue could be the parameter results are not yet available to the main statement when it runs. </p>
  
  <p>How does a parameter run within a Where clause?</p>
  
  <pre><code>SELECT DISTINCT
  H.DOCID
  , H.CUSTNAME
  , H.CUSTNMBR
  , H.PRSTADCD
  , L.ITEMCODE
  , H.ShipToName
  , H.ADDRESS1
  , H.ADDRESS2
  , H.ADDRESS3
  , H.CITY
  , H.STATE
  , H.ZIPCODE
  , H.SLPRSNID
  , H.SALSTERR
  , H.MODIFDT
  , H.SOPSTATUS
  , H.SOPTYPE
  , H.SOPNUMBE
  , H.CSTPONBR
  , H.SHIPMTHD
  , L.LNITMSEQ
  , L.ITEMNMBR
  , L.ITEMDESC
  , L.NONINVEN
  , L.UOFM
  , L.LOCNCODE
  , L.UNITPRCE
  , L.XTNDPRCE
  , L.QUANTITY
  , L.ATYALLOC
  , L.QTYFULFI
  , L.ReqShipDate
  , DATEADD(DAY, 2-DATEPART(WEEKDAY, L.ReqShipDate), L.ReqShipDate) as nWkOf
  , L.FUFILDAT
  , SM.MANUFACTUREORDER_I
  , a.xSpiPM
  , a.xCustPM
  , a.xCeilingType
  , a.xCeilingheight
  , a.xLatitude
  , a.xLongitude
  , s.xSignatureRequired
  , s.xDrvMilesToStore
  , s.xFrtMilesToStore
  , s.xGeneralDescription
  
  
  FROM SOP10200 AS L --ORDER LINES
  
  INNER JOIN SOP10100 AS H --ORDER HEADER
      ON L.SOPTYPE = H.SOPTYPE 
      AND L.SOPNUMBE = H.SOPNUMBE 
  
  INNER JOIN IS010001 AS SM --SOPMOP LINK
      ON L.SOPNUMBE = SM.SOPNUMBE 
      AND L.SOPTYPE = SM.SOPTYPE 
      AND L.LNITMSEQ = SM.LNITMSEQ 
      AND L.CMPNTSEQ = SM.CMPNTSEQ
  
  LEFT JOIN spvCustomerAddr AS A --SP UDF
      ON H.CUSTNMBR = A.Customer_Num 
      AND H.PRSTADCD = A.Address_Code
  
  LEFT JOIN spv3SalesDocument AS S --SP UDF
      ON L.SOPNUMBE = s.Sales_Doc_Num
  
  WHERE
  H.VOIDSTTS = 0 
  AND L.ITEMNMBR NOT LIKE '1%' 
  AND (L.ReqShipDate BETWEEN (@StartDate) and (@EndDate)) 
  AND @OrderClass = 
      CASE 
      WHEN s.xGeneralDescription LIKE 'MILLWORK%' THEN 'MillworkOnly'
      ELSE 'NonMillwork'
      END
  AND H.SLPRSNID in (@AccountRepName) 
  AND H.CUSTNAME in (@CustOrderName) 
  AND H.docid = @OrderType AND L.QUANTITY &lt;&gt; 0 
  AND (L.QUANTITY - L.QTYFULFI) &gt;= (@FulfilledLines);
  </code></pre>
  
  <p>Report Server error = </p>
  
  <p><code>For more information about this error navigate to the report server on
  the local server machine, or enable remote errors
  ---------------------------- Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
  ---------------------------- An error has occurred during report processing. (rsProcessingAborted)</code></p>