Link to home
Start Free TrialLog in
Avatar of D B
D BFlag for United States of America

asked on

SSRS: Pass values for multi-select parameter in batch job

We have a number of reports that run out of our batch system behind data loads. There have been some recent updates. Previously, we have only one account type, so the parameter was just a single-value text value. We now use a multi-select parameter, and am getting an error when we attempt to run in batch although it runs properly through the web interface. I've captured the command line from our batch system:
\\xxx.com\share\CM\DEV\OPS\Apps\SSRSReportBuild\SSRSReportBuild.exe "Report=Solutions/ZZ-XX-12345-AccountAuditReport" "FileName=FCT-ZZ-XX-12345-AccountAuditReport.rdl" "MaxRunTime=1800" "Path=\\rpt2webtest\MFTest" "pAcctType=ABC,XYZ" "pAcctTypeList=ABC,XYZ" "pRunDate=20190906" "pRunType=D" "Render_Format=EXCEL"

Open in new window

When executed, the job fails with this error:
System.Web.Services.Protocols.SoapException: This report requires a default or user-defined value for the report parameter 'pAcctType'. To run or subscribe to this report, you must provide a parameter value.

Open in new window

Any suggestions of how to correct this and properly pass the value for pAcctType, a multi-value parameter?

Some of the questions that might come up
  • The source of available values for the multi-select parameter in the report is a SQL query.
  • The values 'ABC' and 'XYZ' do exist in the data.
  • We cannot pass the same parameter name twice on the command line, as the API does not allow this and will generate and error (I mention this becasue some web searching I've done indicate that is how you pass multi-select values in the web interface (e.g. &pAcctType=ABC&pAcctType=XYZ...)
Avatar of John_Vidmar
John_Vidmar
Flag of Canada image

Add dataset parameter-name @pAcctType, with parameter-value =split(Parameters!pAcctType.Value,",")

Alter the SQL to change from equality to an in-clause: WHERE AcctType IN (@pAcctType)
ASKER CERTIFIED SOLUTION
Avatar of D B
D B
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial