SSRS Multil Select Parameter - Query Help

Scott Williams
Scott Williams used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
My thoughts:

I can't see ClientName in your SELECT.

Why doesn't the report work - what do you mean? You get no results?

>> if a user enters a Client and then enters a ClaimNumber that doesn't match, the report won't work.

Yep - that is what would happen. So what do you want to happen instead?
Scott WilliamsData Analyst

Author

Commented:
i.InsuredName1 as ClientName

I have corrected my Where statement to this:
WHERE LossDate between @FromDate and @ToDate
AND co.FileName like '%' + @Phrase + '%'
AND (i.InsuredName1 in(@ClientName) OR @ClientName IS NULL)
AND (ClaimNumber = @ClaimNumber OR @ClaimNumber IS NULL)

So If I leave the ClientName blank and set the ClaimNumber =GCGL01005724
I get a Parameter Error - asking me to select a value for ClientName
Scott WilliamsData Analyst

Author

Commented:
How do I change my query so either can be used but not both.
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
Which is your preference?
Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
If you have this:

AND (ClientName in(@ClientName) OR @ClientName IS NULL)
AND (ClaimNumber = @ClaimNumber OR @ClaimNumber IS NULL)

and you don't want the ClaimNumber to be considered if there is a ClientName, then change it to this:

AND (ClientName in(@ClientName) OR @ClientName IS NULL)
AND (ClaimNumber = @ClaimNumber OR @ClaimNumber IS NULL OR @ClientName IS NOT NULL)
Scott WilliamsData Analyst

Author

Commented:
So when I use this:
  AND (ClientName in(@ClientName) OR @ClientName IS NULL)
  AND (ClaimNumber = @ClaimNumber OR @ClaimNumber IS NULL OR @ClientName IS NOT NULL)

I run the report and enter the From and To Date and the Claim Number and leave the ClientName empty and I get error - Select a value for parameter ClientName
Scott WilliamsData Analyst

Author

Commented:
So then I tried to run with the Claim Number set to GCGL01005724 and the ClientName set to All and I get this error:
An expression of non-boolean type specified in a context where a condition is expected, near ','.

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

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 (InsuredName1 in(@ClientName) OR @ClientName IS NULL)
AND (ClaimNumber = @ClaimNumber OR @ClaimNumber IS NULL OR @ClientName IS NOT NULL)
Scott WilliamsData Analyst

Author

Commented:
Here's what I want to do.  But it won't work:

WHERE CASE WHEN ISNULL(@ClaimNumber) THEN InsuredName1 in(@ClientName)
ELSE ClaimNumber = @ClaimNumber AND InsuredName1 in(select distinct InsuredName1 from Insured (NOLOCK)) END

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial