SSRS Data Driven subscriptions for Multiselects

1. SSRS: You need to create a new string parameter (ie. ‘ProductCategoryValueSTRING’) and set the initial default value to the initial multiselect parameters the user should have selected  (eg. [Product].[ATC1].&[L],[Product].[ATC1].&[N])
2. SSRS: Make sure this new parameter from step 1 is above the multiselect parameter in the SSRS report and it is hidden as the user shouldn’t see this new parameter.
3. SQL: You need to create the stored procedure that is below in the db that the report connects to e.g. WebWrapperConfig (It basically converts a comma string into a table which is what the multiselect parameter needs).
4. SSRS: You then need to create a new dataset calling this procedure and setting the parameter of the procedure to parameter created in step 1 (ie. ‘ProductCategoryValueSTRING’).
5. SSRS: Point the default value of the multi select parameter (ie. ‘ProductCategoryValue’)  to the dataset you just created and using the value column ‘MultiParameterID’
6. DDS: In the data driven subscription point your multiselect column in SQL (ie.NEC1) to parameter created in (ie. ‘ProductCategoryValueSTRING’).
7. DDS: Make sure the original multiselect parameter is set to default.
8. DDS: Finally, run all your dds subscriptions for this report, try the combinations in the report and check in the ReportServer log db that the combinations you are selecting are using the cached versions.

-- =============================================

-- Description: Multiselect parameter generation SP

-- EXEC [dbo].[WrapperTool_Multiselect] '[Product].[ATC1].&[L],[Product].[ATC1].&[N]'

-- =============================================

CREATE PROCEDURE [dbo].[WrapperTool_Multiselect]

(

@ParameterOutput nvarchar(max)

)

AS

BEGIN

       SET NOCOUNT ON;

SELECT Split.a.value('.', 'VARCHAR(MAX)') AS MultiParameterID 

 FROM 

 (

     SELECT P, 

         CAST ('<M>' + REPLACE(REPLACE(P, ',', '</M><M>'),'&','&amp;') + '</M>' AS XML) AS Data 

     FROM  (SELECT @ParameterOutput as P) as b

) AS A CROSS APPLY Data.nodes ('/M') AS Split(a);

END

Output E.g. of EXEC [dbo].[WrapperTool_Multiselect] '[Product].[ATC1].&[L],[Product].[ATC1].&[N]'

*Note: When the report first runs it uses the default parameters you have set to pass to the multiselect parameter, afterwards it will pick up the parameters in the multiselect to filter that the user has selected so the web wrapper will work fine with this approach and you don’t have to change anything in it.

Also, just as a tip, You should try and remove the [Product].[ATC1].&[ ] from the string so it starts like L,N and build up the full MDX id in the stored procedure back to [Product].[ATC1].&[L],[Product].[ATC1].&[N] as there is a max string limit you might face if you are trying to generate dds subscription for 30 or 40 combinations at once. (If you don’t need a dds subscription of more than 20 or so combinations this probably won’t be an issue).