question

muk avatar image
muk 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
sql-server-2008-r2ssrssql-serverreportingreporting_services
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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/
9 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Pavel can you elaborate on splitting the values? So should I write a stored procedure taking one parameter which is my MultiValue parameter then split its values? And by select data necessary should I just select it as is? I am a little confused.
0 Likes 0 ·
@Pavel I updated my original post with the query I am using to write my report. As you can see I get the Roles by doing a max case statement.
0 Likes 0 ·
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.
0 Likes 0 ·
@Pavel Thank you so much, here are the errors I get: Invalid column name 'Value'. Invalid column name 'CAMPUS_ORG_MEMBER_ID'.
0 Likes 0 ·
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.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.