Posted on Feb 26, 2010

How to return a SQL Report as a PDF/Excel/CSV file from an ASP.Net MVC controller

SQL Reports is a wonderful tool for creating and publishing enterprise reports, yet it’s Web Forms viewer has it’s drawbacks such as requiring an Active-X control for printing and it’s not available in ASP.Net MVC.

A good solution to utilizing SQL Reports in an ASP.Net MVC application is to use the SQL Reports Web Services API to render the report as a PDF, Excel, or CSV file and to have your action method return the rendered report as a Filestream to the browser.

The source code for this project is available on a Subversion server here:  http://code.yanachik.org/public/MVCSQLReportDemo/

The steps involved are:

  1. Create a Web Reference to the SQL Reports Report Execution Web Service.
  2. Create an Action Method that returns a FileStreamResult.
  3. Create a proxy to the Web Service and set the credentials.
  4. Render the report.
  5. Return the report as a file stream.

Create a Web Reference to the SQL Reports Report Execution Web Service

In your ASP.Net MVC project add a Web-Reference to the Report Execution Web Reference to the following URL:  http://REPORTSERVER/ReportServer/ReportExecution2005.asmx and name it rsExecService.  Even if you’re using SQL Reports 2008, the Web Reference is still set for 2005.

To do this, right-click on the project in Visual Studio and select Add Web Reference…  The “Add Web Reference” dialog will appear.  Fill in the URL to your SQL Reports Report Execution Web Service and hit enter or click on the Go button.  Once the Web Service is found you fill out the Web Reference Name with rsExecService and click the Add Reference button.

AddingWebReference

Create an Action Method that returns a FileStreamResult

[System.Web.Mvc.OutputCache(NoStore = true, Duration = 0, VaryByParam = "*")]
public FileStreamResult PrintReport(string id)
{

}

The System.Web.Mvc.OutputCache attribute tells your browser to not cache the report.  If you have static data you can remove this attribute.

Create a proxy to the Web Service and set the credentials

// Create a new proxy to the web service
rsExecService.ReportExecutionService rsExec = new rsExecService.ReportExecutionService();

// Authenticate to the Web service using Windows credentials
rsExec.Credentials = new System.Net.NetworkCredential("USERNAME", "Password", "DOMAIN");

Render the report

When loading the report, the file name is the full path for the report.  In this case “Test Report” is at the root of the Report Server.  If it was in a folder then the path would be “/Folder 1/Folder 2/Report Name”.

// Load the selected report.
rsExec.LoadReport("/Test Report", null);

// Prepare Render arguments
string encoding = String.Empty;
string mimeType = String.Empty;
string extension = String.Empty;
rsExecService.Warning[] warnings = null;
string[] streamIDs = null;

// Prepare report parameters.
rsExecService.ParameterValue[] parameters = new rsExecService.ParameterValue[1];
parameters[0] = new rsExecService.ParameterValue();
parameters[0].Value = id;
parameters[0].Name = "id";

// Set the parameters for the report needed.
rsExec.SetExecutionParameters(parameters, "en-us");

//Render the report to a byte[]
Byte[] byteInfo = rsExec.Render("PDF", null, out extension, out encoding, out mimeType, out warnings, out streamIDs);

Return the report as a file stream. 

System.IO.MemoryStream ms = new System.IO.MemoryStream();
ms.Write(byteInfo, 0, byteInfo.Length);
ms.Position = 0;

HttpContext.Response.AddHeader("content-disposition", "attachment; filename=FILENAME.pdf");

return new FileStreamResult(ms, "application/pdf");

The final Action Method looks like this:

[System.Web.Mvc.OutputCache(NoStore = true, Duration = 0, VaryByParam = "*")]
public FileStreamResult PrintReport(string id)
{
    // Create a new proxy to the web service
    rsExecService.ReportExecutionService rsExec = new rsExecService.ReportExecutionService();

    // Authenticate to the Web service using Windows credentials
    rsExec.Credentials = new System.Net.NetworkCredential("USERNAME", "Password", "DOMAIN");

    // Load the selected report.
    rsExec.LoadReport("/Test Report", null);

    // Prepare Render arguments
    string encoding = String.Empty;
    string mimeType = String.Empty;
    string extension = String.Empty;
    rsExecService.Warning[] warnings = null;
    string[] streamIDs = null;

    // Prepare report parameters.
    rsExecService.ParameterValue[] parameters = new rsExecService.ParameterValue[1];
    parameters[0] = new rsExecService.ParameterValue();
    parameters[0].Value = id;
    parameters[0].Name = "id";

    // Set the parameters for the report needed.
    rsExec.SetExecutionParameters(parameters, "en-us");

    //Render the report to a byte[]
    Byte[] byteInfo = rsExec.Render("PDF", null, out extension, out encoding, out mimeType, out warnings, out streamIDs);

    System.IO.MemoryStream ms = new System.IO.MemoryStream();
    ms.Write(byteInfo, 0, byteInfo.Length);
    ms.Position = 0;

    HttpContext.Response.AddHeader("content-disposition", "attachment; filename=FILENAME.pdf");

    return new FileStreamResult(ms, "application/pdf");
}