SSRS Multil Select Parameter - Query Help
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?
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?
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?
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?
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
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
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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
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
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)
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)
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.