asked
SSRS Report MultiValue Parameter on multiple columns
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
The best approach would be to create a stored procedure which takes the multi-value paramater, splits it's values using eg. the [CSV Splitter][1] by @Jeff Moden or eg. [CLR Splitter][2]. and select data necessary in the stored proc and return them to SSRS. You can join the keys of the multi-value parameter using the Join SSRS function. [1]:
http://www.sqlservercentral.com/articles/Tally+Table/72993/ [2]:
http://www.pawlowski.cz/2010/10/fastest-csv-strings-splitting-using-clr-t-sql-vs-clr-revisited/
From the first look as enough should be define the WHERE condition as below: WHERE (CAMPUS_ORG_AFFILIATION.POS = 1) AND CAMPUS_ORG_MEMBERS_VIEW.CAMPUS_ORG_MEMBERS_ID IN (SELECT CAMPUS_ORG_MEMBER_ID FROM CAMPUS_ORG_ROLES WHERE CMPM_ROLES IN (SELECT Value FROM dbo.DelimitedSplit8k(@param, ',')) ) It assumes, that you have create the `DelimitedSplit8k` function from the article by @Jeff Moden mentioned in the post. The `@param` parameter will contain joined values separated by comma from the multi select parameter.
Nvm I realized that the second one was because I was missing an S in MEMBERS but it still says Invalid column name 'Value'. And when I exit query designer it pops up a box for the parameter and to enter a value for the "query parameter". Sorry I am new to SSRS is why I ask so many questions.
Ah.. sorry. the Delimited Splitter returns **`Item`** field and not `Value`.
@Pavel I tried to do it with the Where condition you specified but it did not let me it said problem with query syntax so I updated the query and now it still works for one value but I am using the DelimitedSplit8k function you provided. Can you look at my new code in the original post and tell me what you think? It gives me the error too many arguments in function DelimitedSplit8k. I suspect this is because using the parameter as an argument does put the list in delimited with commas like it is supposed to, however it is missing the outside apostrophes so thats why it thinks I put two many arguments. Each box in the parameter that I select when I run the report goes into the function as a separate argument; rather than the entire list as an argument like this 'a,b,c,d' (instead it just has a,b,c,d without the outside apostrophes)
8 People are following this question.
