command level parameters in crystal reports

Question

I have a report having 2 parameters carton and location.

Suppose if I didn't gave any value among those then it should give all values.

So created 2 command level parameters.Its showing all values whenever I didnt gave any values in parameters.

But even If I gave some value in either carton or location but still its showing all values.

Please suggest what is the problem

SELECT
      crt.carton_no, crtd.part_no, SUM(crtd.quantity) AS quantity,
      crtd.barcode, crtd.item_description
      , crt.put_away_location AS putAway
FROM
      carton crt, carton_details crtd
WHERE
      crt.carton_id = crtd.carton_id
      AND crt.status = 'N' AND
     ( crt.carton_no like  '{?cartonno}' or '{?cartonno}'  like '%' ) and (crt.put_away_location LIKE '{?location}' or '{?location}'  like '%')
GROUP BY
      crt.carton_no, crtd.carton_id, crtd.part_no
ORDER BY
      crt.put_away_location, crt.carton_no

Answer 1

I guess your query is always taking true ignoring or condition... try using Case

where
    CASE WHEN {?cartonno} Like ""
            THEN
                '{?cartonno}'  like '%'
            ELSE
                crt.carton_no like  '{?cartonno}'
            END

Note: I don't know which database you are using above is just an example of SQL... you can change it as required and use similarly for other parameter in where clause