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:
=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!
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!
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.
ReplyDeleteI 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.
ReplyDeleteHello, 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
ReplyDeleteMany 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...
ReplyDeleteThank 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.
ReplyDeleteHi, 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.
ReplyDeleteDo you have an answer to Flgaro issue? I am having the same issue where in the subscription I get the error on Actual parameter not having a value so I can not set up the subscription. This is about the best solution I have found and would like to get this working.
ReplyDeleteHey, Jerry, let me help you with this. Have you set your Actual Parameter to this =Split(@ReportParameter, ",")?
ReplyDeleteAnd have you mapped your report ReportParameter to the column value from your subscription data query? If your hidden ReportParameter is not sourced properly (i.e. blank), it may result in emptiness of your ActualReportParameter as well.
I set the Default on the Actual parameter with the Split function. The hidden parameter is mapped with the column from my subscription data query.
DeleteI am assuming the Actual parameter is set to use the Report Default value in the subscription. Is this correct?
DeleteYes, for the Split function to the Actual parameter and yes for the hidden parameter to be linked with data query output column. In my case the query always returned one row.
DeleteOk, I found my error on the actual parameter where the split function is located.
DeleteConversion from type 'Object()' to type 'String' is not valid. I'll see if I can get that taken care of.
Yes, data types are important. Let me know, how it goes for you.
DeleteCouldn't get past the conversion issue and decided to move on. I was really hoping this would work but its taking up too much time, oh well.
DeleteI decided to play around with it a little bit more and I did get past the conversion issue. The report does exactly what it should when I run it but the subscription still gives the error about requiring a default. Maybe it is from the actual parameter cascading off another parameter, that seems to be a problem when we are doing data driven subscriptions with cascading parameters.
ReplyDeleteI would check your query that stays behind your data driven subscription; Also, sometimes I look at detailed SSRS logs helped to investigate what actually happen and what error I get. It's good that you didn't loose hope in this solution!
DeleteI'm also running into the same issues as Jerry and Flgaro.
ReplyDelete