Programmatically Export SSRS report from sharepoint using ReportService2010.asmx

Question

I have to programmatically export ssrs reports to excel, added service refernce to

http:/siteurl/_vti_bin/ReportServer/ReportService2010.asmx

http:/siteurl/_vti_bin/ReportServer/ReportExecution2005.asmx

Can somebody provide a working blog

Answer 1

I do this for my work currently with VS 2012 .NET 4.5 for reporting automation for PDF reports.

A. For ease of use compiling your own proxy class is easier than referencing the web service each time as you may forget the service name.

From Visual Studio Command Prompt:

    wsdl /language:CS /n:"Microsoft.SqlServer.ReportingServices2010" http://<Server Name>/reportserver/reportservice2010.asmx?wsdl

reference: http://msdn.microsoft.com/en-us/library/ms155134(v=sql.105).aspx (You need services: ReportExecution2005 and ReportService2010 only if you want to get info and execute. If you only want to render you just need ReportExecution2005)

B. Once you create your proxy class put it in a library project for reuse IMHO. Build maybe some wrapper classes to do the writing and some servers in a config file that you may want to reference if you have multiple environments.

C. Write some code referencing the library and build a first time report in C#:

    using System;
    using System.IO;
    using System.Web.Services.Protocols;
    using myNamespace.MyReferenceName;  // YOUR PROXY PROJECT 

    class Sample
    {
    static void Main(string[] args)
    {
        ReportExecutionService rs = new ReportExecutionService();
        rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
        rs.Url = "http://myserver/reportserver/ReportExecution2005.asmx";

        // Render arguments
        byte[] result = null;
        string reportPath = "/AdventureWorks Sample Reports/Employee Sales Summary";
        string format = "MHTML";
        string historyID = null;
        string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

        // Prepare report parameter.
        ParameterValue[] parameters = new ParameterValue[3];
        parameters[0] = new ParameterValue();
        parameters[0].Name = "EmpID";
        parameters[0].Value = "288";
        parameters[1] = new ParameterValue();
        parameters[1].Name = "ReportMonth";
        parameters[1].Value = "6"; // June
        parameters[2] = new ParameterValue();
        parameters[2].Name = "ReportYear";
        parameters[2].Value = "2004";

        DataSourceCredentials[] credentials = null;
        string showHideToggle = null;
        string encoding;
        string mimeType;
        string extension;
        Warning[] warnings = null;
        ParameterValue[] reportHistoryParameters = null;
        string[] streamIDs = null;

        ExecutionInfo execInfo = new ExecutionInfo();
        ExecutionHeader execHeader = new ExecutionHeader();

        rs.ExecutionHeaderValue = execHeader;

        execInfo = rs.LoadReport(reportPath, historyID);

        rs.SetExecutionParameters(parameters, "en-us"); 
        String SessionId = rs.ExecutionHeaderValue.ExecutionID;

        Console.WriteLine("SessionID: {0}", rs.ExecutionHeaderValue.ExecutionID);


        try
        {
            result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

            execInfo = rs.GetExecutionInfo();

            Console.WriteLine("Execution date and time: {0}", execInfo.ExecutionDateTime);


        }
        catch (SoapException e)
        {
            Console.WriteLine(e.Detail.OuterXml);
        }
        // Write the contents of the report to an MHTML file.
        try
        {
            FileStream stream = File.Create("report.mht", result.Length);
            Console.WriteLine("File created.");
            stream.Write(result, 0, result.Length);
            Console.WriteLine("Result written to the file.");
            stream.Close();
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }

    }
    }

Taken from here: http://msdn.microsoft.com/en-us/library/reportexecution2005.reportexecutionservice.render(v=sql.105).aspx

D. (optional): You may want to handle refreshing the WebService on SSRS Hosting Server(s). The default is that it recycles every 12 hours thus making the first report render after that slow. You can refresh these services with simply making a webcall every 10 hours or so to the service endpoint at http:// (servername)/ReportServer. I use a tool called Visual Cron that can sets up automated tasks, you can also try to change the SSRS service config, create your own keep alive service, etc. Basically you just need to either change the setting or else talk to it to keep it open.