Add an SSRS Report as an Attachment to E-mails in a Custom Workflow in MSCRM 2011

In the below sample code, the report will actually be generated in two formats: Excel and PDF. These would then be attached to an e-mail specified at the workflow level. Make sure to visit Creating a Custom Workflow Activity in MS CRM 2011!

In this second article in a three part series, we’ll look at how to add an SSRS report as an attachment to an e-mail in a custom workflow in MSCRM 2011. In the below sample code, the report (in this case, a quote report) will actually be generated in two formats: Excel and PDF. These would then be attached to an e-mail specified at the workflow level. Make sure to visit Creating a Custom Workflow Activity in MS CRM 2011 to understand how to create custom workflow activities!

To implement:

1. Register your compiled custom workflow activity DLL using the Plugin Registration Tool (see Creating a Custom Workflow Activity in MS CRM 2011).
2. Create a new workflow process.
3. Create an e-mail record.
4. Add the attach quote custom workflow step (I called mine “Attach Quote”):
   
5. Click on the step’s Set Properties.
6. Set the e-mail reference to the e-mail record you created in step #3.
7. Activate your workflow process.

namespace MyWorkflow
{
    using System;
    using System.Activities;
    using System.Diagnostics;
    using System.Web.Services.Protocols;
    using Microsoft.Xrm.Sdk;
    using Microsoft.Xrm.Sdk.Workflow;
    using Microsoft.Xrm.Sdk.Messages;
    using Microsoft.Crm.Sdk.Messages;
    using Microsoft.Crm.Sdk;
    using Microsoft.Xrm.Sdk.Query;
    using WorkflowActivities.RSWebReference;
    using Xrm;

    public sealed class AttachQuote : CodeActivity
    {
        [Input("E-mail Reference")]
        [ReferenceTarget(Email.EntityLogicalName)]
        public InArgument<EntityReference> LookupEmail { get; set; }
        
        protected override void Execute(CodeActivityContext executionContext)
        {
            try
            {
                // Create the tracing service
                ITracingService tracingService = executionContext.GetExtension<ITracingService>();

                // Create the context
                IWorkflowContext context = executionContext.GetExtension<IWorkflowContext>();

                IOrganizationServiceFactory serviceFactory = executionContext.GetExtension<IOrganizationServiceFactory>();

                // Create the Organization service
                IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

                // Get the target entity from the context
                Entity target = (Entity)service.Retrieve(Quote.EntityLogicalName, context.PrimaryEntityId, new ColumnSet(true));

                // Prepare DataContext by using AutoGenerated cs file:
                // CrmSvcUtil.exe /out:Xrm.cs /url:http<s>://<CRMSERVER>/XRMServices/2011/Organization.svc /domain:<DOMAIN> /username:<USERNAME> /password:<PASSWORD> /namespace:Xrm /serviceContextName:XrmDataContext
                XrmDataContext datacontext = new XrmDataContext(service);

                // Attach quote to email
                AttachQuoteToEmail(service, target, LookupEmail.Get<EntityReference>(executionContext));

            }
            catch (SoapException ex)
            {
                // Add the SoapException message in event log
                EventLog.WriteEntry("code error", "Error occured in " + ex.Detail.InnerText.ToString(), EventLogEntryType.Error);
            }
            catch (Exception exe)
            {
                // Add the GeneralException message in event log
                EventLog.WriteEntry("code error", "Error occured in " + exe.InnerException.ToString(), EventLogEntryType.Error);
            }
        }

        public static void AttachQuoteToEmail(IOrganizationService service, Entity quoteEntity, EntityReference emailRef)
        {
            byte[] result = null;

            // Get email instance
            Email emailInstance = new Email();
            Entity emailEntity = service.Retrieve(Email.EntityLogicalName, emailRef.Id, new ColumnSet(true));
            emailInstance.ActivityId = emailEntity.Id;

            ReportExecutionService rs = new ReportExecutionService();
            rs.Credentials = new System.Net.NetworkCredential("username", "password", "domain");
            rs.Url = "http://localhost/ReportServer/ReportExecution2005.asmx";

            string reportPath = "/<REPORT PATH>/Quote - Standard";
            string historyID = null;
            string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

            ParameterValue[] parameters = new ParameterValue[1];
            parameters[0] = new ParameterValue();
            parameters[0].Name = "P1";
            parameters[0].Value = string.Format("select  [quote0].*  from  FilteredQuote as "quote0" WHERE QuoteId = '{0}'", quoteEntity.Id);

            string encoding;
            string mimeType;
            string extension;
            Warning[] warnings = 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[] formats = {
                                    "PDF",
                                    "Excel"
                                };
            for (int i = 0; i < formats.Length; i++)
            {
                // Get data for report
                try
                {
                    result = rs.Render(formats[i], devInfo, out extension, out mimeType, out encoding, out warnings, out streamIDs);
                }

                catch (Exception err)
                {
                    throw new Exception(err.Message.ToString());
                }

                // Create attachment for generated format
                ActivityMimeAttachment attachment = new ActivityMimeAttachment();
                attachment.Subject = "Report";
                attachment.ObjectId = new EntityReference(Email.EntityLogicalName, emailInstance.Id);
                attachment.ObjectTypeCode = emailInstance.LogicalName;
                attachment.FileName = quoteEntity.Attributes["quotenumber"].ToString() + "." + extension;
                attachment.Body = Convert.ToBase64String(result);
                attachment.MimeType = mimeType;
                attachment.AttachmentNumber = i;

                service.Create(attachment);
            }
            service.Update(emailInstance);
        }
    }
}