I've found a lot of help online about passing multi-value parameters to a drillthrough report in SSRS. I haven't found a successful response for multiple multi-value parameters in SSRS.
To explain, I have the following summary report:
SELECT
SH.[USER-ID],
U.[DESCRIPTION] AS "USER NAME",
COUNT (J.[JOB-NO]) AS "NUMBER-OF-JOBS"
FROM
[RM-JOB] AS J
LEFT OUTER JOIN
[RM-STAGE-HISTORY] AS SH ON SH.[JOB-NO] = J.[JOB-NO]
JOIN
[CO-USERS] AS U ON U.[USER-NAME] = J.[RAISED-BY]
LEFT JOIN
[IH-LOCATION] L ON J.[PLACE-REF] = L.[PLACE-REF]
LEFT JOIN
[IH-LOC-ATTR] LA ON J.[PLACE-REF] = LA.[PLACE-REF] AND LA.[ATTRIB-CODE] = 'SBU'
WHERE
J.[DATE-LOGGED] BETWEEN @DATE1 AND @DATE2
AND
SH.[STAGE-CODE] IN ('06')
AND
SH.[STAGE-DATE] BETWEEN @DATE1 AND @DATE2
AND
J.[RAISED-BY] = SH.[USER-ID]
AND
LA.[SUB-ATTRIB-CODE] IN (@SBU)
AND
J.[RAISED-BY] IN (@RAISEDBY)
AND
J.[LATEST-PRIORITY] IN (@Priority)
AND
L.[MGT-AREA] IN (@MGTAREA)
GROUP BY
SH.[USER-ID],
U.[DESCRIPTION]
ORDER BY
U.[DESCRIPTION]
And the following Details Report:
SELECT DISTINCT
J.[RAISED-BY],
J.[JOB-NO],
LEFT(J.[PLACE-REF],3) AS [SCHEME],
J.[PLACE-REF],
L.[MGT-AREA],
LA.[SUB-ATTRIB-CODE] SBU,
J.[JOB-STATUS],
CONCAT(IP.[PRIORITY-CODE], '- ',IP.DESCRIPTION) [INITIAL-PRIORITY],
CONCAT(LP.[PRIORITY-CODE], '- ',LP.DESCRIPTION) [LATEST-PRIORITY],
J.[DATE-LOGGED],
J.[CURRENT-STAGE-CODE],
J.[ORIG-LOGGED-VALUE] AS "LOGGED-VALUE",
J.[TOTAL-VALUE],
J.[SHORT-DESCRIPTION],
J.[CONTRACTOR],
C.[DESCRIPTION],
J.[CL-ANALYSIS-CODE-ELEMENT],
J.[CL_ANALYSIS_CODE_ELEMENT##2],
J.[CL_ANALYSIS_CODE_ELEMENT##3],
J.[CL_ANALYSIS_CODE_ELEMENT##4],
J.[CL_ANALYSIS_CODE_ELEMENT##5],
SH.[JOB-AMENDED]
FROM
[RM-JOB] AS J
LEFT OUTER JOIN
[RM-CONT] as C ON C.CONTRACTOR = J.CONTRACTOR
RIGHT OUTER JOIN
(SELECT
[JOB-NO],
CASE WHEN MAX([STAGE-CODE]) = ('11') THEN 'YES' ELSE 'NO' END AS "JOB-AMENDED"
FROM
[RM-STAGE-HISTORY]
WHERE [STAGE-CODE] IN ('06','11')
GROUP BY [JOB-NO]) AS SH ON J.[JOB-NO] = SH.[JOB-NO]
LEFT JOIN
[RM-PRIORITY] IP ON J.[INITIAL-PRIORITY] = IP.[PRIORITY-CODE]
LEFT JOIN
[RM-PRIORITY] LP ON J.[INITIAL-PRIORITY] = LP.[PRIORITY-CODE]
LEFT JOIN
[IH-LOCATION] L ON J.[PLACE-REF] = L.[PLACE-REF]
LEFT JOIN
[IH-LOC-ATTR] LA ON J.[PLACE-REF] = LA.[PLACE-REF] AND LA.[ATTRIB-CODE] = 'SBU'
WHERE
J.[DATE-LOGGED] BETWEEN @DATE1 AND @DATE2
AND
J.[RAISED-BY] = @RAISEDBY
AND
LA.[SUB-ATTRIB-CODE] IN (@SBU)
AND
J.[LATEST-PRIORITY] IN (@Priority)
AND
L.[MGT-AREA] IN (@MGTAREA)
Now, @Date1, @Date2 and @RAISEDBY are all single value and work fine, but @SBU, @Priority and @MGTAREA are all multi-value. In all three cases, I've set the multivalue parameters to Allow Multiple Values, and in the textbox Action, I've set all three Parameters to split out the multiple values as suggested elsewhere: =SPLIT(JOIN(Parameters!SBU.Value, ","), ",")
However, when I select more than one value for @Priority or @MGTAREA, then the details report is blank. This is the first time I think I've done this and I'm wondering if I've done anything obvious wrong, or whether SSRS can even handle three multi-value parameters on the same drillthrough.
Any help gratefully accepted, I've been literally staring at this for most of a day!
