Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!
Learn how Concerto can help you.
Solved
SSRS Multil Select Parameter - Query Help
Posted on 2014-11-12
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?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
3
8 Comments
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
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)
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)
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)
Featured Post
This course (1 of 3) is designed to help students who are interested in Google Cloud Platform (GCP) to become familiar with the platform, navigate the console and learn its capabilities. It will also prepare students for the Google Cloud Architect certification exam.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on.
…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data.
But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses
Course of the Month7 days, 22 hours left to enroll
715 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.