question

Hello everyone, I have a report that I am building in SSRS and I need a multi-value parameter to search 5 columns in my report. My report has Campus organization and then 5 roles that the person could have in the organization (not all or even any necessarily populated) . I need the end-user to be able to choose which roles they want to display a list of people for. So if Sue Ellen's Role 1 is PRESIDENT and Role 2 is VICE PRESIDENT and John Smith's role 1 is CPTN and role 2 is PRESIDENT and the person selects CPTN AND VICE PRESIDENT in the multi-valued parameter I need both of these records to show up. What is the best way to do this? Thanks Here is the updated query I use in the report: SELECT CAMPUS_ORG_MEMBERS_VIEW.CMPM_CAMPUS_ORGS_ID, MAX(CASE WHEN CAMPUS_ORG_ROLES.POS = 1 THEN CAMPUS_ORG_ROLES.CMPM_ROLES ELSE '' END) AS 'Role 1', MAX(CASE WHEN CAMPUS_ORG_ROLES.POS = 2 THEN CAMPUS_ORG_ROLES.CMPM_ROLES ELSE '' END) AS 'Role 2', MAX(CASE WHEN CAMPUS_ORG_ROLES.POS = 3 THEN CAMPUS_ORG_ROLES.CMPM_ROLES ELSE '' END) AS 'Role 3', MAX(CASE WHEN CAMPUS_ORG_ROLES.POS = 4 THEN CAMPUS_ORG_ROLES.CMPM_ROLES ELSE '' END) AS 'Role 4', MAX(CASE WHEN CAMPUS_ORG_ROLES.POS = 5 THEN CAMPUS_ORG_ROLES.CMPM_ROLES ELSE '' END) AS 'Role 5', CAST(CAMPUS_ORG_MEMBERS_VIEW.CMPM_COMMENTS AS NVARCHAR(4000)) AS Comments, PREFERRED_MAIL_VIEW.ID, PREFERRED_MAIL_VIEW.Prefix, PREFERRED_MAIL_VIEW.[First Name], PREFERRED_MAIL_VIEW.[Middle Name], PREFERRED_MAIL_VIEW.[Last Name], PREFERRED_MAIL_VIEW.SUFFIX, PREFERRED_MAIL_VIEW.[Company Name], PREFERRED_MAIL_VIEW.[Address Line 1], PREFERRED_MAIL_VIEW.[Address Line 2], PREFERRED_MAIL_VIEW.[Address Line 3], PREFERRED_MAIL_VIEW.City, PREFERRED_MAIL_VIEW.State, PREFERRED_MAIL_VIEW.Zip, PREFERRED_MAIL_VIEW.[Address Type], PREFERRED_MAIL_VIEW.[Preferred Email], PREFERRED_MAIL_VIEW.Cell, PREFERRED_MAIL_VIEW.Home, PREFERRED_MAIL_VIEW.[Business Phone], PREFERRED_MAIL_VIEW.[Business Phone EXT], MAX(CASE WHEN INT.POS = 1 THEN INT.INTERESTS ELSE '' END) AS 'Interest 1', MAX(CASE WHEN INT.POS = 2 THEN INT.INTERESTS ELSE '' END) AS 'Interest 2', MAX(CASE WHEN INT.POS = 3 THEN INT.INTERESTS ELSE '' END) AS 'Interest 3', MAX(CASE WHEN INT.POS = 4 THEN INT.INTERESTS ELSE '' END) AS 'Interest 4', MAX(CASE WHEN INT.POS = 5 THEN INT.INTERESTS ELSE '' END) AS 'Interest 5', MAX(CASE WHEN INT.POS = 6 THEN INT.INTERESTS ELSE '' END) AS 'Interest 6', MAX(CASE WHEN INT.POS = 7 THEN INT.INTERESTS ELSE '' END) AS 'Interest 7', PREFERRED_MAIL_VIEW.PERSON_STATUS, PREFERRED_MAIL_VIEW.[Reunion Class 1], PREFERRED_MAIL_VIEW.[Reunion Class 2], PREFERRED_MAIL_VIEW.[Reunion Class 3], PREFERRED_MAIL_VIEW.[Mail Rules 1], PREFERRED_MAIL_VIEW.[Mail Rules 2], PREFERRED_MAIL_VIEW.[Mail Rules 3], CAMPUS_ORG_AFFILIATION.CMPM_START_DATES, CAMPUS_ORG_AFFILIATION.CMPM_END_DATES, CAMPUS_ORG_AFFILIATION.CMPM_STATUSES FROM PREFERRED_MAIL_VIEW RIGHT OUTER JOIN CAMPUS_ORG_MEMBERS_VIEW ON CAMPUS_ORG_MEMBERS_VIEW.CMPM_PERSON_ST_ID = PREFERRED_MAIL_VIEW.ID LEFT OUTER JOIN INT ON CAMPUS_ORG_MEMBERS_VIEW.CMPM_PERSON_ST_ID = INT.ID FULL OUTER JOIN CAMPUS_ORG_ROLES ON CAMPUS_ORG_ROLES.CAMPUS_ORG_MEMBERS_ID = CAMPUS_ORG_MEMBERS_VIEW.CAMPUS_ORG_MEMBERS_ID LEFT OUTER JOIN CAMPUS_ORG_AFFILIATION ON CAMPUS_ORG_MEMBERS_VIEW.CAMPUS_ORG_MEMBERS_ID = CAMPUS_ORG_AFFILIATION.CAMPUS_ORG_MEMBERS_ID WHERE (CAMPUS_ORG_AFFILIATION.POS = 1) GROUP BY CAMPUS_ORG_MEMBERS_VIEW.CMPM_CAMPUS_ORGS_ID, CAST(CAMPUS_ORG_MEMBERS_VIEW.CMPM_COMMENTS AS NVARCHAR(4000)), PREFERRED_MAIL_VIEW.ID, PREFERRED_MAIL_VIEW.Prefix, PREFERRED_MAIL_VIEW.[First Name], PREFERRED_MAIL_VIEW.[Middle Name], PREFERRED_MAIL_VIEW.[Last Name], PREFERRED_MAIL_VIEW.SUFFIX, PREFERRED_MAIL_VIEW.[Company Name], PREFERRED_MAIL_VIEW.[Address Line 1], PREFERRED_MAIL_VIEW.[Address Line 2], PREFERRED_MAIL_VIEW.[Address Line 3], PREFERRED_MAIL_VIEW.City, PREFERRED_MAIL_VIEW.State, PREFERRED_MAIL_VIEW.Zip, PREFERRED_MAIL_VIEW.[Address Type], PREFERRED_MAIL_VIEW.[Preferred Email], PREFERRED_MAIL_VIEW.Cell, PREFERRED_MAIL_VIEW.Home, PREFERRED_MAIL_VIEW.[Business Phone], PREFERRED_MAIL_VIEW.[Business Phone EXT], PREFERRED_MAIL_VIEW.PERSON_STATUS, PREFERRED_MAIL_VIEW.[Reunion Class 1], PREFERRED_MAIL_VIEW.[Reunion Class 2], PREFERRED_MAIL_VIEW.[Reunion Class 3], PREFERRED_MAIL_VIEW.[Mail Rules 1], PREFERRED_MAIL_VIEW.[Mail Rules 2], PREFERRED_MAIL_VIEW.[Mail Rules 3], CAMPUS_ORG_AFFILIATION.CMPM_START_DATES, CAMPUS_ORG_AFFILIATION.CMPM_END_DATES, CAMPUS_ORG_AFFILIATION.CMPM_STATUSES, CAMPUS_ORG_MEMBERS_VIEW.CAMPUS_ORG_MEMBERS_ID, CAMPUS_ORG_AFFILIATION.POS HAVING (PREFERRED_MAIL_VIEW.PERSON_STATUS = 'A') AND (CAMPUS_ORG_AFFILIATION.CMPM_END_DATES IS NULL) AND (MAX(CASE WHEN CAMPUS_ORG_ROLES.POS = 1 THEN CAMPUS_ORG_ROLES.CMPM_ROLES ELSE '' END) IN (SELECT Item FROM dbo.DelimitedSplit8K('''' + @RolesChoice + '''', ',') AS DelimitedSplit8K_1)) OR (MAX(CASE WHEN CAMPUS_ORG_ROLES.POS = 2 THEN CAMPUS_ORG_ROLES.CMPM_ROLES ELSE '' END) IN (SELECT Item FROM dbo.DelimitedSplit8K(@RolesChoice, ',') AS DelimitedSplit8K_2)) OR (MAX(CASE WHEN CAMPUS_ORG_ROLES.POS = 3 THEN CAMPUS_ORG_ROLES.CMPM_ROLES ELSE '' END) IN (SELECT Item FROM dbo.DelimitedSplit8K(@RolesChoice, ',') AS DelimitedSplit8K_3)) OR (MAX(CASE WHEN CAMPUS_ORG_ROLES.POS = 4 THEN CAMPUS_ORG_ROLES.CMPM_ROLES ELSE '' END) IN (SELECT Item FROM dbo.DelimitedSplit8K(@RolesChoice, ',') AS DelimitedSplit8K_4)) OR (MAX(CASE WHEN CAMPUS_ORG_ROLES.POS = 5 THEN CAMPUS_ORG_ROLES.CMPM_ROLES ELSE '' END) IN (SELECT Item FROM dbo.DelimitedSplit8K(@RolesChoice, ',') AS DelimitedSplit8K_5)) ORDER BY PREFERRED_MAIL_VIEW.ID, CAMPUS_ORG_MEMBERS_VIEW.CMPM_CAMPUS_ORGS_ID