Programmatically printing CRM SSRS reports.

Update: The mechanism previously used in this article would only ever print the first page of your report, as the StreamIds field is empty when rendering images. This is due to a change in Sql Server 2008 R2. One alternative would be to use the NumPages property in the ExecutionInfo object, but this is only populated for a few output formats, notably not the Image, PDF or Word format. Instead, we've had to revert to a process that just continues rendering pages until the service returns an empty page. This has obvious downsides and limitations. It works fine for the Image/emf format, but an exception is thrown for PDF. Other formats may have a different exceptions thrown. Word documents doesn't throw exceptions, and continues to merrily generate non-empty pages. For these formats, you could try creating an item containing the totalnumber of pages in the report that could be read programmatically, but that is beyond the scope of this post. Some additional info can be found here:
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);
        }

Beware the smart quotes when writing code outside a code editor!

I struggled a few hours today performing a REST query from the IE address bar. I copied the URL from some source to One Note, and changed it a bit. I pasted it back into IE, and just couldn't get it to work.

Eventually, after much pain and suffering, I typed a single quote next to the existing one in notepad to try escaping some text, and noticed a subtle difference between the quotes. See if you can spot it:  ‘ ’ '   While these three characters look similar to you and me, they are in fact very different to text parsers.

My guess is that at some point it must have been inserted by the auto correct feature of a helpful Microsoft Office application. A little further digging exposed that there is a way to turn off the "insert smart quotes" feature in Office applications, explained here.

There are more info available about smart quotes here. A particularly handy tip, is that you can undo the smart quote conversion by pressing ctrl-z immediately after typing the quote character.

How to Render a partial view in the controller.

I had the need today to render some html using a partial view, and then add that html to a JSON Result in MVC 3. I found a few examples on the internet that does it by extending the Controller class, but I don't particularly like this approach as it seemed hard to test. The reason is that it requires the view to be resolved in the controller itself, using built-in methods that rely on other static methods ("dirty, evil!"). I'm sure you can make it work by mocking some of the funkiness around view engines, but that just seemed like too much hard work.

My solution is to apply a bit of dependency injection by injecting a new class called a PartialViewRenderer as an interface into my controller. This class will abstract the rendering away, thereby removing the dependency on the view engines etc. from the controller. My controller now becomes easily testable again! This also adheres to the default approach of rendering views etc outside of the controller. You can also test the rendering seperately, but I haven't bothered with that.

The code for the renderer is as follows:

public class PartialViewRenderer : IPartialViewRenderer
{
    public string RenderOutput(ControllerContext controllerContext, object model, string partialViewName)
    {
        using (StringWriter writer = new StringWriter())
        {
            ViewEngineResult viewResult = ViewEngines.Engines.FindPartialView(controllerContext, partialViewName);
            ViewContext viewContext = new ViewContext(controllerContext, viewResult.View, new ViewDataDictionary(model), 
                controllerContext.Controller.TempData, writer);
            viewResult.View.Render(viewContext, writer);
            return writer.GetStringBuilder().ToString();
        }
    }
}

It is used in the following action:

public JsonResult LoadUrl(string request)
{
    RestUrlRequest data = new JavaScriptSerializer().Deserialize(request);
    RestSampleUrlModel model = restExamplesProvider.LoadUrl(data);
    model.OutputHtml = outputRenderer.RenderOutput(ControllerContext, model, formatToOuputViewMap[data.Format]);
    return Json(model, JsonRequestBehavior.AllowGet);
}

outputRenderer is an instance of the PartialViewRenderer that we've injected in this controllers' constructor.

A test can be done as such:

[TestMethod]
public void LoadUrlReturnsUrlResultWithOuputFromRendererForFormat()
{
    RestSampleUrlModel model = new RestSampleUrlModelFixture().Build();
    IRestExamplesProvider samplesProvider = new RestExamplesProviderMockFixture { RestSampleUrlModel = model }.Build();
    RestBrowserControllerFixture controllerFixture = new RestBrowserControllerFixture { RestSamplesProvider = samplesProvider };
    RestBrowserController controller = controllerFixture.Build();
    RestUrlRequest request = new RestUrlRequestFixture { Format = "atom" }.Build();

    JsonResult result = controller.LoadUrl(new JavaScriptSerializer().Serialize(request));

    result.Should().NotBeNull();
    RestSampleUrlModel data = (RestSampleUrlModel) result.Data;
    data.OutputHtml.Should().Be("mock render output");
    controllerFixture.RestOutputRenderer.AssertWasCalled(mock => mock.RenderOutput(controller.ControllerContext, model, "atom"));
}

I used a mock for the implementation of the OutputRenderer in this test. The mock itself is setup in a seperate fixture class. The renderer class itself can be extended to be more flexible with different overloads for the method, but that is surplus to my requirements.

Fun with CRM RetrieveMultiple, ConditionOperator.Contains and sql indexing

Had an interesting problem today, trying to search a CRM entity.
I had the following code:

QueryExpression query = new QueryExpression("fpis_jobsite")
{ 
    ColumnSet = new ColumnSet { AllColumns = true } 
};
query.Criteria = new FilterExpression();
query.Criteria.AddCondition("fpis_name", ConditionOperator.Contains, siteName);
EntityCollection entities = organizationService.RetrieveMultiple(query);
return entities.Entities.Select(BuildJobSite).ToList();

Running it in a test gave me a Generic SQL Error.

After much fuss, I followed the instructions
here to run a SQL trace.

I found the following error:
"Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'fpis_JobSite' because it is not full-text indexed."
Once I changed the ConditionOperator.Contains operator in my condition to ConditionOperator.Like, the call executed without any further problems.
On a side note, it is not possible to populate child entities by calling RetrieveMultiple with a QueryExpression. You have to make seperate web service calls. In most cases though, you'd be able to craft your QueryExpression to retrieve all the child entities in a single call.