January 26, 2015 at 10:50 am
Hello,
I'm new to SSRS, having used Crystal for about 2 years. I'm trying to re-create some of the reports I've done in CR and having an issue with an issue that I could never solve in CR.
We host two drives of drives/events: mobile drives and fixed site drives. Mobile drives are OwnerType=0 and Fixed Sites drives are OwnerType=1 in the DriveMaster table. Also, Mobile Drives have a recruiter assigned to them while Fixed Sites do not (NULL value in RecruiterID).
My issue is that I want to be able to add a parameter to filter records by recruiter, but when I do so, I prevent any Fixed Site records from being displayed. And if I try to check the box in the RecruiterID parameter to allow for NULL values, I get an error stating a multi-value parameter cannot include null values.
Somehow, I need to allow fixed sites drive records come back on the report while keeping the recruiter parameter.
Would a case statement be needed in the where clause that states if DM.Ownertype=1 then RecID is null else RecID in (@RecruiterID).
I've tried a few variations of this but can't see to find the proper verbage, if that is even the correct syntax.
Any suggestions?
January 27, 2015 at 1:15 am
Consider the below - this is my preferred way of solving this type of issue. (Redundant brackets around the outside because I would then likely add another unrelated condition that I don't want to be messed with by that OR statement):
SELECT *
FROM DriveMaster
WHERE (DriveMaster.RecID in (@RecruiterID) OR DriveMaster.Ownertype = 1)
January 27, 2015 at 1:41 pm
Thanks, I'll give this a shot.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply