Search This Blog

Thursday, August 28, 2014

SSRS report: Multi-value parameter going after Data-driven subscription

It is always good to enrich SSRS reports with multi-value parameters; users have more flexibility to combine different reporting scenario with no need to run a report for different parameter values separately. On the other hand, developers can craft their skills to embed multi-value parameters into report datasets; referencing different rows, labels and values from those parameters into some custom controls within reports is another competence which can also be quickly obtained.

Alright, report is developed and deployed for users. They requested to create report subscriptions and now enjoy luxury of scheduled results delivery in different formats. Report Manager in Native mode and SharePoint in SSRS Integrated mode read report parameters metadata pretty well and allow setting new values for multi-value parameters or keep default settings.

However when you’re dealing with data-driven subscription, it’s not that easy to pass data query values for multi-value parameters. You have a choice:
1) To be happy with reports default values and change nothing in report subscription 
2) You can still be happy and create regular subscription with manually setting parameter values
3) You can programmatically define parameter values using CreateDataDrivenSubscription method of the ReportingService assembly
4) Also, you can be crazy enough and built set of XML parameters tags in Parameters column and update the dbo.Subscriptions table of your Report Server instance.

But still, it doesn't let to link a data element from data-driven subscription query with your multi-value parameter; how would you pass several values in one columns of a single record. And I think you’ve already guessed one of the right answers: delimited string of values!

Here is how I’ve made it working for a project, where my client wanted to trigger report subscriptions from their web UI and each SSRS report executions could have different multi-value parameters values.

1) I've created two multi-value parameters in my SSRS reports:
        a. ReportParameter; made it Hidden, Available Values – None, Default Values – Specified values for just 2 or 3 lines for my reports (number of them is not really important at this moment)
        b. ReportParameterActual; kept it Visible, Available Value – Query from a dataset, Default values – Specify values with the following expression:
=Split(@ReportParameter, ",")
2) Once this report was deployed, I’ve created a data driven subscription where my data query looked similar to this: select ReportParameter = ‘Value1,Value2,Value3’ from MyTable
3) And then I mapped ReportParameter from the report with the ReportParameter column from this query.

This was the whole solution to the problem. ReportParameterActual was visible parameter for users and they could interact and set different values themselves beyond the subscriptions definition. ReportParameter was the actual interface and gateway for default values (which with data-driven subscription now became a dynamic control on set of values that would be supplied to multi-value ReportParameter (i.e. ReportParameterActual) in different subscription scenarios). And Split(@ReportParameter, ",") was the whole trick :-)

Update (2014-09-03)Richard D'Angelo asked me to share the RDL with the solution. I couldn't upload the actual client report, but I've made a sample one based on the [AdventureWorksDW2014].[dbo].[DimGeography] table. You can get this sample through this link - MultiValueParameterReport.rdl.

Happy data adventures!

6 comments:

  1. Great post! I've run into a snafu, though. I'm using a SharePoint List as a basis for my data driven subscription, which means I'm using Query Designer to generate the XML for the query. Any ideas on how to modify the generate XML to point to the multiselect/value parameter, ReportParameter? Any insight would be appreciated.

    ReplyDelete
  2. I don’t think you will need to modify anything in XML for your queries (I haven’t worked much with XML in SSRS). But I believe that once you finish defining your XML dataset, then it will be the same record based recordset that you can make a reference in your parameters’ settings with columns names and their values. So the approach if using the Split function should work as well.

    ReplyDelete
  3. Hello, Rayis! Could you please provide more details on what do you mean by "3) And then I mapped ReportParameter from the report with the ReportParameter column from this query."? I'm getting the error "This report requires a default or user-defined value for the report parameter 'ContentTypeList'. To run or subscribe to this report, you must provide a parameter value. (rsReportParameterValueNotSet)" and it seems related to that i haven't dome #3 properly

    ReplyDelete
  4. Many thanks - one thing to add is that the values are CaSe SeNsitive - I ran into trouble trying to match SouthEast with Southeast...took me a while to see it, as all developers are blind...

    ReplyDelete
  5. Thank you for the great post. I can't view the example .rdl file you posted. Also, can you please include the data driven subscription SQL you used? I have a table that stores the values as it can change per customer. So in your example step 2) "select ReportParameter = ‘Value1,Value2,Value3’ from MyTable" I am selecting column1 that already stores the values I need. In your example it would store Value1,Value2,Value3 in column1. My query would then Select Column1 from MyTable and I need to parse column1. I have it working when I run my report it is just the data driven subscription generation that it is failing on.

    ReplyDelete
  6. Hi, I've just checked the link for the RDL file, it's still available. Please let me know and I can email it to you if you like. Regarding your Column1 case which should have values in a string separated by ','; then you don't need to parse anything, the Split function should do all the text parsing for you. Let me know if you're still confused.

    ReplyDelete