Recently we had a project that requires us to use Reporting Server for email notification. In short, the HTML output of the Report is assigned to the body of the email notification.
1) First step is to create a proxy library utility containing reference to Reporting Server Web Service. To do this Click Add Service Reference and set url to:
http://{ReportServerName}/ReportServer/ReportExecution2005.asmx, In the collection type set it to System.Collections.Generic.List
Note: Version of SSRS is 2005
Update!: Problem with Authorization:
“The HTTP request is unauthorized with client authentication scheme ‘Negotiate’. The authentication header received from the server was ‘NTLM’.”
To fix problem with Authorization issue in LoadReport method:
1. Added new method called GetDefaultBinding.
2.Also a new parameter in Export method named useDefaultBinding with value set to false was added. If this value is set to true, it would use the binding from GetDefaultBinding and will use DefaultNetworkCredentials.
Note: Network credential is not being used when useDefaultBinding is set to true.
Updated code is as follows:
////// Utility class that renders and exports a SQL Reporting Services report into the specified output format. /// public static class ReportExporter { private static string GetExportFormat(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"; } } ////// Exports a Reporting Service Report to the specified format using Windows Communication Foundation (WCF) endpoint configuration specified. /// /// Address of Report Web Service /// Network Credential to use to connect to the web service /// Friendly name of Reporting Services Report to execute /// Reporting Services Report to execute /// report parameters /// /// rendering output result in bytes /// output format file extension /// output MIME type /// output encoding /// warnings (if any) /// stream identifiers for external resources (images, etc) that are associated with a given report /// public static void Export(string url, System.Net.NetworkCredential clientCredentials, string reportName, string report, ParameterValue[] parameters, ExportFormat format, out byte[] output, out string extension, out string mimeType, out string encoding, out Warning[] warnings, out string[] streamIds, DataSourceCredentials[] dataSourceCredentials=null, bool UseDefaultBinding=false) { Binding binding; if (UseDefaultBinding) { binding = GetDefaultBinding(); } else { binding = GetBinding(); } using (var webServiceProxy = new ReportExecutionServiceSoapClient(binding, new EndpointAddress(url))) { webServiceProxy.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation; if (UseDefaultBinding) { webServiceProxy.ClientCredentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials; } else { webServiceProxy.ClientCredentials.Windows.ClientCredential = clientCredentials; } // Init Report to execute ServerInfoHeader serverInfoHeader; ExecutionInfo executionInfo; try { ExecutionHeader executionHeader = webServiceProxy.LoadReport(null, report, null, out serverInfoHeader, out executionInfo); // Attach Report Parameters webServiceProxy.SetExecutionParameters(executionHeader, null, parameters, null, out executionInfo); //Set Credentials if(dataSourceCredentials!=null) webServiceProxy.SetExecutionCredentials(executionHeader, null, dataSourceCredentials, out executionInfo); // Render webServiceProxy.Render(executionHeader, null, GetExportFormat(format), null, out output, out extension, out mimeType, out encoding, out warnings, out streamIds); } catch (FaultException faultExc) { throw new Exception(string.Format("Encountered error while generating the report: '{0}', Report Url: '{1}', Web Service Url: '{2}' Message: '{3}'", reportName, report, url, faultExc.Message ), faultExc); } } } ////// Export a Reporting Services to HTML /// /// Address of Report Web Service /// Network Credential to use to connect to the web service /// Friendly name of Reporting Services Report to execute /// Reporting Services Report to execute /// report parameters /// ///public static string ExportToHTML(string url, System.Net.NetworkCredential clientCredentials, string reportName, string report, ParameterValue[] parameters, DataSourceCredentials[] dataSourceCredentials = null, bool UseDefaultBinding = false) { Warning[] warn; string[] p; byte[] output; string extension, mimeType, encoding; //Get HTML ReportExporter.Export(url, clientCredentials, reportName, report, parameters.ToArray(), ExportFormat.HTML4, out output, out extension, out mimeType, out encoding, out warn, out p, dataSourceCredentials, UseDefaultBinding ); System.Text.Encoding enc = System.Text.Encoding.GetEncoding("UTF-8"); string html = enc.GetString(output); return html; } /// /// Returns the binding to use, eliminates the app.config /// ///internal static Binding GetBinding() { BasicHttpBinding binding = new BasicHttpBinding(); binding.MaxBufferPoolSize = 0; binding.MaxReceivedMessageSize = 5242880; binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard; binding.TextEncoding = System.Text.Encoding.UTF8; binding.MessageEncoding = WSMessageEncoding.Text; binding.TransferMode = TransferMode.Buffered; binding.UseDefaultWebProxy = true; binding.ReaderQuotas = new System.Xml.XmlDictionaryReaderQuotas { MaxArrayLength = 2147483647, MaxBytesPerRead = 2147483647, MaxDepth = 2147483647, MaxNameTableCharCount = 2147483647, MaxStringContentLength = 2147483647 }; binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly; binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows; return binding; } /// /// Returns the binding to use, eliminates the app.config /// ///internal static Binding GetDefaultBinding() { BasicHttpBinding binding = new BasicHttpBinding(); binding.MaxBufferPoolSize = 0; binding.MaxReceivedMessageSize = 5242880; binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard; binding.TextEncoding = System.Text.Encoding.UTF8; binding.MessageEncoding = WSMessageEncoding.Text; binding.TransferMode = TransferMode.Buffered; binding.UseDefaultWebProxy = true; binding.ReaderQuotas = new System.Xml.XmlDictionaryReaderQuotas { MaxArrayLength = 2147483647, MaxBytesPerRead = 2147483647, MaxDepth = 2147483647, MaxNameTableCharCount = 2147483647, MaxStringContentLength = 2147483647 }; binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly; binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm; return binding; } #endregion }
Updated Unit Test to use default bindings:
[TestMethod]
public void GetHTMLReportTest()
{
//Set network credentials
string userName = "NotUsed";
string password = "NotUsed";
string domain = "NotUsed";
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential(userName, password, domain);
string outputPath = @"C:\TestFolder";
//Url
string path = "/MyReport/Invoice";
//Parameters
List parameters = new List();
parameters.Add(new ParameterValue { Name = "AcctId", Value = "0001" });
//Get HTML
string html = ReportExporter.ExportToHTML("http://HypV-Rpaulo/ReportServer/ReportExecution2005.asmx",
credentials,
"My invoice report",
path ,
parameters.ToArray(),UseDefaultBinding:true
);
Assert.AreEqual(true, html.Length > 0, "No HTML generated");
Console.WriteLine(html);
System.IO.File.WriteAllText(outputPath + "result.html", html);
}
The library can be used in BizTalk (deployed in GAC) , SSIS 2005 or 2008(deployed in GAC, .NET version is <=3.5 and is copied to %Windir%\Microsoft.NET\Framework\v2.0.50727 folder to be able to referenced in a SSIS Package..