September 10, 2019 at 5:27 pm
I have a multi-value parameter on a report, and I cannot figure out how to pass multiple values in a subscription parameter, i.e., I pass this value in parameter partner_list;
57590,12100
which produces this error;
library!WindowsService_105!1f64!09/10/2019-12:36:40:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: , Microsoft.ReportingServices.Diagnostics.Utilities.InvalidReportParameterException: Default value or value provided for the report parameter 'partner_list' is not a valid value.
thank you, beth
September 11, 2019 at 11:01 am
Have you tried '57590,12100' with the quotes?
September 11, 2019 at 1:01 pm
Yes. A single value works, but not a list. The report has several independent parameters, plus two that are in a cascade:
End Date > Practice Groups > Employees
I haven't tried it, but if Practice Groups and Employees weren't in a cascade, the comma-delimited list might work.
There is one more technique I'm going to try, but I don't have much hope it will work.
thank you, beth
September 11, 2019 at 10:53 pm
Well, the "one more technique" did not work;
instead of AM,BEN, etc.,
I tried <ParameterValue><Name>pg_list</Name><Value>AM</Value></ParameterValue><ParameterValue><Name>pg_list</Name><Value>BEN</Value></ParameterValue> etc.
I'm out of ideas on how to get this to work dynamically. So ...
1. In VS, I ended up setting Default Values on all parameters up to and including PGs and Employees, then deployed it to the RM server.
2. Created a linked report from it.
3. Went back to the linked-to report, then Manage > [report] > Parameters, cleared the "Use default" checkbox. This is to prevent the report from running automatically when a user clicks on it; some of these reports take 5+ minutes to run, and it would be nasty to force the user to click cancel if they didn't want to run it for everyone.
4. Created the subscription on the linked report, where I set the "Source of value" on the two parameters in Report parameters to "Use default value", which I hope will be refreshed with the latest values each time the subscription runs. If that does not prove to be the case, I'm screwed.
This solution works, but is not stable - changes to the linked-to report might break the linked report and/or the subscription on the linked report.
MS, IMO, has fallen flat on its face with this - once again, they are blind to, or don't care about, obvious capabilities & features that, because they are absent, cause customers to do a lot of head-banging to implement some other way. If they did not have us by the short hairs, they would quickly go out of business.
thank you, beth
September 11, 2019 at 11:20 pm
Have you tried getting it to work using SSRS join? That's how I've typically passed multiple values and that's what the SSRS join is for. I haven't played with it when using cascading parameters but I suspect there is a way to do it. There are quite a few examples of using this approach. Here is one with data driven subscriptions, since that can be a bit different:
SSRS – Passing multiple value parameters in data driven subscriptions
This article has some interesting tips about multiple value parameters, noting how just a stored procedure may not work well:
Passing multiple values for a single parameter in Reporting Services
Try working with join and see if it works for you.
Sue
September 11, 2019 at 11:58 pm
I'd seen the first article earlier this week; while it might alleviate some of the issues I've had, the lack of flexibility remains - until/unless MS makes the subscription process smart enough to recognize a dynamic list of values passed via a data driven dataset, it will always be a head-banging experience. The solution in the article would not give me a dynamic way to, for example, create a subscription for only a selected list of PG's.
Not sure how the second article applies - there's no mention of subscriptions.
thank you Sue, beth
September 17, 2019 at 4:51 pm
Update, if anyone is interested. I could not get SSRS – Passing multiple value parameters in data driven subscriptions to work for my reports; cards on the table, I wasn't crazy about the idea of having to mutilate my reports to get them to work, so perhaps that explains why I could not get my head around how to do it. I'm still steamed that MS didn't handle this properly in the first place.
My solution, based on the above article, was to create a "shell" report for each of the original reports, with all the parameters of the original report, inserting a subreport pointing to the original. The two parameters that are multi-value in the original reports are not multi-value in the shell reports. I add a subscription to the shell reports, and pass the non-multi-value parameters to the subreport using Split().
thank you, beth
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply