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);
        }
    }
}
  • Lovely Husna

    Hi I want to ask you about the parameter part in the code. I’m not so clear on that section. what is the purpose of the parameter?

    • Hi, sorry for the late reply. The purpose of the parameter is to set the filter for the report when it runs. In this case, the filter is the QuoteId field. Let me know if you need further clarification.