Programmatically Exporting Reports from SQL 2012 Reporting Services

Question

I have a console application that needs to connect to SQL Reporting Services on SQL Server Express 2012. All export logic must be implemented in a separate DLL, and the paths must be dynamic (I perform various settings for different servers / reports and export them to Excel one after another).

I tried to follow these tutorials:

http://www.aspose.com/docs/display/wordsreportingservices/Rendering+Reports+Programmatically

http://blogs.msdn.com/b/selvar/archive/2010/12/13/accessing-the-reportexecutionservice-render-method-when-reporting-service-2008-r2-is-in-forms-based- authentication.aspx

Basically, I added web links:

http://localhost:80/ReportServer_SQLEXPRESS12/ReportExecution2005.asmx 

and

 http://localhost:80/ReportServer_SQLEXPRESS12/ReportService2010.asmx 

into my dll. It looks good so far, except for those nasty app.config settings (I will have to dynamically configure them later).

Then I tried to do as in the example:

 // Create Web service proxies. ReportingService2010.ReportingService2010 rs = new ReportingService2010.ReportingService2010(); ReportExecutionService.ReportExecutionService rsExec = new ReportExecutionService.ReportExecutionService(); 

and got some problems:

 Error 76 The type or namespace name 'ReportExecutionService' does not exist in the namespace 'MyDllNamespace.ReportExecutionService' (are you missing an assembly reference?) Error 74 The type or namespace name 'ReportingService2010' does not exist in the namespace 'MyDllNamespace.ReportingService2010' (are you missing an assembly reference?) 

Now, where should I go further, how to use the Reporting Services API, if I can’t even create a proxy object? Or am I better off using the ServerReport class for the Winforms ReportViewer form instead of these web links?

Even one of Microsoft's examples uses ReportViewer in a console application, but it seems a little inconvenient to import Winforms in a console application.

Answer 1

I hope the following helps (excerpt from the relevant parts of the code)

  using (ZUtilities.SSRS.Report report = new ZUtilities.SSRS.Report { ReportServerPath = VParameter.GetValue("SSRS_WebServiceUrl", _repo.Parameters).ToString(), Format = rformat, ReportPath = "some_path_on_ssrs_server" }) { report.Params.Add("Id", id.ToString()); report.Credentials = nwc; MemoryStream ms = new MemoryStream(); report.Render().CopyTo(ms); FileContentResult fsr = new FileContentResult(ms.ToArray(), rctype); fsr.FileDownloadName = String.Format("EPV-{0}-{1:yyyyMMdd}.{2}", epv.ExternalReference, DateTime.Now, fext); ms.Close(); return fsr; } 

with the following (note flow control)

 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Net; using System.IO; namespace ZUtilities.SSRS { public enum ReportFormats { Html = 1, MHtml, Pdf, Xlsx, Docx } public class ReportFormat { static ReportFormat() { Html = new ReportFormat { Code = ReportFormats.Html, Instruction = "HTML4.0" }; MHtml = new ReportFormat { Code = ReportFormats.MHtml, Instruction = "MHTML" }; Pdf = new ReportFormat { Code = ReportFormats.Pdf, Instruction = "PDF" }; Xlsx = new ReportFormat { Code = ReportFormats.Xlsx, Instruction = "EXCELOPENXML" }; Docx = new ReportFormat { Code = ReportFormats.Docx, Instruction = "WORDOPENXML" }; } private ReportFormat() { } public ReportFormats Code { get; set; } public String Instruction { get; set; } public static ReportFormat Html { get; private set; } public static ReportFormat MHtml { get; private set; } public static ReportFormat Pdf { get; private set; } public static ReportFormat Xlsx { get; private set; } public static ReportFormat Docx { get; private set; } public static ReportFormat ByCode(ReportFormats code) { switch (code) { case ReportFormats.Html: return Html; case ReportFormats.MHtml: return Html; //<<====================== case ReportFormats.Pdf: return Pdf; case ReportFormats.Xlsx: return Xlsx; case ReportFormats.Docx: return Docx; default : return null; } } } public class Report : IDisposable { private HttpWebRequest _httpWReq; private WebResponse _httpWResp; public Report() { _httpWReq = null; _httpWResp = null; Format = ReportFormats.Html; Params = new Dictionary<String, String>(); } public Dictionary<String, String> Params { get; set; } public String ReportServerPath { get; set; } public String ReportPath { get; set; } public ReportFormats Format { get; set; } public NetworkCredential Credentials { get; set; } //public String PostData { get { return String.Format("rs:Command=Render&rs:Format={0}", ReportFormat.ByCode(Format).Instruction); } } public String PostData { get { StringBuilder sb = new StringBuilder(1024); sb.AppendFormat("rs:Command=Render&rs:Format={0}", ReportFormat.ByCode(Format).Instruction); if (Format == ReportFormats.Html) { sb.Append("&rc:Toolbar=false"); } foreach (var kv in Params) { sb.AppendFormat("&{0}={1}", kv.Key, kv.Value); } return sb.ToString(); } } public String ReportFullPath { get { return ReportServerPath + "?/" + ReportPath; } } public Stream Render() { _httpWReq = (HttpWebRequest)HttpWebRequest.Create(ReportFullPath); _httpWReq.Method = "POST"; if (Credentials != null) _httpWReq.Credentials = Credentials; byte[] byteArray = Encoding.UTF8.GetBytes(PostData); _httpWReq.ContentType = "application/x-www-form-urlencoded"; _httpWReq.ContentLength = byteArray.Length; Stream dataStream = _httpWReq.GetRequestStream(); dataStream.Write(byteArray, 0, byteArray.Length); dataStream.Close(); if (_httpWResp != null ) _httpWResp.Close(); _httpWResp = _httpWReq.GetResponse(); return _httpWResp.GetResponseStream(); } public void RenderTo(String fileName) { Stream receiveStream = Render(); Stream ds = File.Open(fileName, FileMode.Create); receiveStream.CopyTo(ds); ds.Close(); receiveStream.Close(); } public void Dispose() { if (_httpWResp != null) { _httpWResp.Close(); _httpWResp = null; } if (_httpWReq != null) { _httpWReq = null; } } } }