Subscription - how to pass list of values?

  • beth-447786

    Old Hand

    Points: 359

    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

  • Cary Hower-563110

    SSChasing Mays

    Points: 629

    Have you tried '57590,12100' with the quotes?

  • beth-447786

    Old Hand

    Points: 359

    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

  • beth-447786

    Old Hand

    Points: 359

    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

  • Sue_H

    SSC Guru

    Points: 90287

    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

  • beth-447786

    Old Hand

    Points: 359

    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

  • beth-447786

    Old Hand

    Points: 359

    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 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply