Executing Reporting Services Web Service from ASP.NET MVC Using WCF “Add Service Reference”

Last week, I needed to call the SQL Reporting Services Web Service to export reports as Excel, PDF, and Word formats.
I could have used the [Add Web Reference…] as the MSDN Documentation Accessing the Report Server Web Service Using Visual Basic or Visual C#, but I decided to use [Add Service Reference…].

If you are wondering what the difference is between Web Reference and Service Reference, please read this blog post by Andrew Tokeley.

Goal

Create a common library project which I could reuse in all of my other projects to call the Reporting Services Web Service API to export reports to various formats

Reason

I am using the Reporting Services Web Service because the Reporting Server is behind the firewall and not visible to the outside.
Only my ASP.NET MVC web application front-end is visible to the outside world.

Step 1: Create a Class Library Project with “Add Service Reference”

Create a new Class Library project called ReportingServiceClientLib.
Right-Mouse-Click on References and choose Add Service Reference.

3. If you want the generated code to have asynchronous methods, you can do so by clicking on the Advanced button.

4. Once you click OK, Visual Studio will generate ReportExecutionServiceSoapClient class and other necessary classes.
Additionally, App.config file will also be generated for the WCF Configuration Settings.

Step 2: How to Use ReportExecutionServiceSoapClient Class

Okay, when we call the Web Service, we have to do the following tasks.

Specify which Report we want to run.
Pass in Domain Account credentials for the Web Service call since we are in the Active Directory Domain.

Use the class and the enum below as our report execution utility class which will be using the Visual Studio generated ReportExecutionServiceSoapClient class.
The comments I have in it should be more than enough explanation what it does and how the parameters are used.

using System;

using ReportingServiceClientLib.ReportingWebService;

namespace ReportingServiceClientLib {
#region enum ExportFormat
  /// <summary>
  /// Export Formats.
  /// </summary>
  public enum ExportFormat {
    /// <summary>XML</summary>
    XML,
    /// <summary>Comma Delimitted File
    CSV,
    /// <summary>TIFF image</summary>
    Image,
    /// <summary>PDF</summary>
    PDF,
    /// <summary>HTML (Web Archive)</summary>
    MHTML,
    /// <summary>HTML 4.0</summary>
    HTML4,
    /// <summary>HTML 3.2</summary>
    HTML32,
    /// <summary>Excel</summary>
    Excel,
    /// <summary>Word</summary>
    Word
  }
#endregion

  /// <summary>
  /// Utility class that renders and exports a SQL Reporting Services report
  /// into the specified output format.
  /// </summary>
  public static class ReportExporter {
#region Method : GetExportFormatString(ExportFormat f)
    /// <summary>
    /// Gets the string export format of the specified enum.
    /// </summary>
    /// <param name="f">export format enum</param>
    /// <returns>enum equivalent string export format</returns>
    private static string GetExportFormatString(ExportFormat f) {
      switch (f) {
        case ExportFormat.XML:
          return "XML";
        case ExportFormat.CSV:
          return "CSV";
        case ExportFormat.Image:
          return "IMAGE";
        case ExportFormat.PDF:
          return "PDF";
        case ExportFormat.MHTML:
          return "MHTML";
        case ExportFormat.HTML4:
          return "HTML4.0";
        case ExportFormat.HTML32:
          return "HTML3.2";
        case ExportFormat.Excel:
          return "EXCEL";
        case ExportFormat.Word:
          return "WORD";

        default:
          return "PDF";
      }
    }
#endregion

#region Method : Export(...)
    /// <summary>
    /// Exports a Reporting Service Report to the specified format using Windows
    /// Communication Foundation (WCF) endpoint configuration specified.
    /// </summary>
    /// <param name="wcfEndpointConfigName">WCF Endpoint name</param>
    /// <param name="clientCredentials">Network Credential to use to connect to
    /// the web service</param> <param name="report">Reporting Services Report
    /// to execute</param> <param name="parameters">report parameters</param>
    /// <param name="format">export output format (e.g., XML, CSV, IMAGE, PDF,
    /// HTML4.0, HTML3.2, MHTML, EXCEL, and HTMLOWC)</param> <param
    /// name="output">rendering output result in bytes</param> <param
    /// name="extension">output format file extension</param> <param
    /// name="mimeType">output MIME type</param> <param name="encoding">output
    /// encoding</param> <param name="warnings">warnings (if any)</param> <param
    /// name="streamIds">stream identifiers for external resources (images, etc)
    /// that are associated with a given report</param>
    public static void Export(string wcfEndpointConfigName,
                              System.Net.NetworkCredential clientCredentials,
                              string report, ParameterValue[] parameters,
                              ExportFormat format, out byte[] output,
                              out string extension, out string mimeType,
                              out string encoding, out Warning[] warnings,
                              out string[] streamIds) {
      using (var webServiceProxy =
                 new ReportExecutionServiceSoapClient(wcfEndpointConfigName)) {
        webServiceProxy.ClientCredentials.Windows.AllowedImpersonationLevel =
            System.Security.Principal.TokenImpersonationLevel.Impersonation;
        webServiceProxy.ClientCredentials.Windows.ClientCredential =
            clientCredentials;

        // Init Report to execute
        ServerInfoHeader serverInfoHeader;
        ExecutionInfo executionInfo;
        ExecutionHeader executionHeader = webServiceProxy.LoadReport(
            null, report, null, out serverInfoHeader, out executionInfo);

        // Attach Report Parameters
        webServiceProxy.SetExecutionParameters(
            executionHeader, null, parameters, null, out executionInfo);

        // Render
        webServiceProxy.Render(executionHeader, null,
                               GetExportFormatString(format), null, out output,
                               out extension, out mimeType, out encoding,
                               out warnings, out streamIds);
      }
    }
#endregion
  }
}

Step 3: Configuring the WCF Settings

So far, we still haven’t defined the WCF settings. We are going to use the WCF Service Configuration Editor from the Visual Studio’s Tool menu. Lets use it to edit the generated App.config.

Okay, I am going to be lazy and just copy and paste my WCF config section.
Just replace your App.config file section with the following one.
You can play with the WCF Configuration Editor yourself to understand what it does.

Please note that my config file limits the Message Size to be 5MB or less.

<system.serviceModel>
	<services />
	<bindings>
		<basicHttpBinding>
			<!-- maxReceivedMessageSize ===> 5242880 bytes ===> 5MB (Megabytes)
			<binding name="basicHttpBindingConfig" allowCookies="true" maxReceivedMessageSize="5242880">
				<security mode="TransportCredentialOnly">
					<transport clientCredentialType="Ntlm" proxyCredentialType="None" realm="" />
					<message clientCredentialType="UserName" algorithmSuite="Default" />
				</security>
			</binding>
		</basicHttpBinding>
	</bindings>

	<client>
		<endpoint address="http://localhost/ReportServer/ReportExecution2005.asmx?wsdl"
			binding="basicHttpBinding" bindingConfiguration="basicHttpBindingConfig"
			contract="ReportingWebService.ReportExecutionServiceSoap" name="basicHttpEndpoint" /> <!-- the WCF endpoint name in RED is important -->
	</client>
</system.serviceModel>

The above configuration section will be going into the Web.config file of my ASP.NET MVC Web Application.

Step 4: Putting It All Together in an ASP.NET MVC File Download Action Method

Here is my MVC Action Method and how I pass in Report Parameters.

using System.Linq;
using ReportingServiceClientLib;
using ReportingServiceClientLib.ReportingWebService;

[HttpPost]
public FileContentResult ExportExcel(string lastName) {
  IList<ParameterValue> parameters = new List<ParameterValue>();
  parameters.Add(new ParameterValue { Name = "lastName", Value = lastName });

  byte[] output;
  string extension, mimeType, encoding;
  string reportName = "/ReportFolderName/MyReportName";

  ReportExporter.Export(
      "basicHttpEndpoint" /* name of the WCF endpoint from Web.config */,
      new NetworkCredential("userName", "secretPassword",
                            "activeDirectoryDomainName"),
      reportName, parameters.ToArray(), ExportFormat.Excel, out output,
      out extension, out mimeType, out encoding);

  //-------------------------------------------------------------
  // Set HTTP Response Header to show download dialog popup
  //-------------------------------------------------------------
  Response.AddHeader(
      "content-disposition",
      string.Format("attachment;filename=GeneratedExcelFile{0:yyyyMMdd}.{1}",
                    DateTime.Today, extension));
  return new FileContentResult(output, mimeType);

There you have it. That is how you call the Reporting Services Web Service using WCF from ASP.NET MVC Web Application.

Cheers,
Soe

This article is part of the GWB Archives. Original Author: Soe Tun

Replatforming Guide: Pros, Cons, and Impact

Deciding to replatform is no small feat; it’s like setting sails for new horizons with your digital presence. Weighing the

Cypress vs Selenium: Why Cypress is Better!

Navigating the competitive landscape of web testing tools, Cypress emerges as a noteworthy contender, outshining Selenium with its cutting-edge advantages.