Programmatically exporting reports from SQL 2012 Reporting Services

Question

I have a console application which needs to connect to SQL Reporting Services on SQL Server Express 2012. All the exporting logic should be implemented in a separate dll, and the paths should be dynamic (I have loop through various settings for various servers/reports and export them to Excel one by one).

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 references:

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

and

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

to my dll. Looks good so far, except those nasty app.config settings (I'll have to adjust them dynamically 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 troubles:

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 do I go next, how do I use the Reporting Services API, if I cannot even create a proxy object? Or should I better use ServerReport class form the Winforms ReportViewer instead of these web references?

Even one of Microsoft examples is using ReportViewer in a console application, but it seems a bit awkward to import Winforms in a console app.

Answer 1

I hope the following will help (extract of pertinent parts of 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 (be carefull to the stream management)

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;
            }
        }
    }
}