http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/b704d524-09a2-43b3-b4f3-708b8def4dea
https://connect.microsoft.com/SQLServer/feedback/details/560911/sql-2008-r2-reportexecutionservice2005-broken-with-image-emf
http://social.msdn.microsoft.com/Forums/en-SG/sqlreportingservices/thread/e3367f6a-e76d-4567-bd11-dafe5a3b2a5a
I have updated the code samples in this article to reflect the new approach.
We recently had to create a service to programmatically print some CRM 2011 reports hosted with SSRS on SQL 2008 R2. Our solution involves using the SSRS execution service to render the report, and then printing it using the System.Graphics.PrintDocument class.
This tutorial on the MSDN website shows the steps required to render reports in any supported format:
http://msdn.microsoft.com/en-us/library/ms154699.aspx
Here is a short post by someone else on rendering reports: http://geekswithblogs.net/stun/archive/2010/02/26/executing-reporting-services-web-service-from-asp-net-mvc-using-wcf-add-service-reference.aspx
SSRS exposes five services. The services called ReportService2005 and ReportService2006 have been deprecated and rolled into ReportService2010 service. This is not the service you want. This service is for server management. A seperate service called ReportExecution2005, located at http://{servername}/ReportServer/ReportExecution2005.asmx is used for executing reports. This service is detailed here: http://msdn.microsoft.com/en-us/library/reportexecution2005.reportexecutionservice.aspx The fifth service, ReportServiceAuthentication is used for authentication, but not in the context of this post.
I've wrapped some of the interaction with this service and the printing sub-system up in a few of my own classes. The first is a printing service, which will take the printer name and a renderer object as it's parameters, and deal with the actual printing:
public class PrintingService
{
private IReportRenderer reportRenderer;
private int currentPrintingPage;
private int lastPrintingPage;
private RenderedReport renderedReport;
public void Print(bool isLandscape, string printerName, IReportRenderer renderer)
{
reportRenderer = renderer;
lock (reportRenderer)
{
renderedReport = renderer.RenderReport();
PrintDocument printDocument = new PrintDocument();
printDocument.DefaultPageSettings.Landscape = isLandscape;
printDocument.PrinterSettings.MaximumPage = renderedReport.PageCount;
printDocument.PrinterSettings.MinimumPage = 1;
printDocument.PrinterSettings.PrintRange = PrintRange.SomePages;
printDocument.PrinterSettings.FromPage = 1;
printDocument.PrinterSettings.ToPage = renderedReport.PageCount;
printDocument.PrinterSettings.ToPage = renderedReport.PageCount;
printDocument.PrinterSettings.PrinterName = printerName;
currentPrintingPage = 1;
lastPrintingPage = renderedReport.PageCount;
printDocument.PrintPage += PrintDocumentOutputRequired;
printDocument.Print();
}
}
private void PrintDocumentOutputRequired(object sender, PrintPageEventArgs ev)
{
ev.HasMorePages = false;
if (currentPrintingPage <= lastPrintingPage)
{
reportRenderer.DrawPage(ev.Graphics, currentPrintingPage, renderedReport);
if (++currentPrintingPage <= lastPrintingPage)
{
ev.HasMorePages = true;
}
}
}
}
public class RenderedReport
{
public byte[][] Pages { get; set; }
public int PageCount { get; set; }
public RenderedReport(int numberOfPages, byte[][] pages)
{
PageCount = numberOfPages;
Pages = pages;
}
}
The locks should ensure thread safety for these classes, but I haven't confirmed it yet.
You can use the following code to print the names of all the installed printers:
public void ShowAllInstalledPrinters()
{
PrinterSettings.InstalledPrinters.Cast<string>().ToList<string>().ForEach(Console.WriteLine);
}
The second is the rendering class, which will make the actual call to the service. This is the actual meat of the exercise.
public class ReportRenderer : IReportRenderer
{
private Metafile metafile;
private readonly string reportPath;
private readonly ParameterValue[] parameters;
private readonly string parameterLanguage;
private readonly NetworkCredential serviceCredentials;
private readonly DataSourceCredentials[] dataSourceCredentials;
public ReportRenderer(string reportPath, string parameterLanguage, NetworkCredential serviceCredentials, ParameterValue[] parameters = null, DataSourceCredentials[] dataSourceCredentials = null)
{
Argument.CheckIfNull(parameterLanguage, "parameterLanguage");
Argument.CheckIfNull(reportPath, "reportPath");
Argument.CheckIfNull(serviceCredentials, "serviceCredentials");
this.reportPath = reportPath;
this.parameters = parameters;
this.parameterLanguage = parameterLanguage;
this.serviceCredentials = serviceCredentials;
this.dataSourceCredentials = dataSourceCredentials;
}
public RenderedReport RenderReport()
{
ReportExecutionServiceSoapClient reportingService = new ReportExecutionServiceSoapClient();
reportingService.ClientCredentials.Windows.ClientCredential = serviceCredentials;
reportingService.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;
TrustedUserHeader trustedUserHeader = new TrustedUserHeader();
ExecutionInfo2 executionInfo;
ServerInfoHeader serverInfoHeader;
ExecutionHeader executionHeader = reportingService.LoadReport2(trustedUserHeader, reportPath, null, out serverInfoHeader, out executionInfo);
if (parameters != null && parameters.Length != 0)
{
reportingService.SetExecutionParameters2(executionHeader, trustedUserHeader, parameters, parameterLanguage, out executionInfo);
executionHeader.ExecutionID = executionInfo.ExecutionID;
}
if (dataSourceCredentials != null && dataSourceCredentials.Length != 0)
{
reportingService.SetExecutionCredentials2(executionHeader, trustedUserHeader, dataSourceCredentials, out executionInfo);
executionHeader.ExecutionID = executionInfo.ExecutionID;
}
List<byte[]> pages = new List<byte[]>();
int pageIndex = 1;
const string format = "IMAGE";
Byte[] tempPage;
while (true)
{
string deviceInfo = String.Format(@"<DeviceInfo><StartPage>{0}</StartPage><OutputFormat>EMF</OutputFormat></DeviceInfo>",
pageIndex++);
string encoding;
string extension;
string mimeType;
Warning[] warnings;
string[] streamIDs;
reportingService.Render(executionHeader, trustedUserHeader, format, deviceInfo, out tempPage,
out extension, out mimeType, out encoding, out warnings, out streamIDs);
if (tempPage.Length > 0)
{
pages.Add(tempPage);
}
else
{
break;
}
}
return new RenderedReport(pages.Count, pages.ToArray());
}
public void DrawPage(Graphics graphics, int currentPrintingPage, RenderedReport renderedReport)
{
if (renderedReport.Pages[currentPrintingPage - 1] == null)
{
return;
}
MemoryStream currentPageStream = new MemoryStream(renderedReport.Pages[currentPrintingPage - 1])
{
Position = 0
};
if (metafile != null)
{
metafile.Dispose();
metafile = null;
}
metafile = new Metafile(currentPageStream);
lock (metafile)
{
Graphics.EnumerateMetafileProc enumerateMetafileProc = MetafileCallback;
graphics.EnumerateMetafile(metafile, graphics.VisibleClipBounds, enumerateMetafileProc);
enumerateMetafileProc = null;
}
}
private bool MetafileCallback(EmfPlusRecordType recordType, int flags, int dataSize, IntPtr data, PlayRecordCallback callbackData)
{
byte[] dataArray = null;
// Dance around unmanaged code.
if (data != IntPtr.Zero)
{
// Copy the unmanaged record to a managed byte buffer
// that can be used by PlayRecord.
dataArray = new byte[dataSize];
Marshal.Copy(data, dataArray, 0, dataSize);
}
// play the record.
metafile.PlayRecord(recordType, flags, dataSize, dataArray);
return true;
}
}
The datasource credentials passed into the SetExecutionCredentials method is a userid and password associated with CRM. To find these credentials, have a look at the FilteredSystemUser table in your CRM database. Select a suitable, preferably service, user and use the systemuserid field for the username, and the organizationid field for the password. To find the datasource name, open the report you'd like to print, and have a look at the name for the datasource in there. The datasource name is not the name of the actual datasource the report is bound to, but rather the name of the datasource binding in your report. The credentials only apply if you have a datasource explicitly defined in the RDL. If it is only assigned through the report properties outside the report, they're not required.
The Network credentials assigned to your client is the windows credentials of the service. These can be instantiated with values in config, or you can use System.Net.CredentialCache.DefaultCredentials to use the identity of the process you're running as. It is important to enable impersonation. Also note that restrictions may apply if your process is already impersonating a user.
The parameters in the SetExecutionParameters method will be the parameters defined in your report. You only need to specify Name and Value, not Label.
The funniness at the end of the class is to draw the page, and transfer data from unmanaged data structures to managed data structures.
I've also had to make one or two changes to the endpoint's definition in the app.config, to set the authentication up to use NTLM, as seen in the following xml in the app.config.
<system.serviceModel>
<bindings>
<basicHttpBinding>
<binding name="ReportExecutionServiceSoap" closeTimeout="00:01:00"
openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"
allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"
maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"
messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"
useDefaultWebProxy="true">
<readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"
maxBytesPerRead="4096" maxNameTableCharCount="16384" />
<security mode="TransportCredentialOnly">
<transport clientCredentialType="Ntlm" proxyCredentialType="None"
realm="" />
<message clientCredentialType="UserName" algorithmSuite="Default" />
</security>
</binding>
</basicHttpBinding>
</bindings>
<client>
<endpoint address="http://192.168.10.57:80/ReportServer/ReportExecution2005.asmx"
binding="basicHttpBinding" bindingConfiguration="ReportExecutionServiceSoap"
contract="ReportExecutionService.ReportExecutionServiceSoap"
name="ReportExecutionServiceSoap" />
</client>
</system.serviceModel>
I've written a small test that will call the print method and print a report:
public void PrintAReport()
{
string printername = @"Microsoft XPS Document Writer";
string reportPath = @"/NZBUpgradeBeta1_MSCRM/CustomReports/{8b7f0cb2-0afa-e011-b6d0-00155d087d2e}";
ParameterValue[] parameters = new ParameterValue[]{};
string parameterLanguage = "en-NZ";
string username = "2FB4FC6A-97CE-E011-833E-00155D087D2E";
string password = "62CF13D1-61C1-E011-B6CB-00155D087D2E";
string datasourcename = @"DataSource1";
NetworkCredential serviceCredentials = new NetworkCredential("intergen.dev","Password1","dev");
ReportRenderer reportRenderer = new ReportRenderer(reportPath, parameters, parameterLanguage, username, password, datasourcename, serviceCredentials);
PrintingService service = new PrintingService();
service.Print(false, printername, reportRenderer);
}
