troubleshooting Question

SSRS Multil Select Parameter - Query Help

Avatar of Scott Williams
Scott Williams asked on
Microsoft SQL Server 2008SSRSSQL
8 Comments1 Solution302 ViewsLast Modified:
So I have an SSRS report that has 5 parameters.  Three of the parameters are set - with AND in the SQL Where clause:
  WHERE LossDate between @FromDate and @ToDate
  AND co.FileName like '%' + @Phrase + '%'

I have 2 other parameters that I need to add to the query and they are both optional

Here is my broken query:
SELECT c.LossDate, co.CorrespondenceID, c.ClaimNumber,
co.[FileName], co.Title, co.EntryDate, i.InsuredID, i.InsuredName1 as ClientName,
U1.LastName+', '+U1.FirstName as EnteredByName,
U2.LastName+', '+U2.FirstName ExaminerName,
AddressBook.LastFirstName as ClaimantName

FROM Correspondence co (NOLOCK)
JOIN Claimant cl (NOLOCK) ON co.ClaimantID = cl.ClaimantID
JOIN Claim c (NOLOCK) ON cl.ClaimID = c.ClaimID
JOIN Policy p (NOLOCK) ON c.PolicyID = p.PolicyID
JOIN Insured i (NOLOCK) ON p.InsuredID = i.InsuredID
LEFT OUTER JOIN CorrespondenceStatus cs (NOLOCK) ON co.StatusID = cs.StatusID
LEFT OUTER JOIN Users U1 (NOLOCK) ON co.EnteredBy = U1.UserName
LEFT OUTER JOIN Users U2 (NOLOCK) ON c.ExaminerCode = U2.UserName
LEFT OUTER JOIN AddressBook on cl.AddressBookID = AddressBook.AddressBookID

WHERE LossDate between @FromDate and @ToDate
AND co.FileName like '%' + @Phrase + '%'
AND (ClientName in(@ClientName) OR @ClientName IS NULL)
AND (ClaimNumber = @ClaimNumber OR @ClaimNumber IS NULL)

@ClientName  is a list box where they can select one or all values - with no default value
@ClaimNumberis a text parameter where they can enter a single value

The issue with them both being AND in the Where clause is if a user enters a Client and then enters a ClaimNumber that doesn't match, the report won't work.

Do I need to use a Case statement in the Where clause?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros