please find the query below and for ClaimStatus report parameter i m supplying available values those are Pay,Paid. i should get all(both paid and pay) status records WHEN I select NULL in report parameter.
i am converting this report from crystal to ssrs 2008.in crystal even if we supply paramenter values also wewill get NULL check box and when we select that it displays all. now i need same functionality.what can i include in where conditon's status statement
@startOK datetime
,@endOK datetime
, @ClaimStatus CHAR(10)
AS
--set @startOK = '2/1/2011'
--set @endOK = '5/26/2011'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Remove time part of parameters
SET @startOK = CAST(CONVERT(VARCHAR, @startOK, 101) AS DATETIME)
SET @endOK = CAST(CONVERT(VARCHAR, @endOK, 101) AS DATETIME) + 1
SELECT c.id, reason, okpayby, cd.servcode, cd.amt, cd.paidamt, cd.amountpaid,
ct.description as contract, b.description as benefit, c.status
FROM claim c
JOIN claimdetail cd on cd.claimid = c.claimid
JOIN benefit b on b.benefitid = cd.benefitid
JOIN contract ct on ct.contractid = cd.contractid
WHERE ((c.status IN ('PAID','PAY')) and c.formtype = '2000'
and c.resubclaimid = ''
and okpaydate between @startOK and @endOK
Thanks