February 7, 2018 at 1:11 pm
Hi everyone,
I have a unique request from a customer, I am even having trouble to word it out in the subject. Anyway, I will try explain here and any insight is greatly appreciated.
Imaging I have the following records
Name Skills
John A .Net
John A C#
Jane C#
Robert BA
So on the report, the customer wants to see a list of people with selected skills. I used a multiple values parameter, Skill, with the options of .Net, C# and BA. Typically, if I select .Net and C#, It will return:
John A
Jane
What the customer really wants is to return people with both skills, not either skill. (it's AND operator, not OR), really it should only return:
John A
I don't think that's what multiple values parameter is meant to be, but any solution/insight/work-around is greatly welcomed.
Thanks!
Jack
February 7, 2018 at 5:55 pm
I'm working on it... In T-SQL, it would be like this:
(Yes, I'm aware that this table design of Candidate is silly!!!)
.CREATE TABLE Candidate(
CandidateID INT IDENTITY,
FirstName VARCHAR(15) NOT NULL
CONSTRAINT pkCandidate PRIMARY KEY (CandidateID),
CONSTRAINT uqFirstName UNIQUE (FirstName));
GO
CREATE TABLE CandidateSkills(
SCandidateID INT,
Skill VARCHAR(10)
CONSTRAINT pkCS PRIMARY KEY (ScandidateID, Skill),
CONSTRAINT fkCandidate FOREIGN KEY (SCandidateID) REFERENCES Candidate(CandidateID));
GO
Now add some recordsINSERT INTO Candidate(FirstName) VALUES ('John'),('Jane'),('Robert');
INSERT INTO CandidateSkills(SCandidateID,Skill)
VALUES (1,'.Net'),(1,'C#'),(2,'C#'),(3,'BA');;
T-SQL Answer... well, a start...DECLARE @Skill1 VARCHAR(10) = 'C#',
@Skill2 VARCHAR(10) = '.Net';
SELECT *
FROM Candidate c
WHERE EXISTS (
SELECT 1
FROM CandidateSkills cs
WHERE cs.SCandidateID = c.CandidateID
AND cs.Skill= @Skill1 )
AND EXISTS (
SELECT 1
FROM CandidateSkills cs
WHERE cs.SCandidateID = c.CandidateID
AND cs.Skill=@Skill2 )
This does the ANDing of the filters (unlike the OR that IN() does.)
Not sure how to do the ANDing from SSRS....
February 8, 2018 at 11:25 am
Jack,
I asked a question about how to do this in another forum. Check this out:
https://www.sqlservercentral.com/Forums/1922517/EXISTS-ALL#bm1922645
February 8, 2018 at 12:02 pm
Thanks both for prompt response!
It's truly helpful.
February 8, 2018 at 5:52 pm
Jack,
Luis' answer fixed it. This is the query I ended up using that worked for me:SELECT candidate_id, first_name, last_name
FROM Candidate AS c
WHERE (NOT EXISTS
(SELECT 1 AS Expr1
FROM Candidate AS ci CROSS JOIN
Skill AS s LEFT OUTER JOIN
CandidateSkills AS cs ON ci.candidate_id = cs.candidate_id AND s.skill_id = cs.skill_id
WHERE (cs.candidate_id IS NULL) AND (ci.candidate_id = c.candidate_id) AND (s.skill_id IN (@prmSkills))))
I created a multi-valued parameter which used a dataset of Skills (SkillID, SkillName). Then I could select the group of skills I am looking for and return only the candidates with them all.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply