FAQ: How do I pass a multi-value parameter into a report with SQL Server Reporting Services (SSRS) Web Services API?

Question 

How do I pass a multi-value parameter into a report with Reporting Services Web service API?

 Answer

 In Microsoft SQL Server Reporting Services (SSRS), in order to pass a multi-value parameter through the Reporting Services Web services, you need to define the same numbers of ParameterValue objects as the number of the values of the multi-value parameter being past into the report. The Name property of these ParameterValue objects must be specified same to the parameter name.

For example, you have a multi-value parameter named "ProductSubcategory" in a report, and now you want to pass the values 1, 2 and 3 to the parameter, you need to create a ParameterValue array which length is 3 and specify each one with the same Name property value.

Below is a complete sample for your reference:

 SRReportExecution2005.ReportExecutionServiceSoapClient rs = new SRReportExecution2005.ReportExecutionServiceSoapClient();

      rs.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

      // Render arguments

      byte[] result = null;

      string reportPath = "/AdventureWorks 2008 Sample Reports/Product Line Sales 2008";

      string format = "MHTML";

      string historyID = null;

      string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

      // Prepare report parameter.

      ParameterValue[] parameters = new ParameterValue[3];

     

      parameters[0].Name = "ProductCategory";

      parameters[0].Value = "1";

      parameters[1].Name = "ProductSubcategory";

      parameters[1].Value = "2"; // June

     

      parameters[2].Name = "ProductSubcategory";

      parameters[2].Value = "3";

      DataSourceCredentials[] credentials = null;

      string showHideToggle = null;

      string encoding;

      string mimeType;

      string extension;

      Warning[] warnings = null;

      ParameterValue[] reportHistoryParameters = null;

      string[] streamIDs = null;

      ExecutionInfo2 execInfo2 = new ExecutionInfo2();

      ExecutionHeader execHeader = new ExecutionHeader();

      ServerInfoHeader svrinfoHeader;

      execHeader = rs.LoadReport2(null, reportPath, historyID, out svrinfoHeader, out execInfo2);

      rs.SetExecutionParameters2(execHeader, null, parameters, "en-us", out execInfo2);

      String SessionId = execInfo2.ExecutionID.ToString();

      try

      {

        rs.Render(execHeader, null, format, devInfo, out result, out extension, out mimeType, out encoding, out warnings, out streamIDs);

      }

      catch (Exception exp)

      {

        //Throw the exception.

        throw (exp);

      }

 

More Information

 ReportExecution2005 Namespace: https://msdn.microsoft.com/en-us/library/reportexecution2005.aspx

Applies to

SQL Server Reporting Services 2005

SQL Server Reporting Services 2008

SQL Server Reporting Services 2008 R2