Wednesday, November 28, 2012

CRM 2011- Send SSRS reports via email & sample code to convert (render) report as pdf attachment

// <copyright file="RenderReport.cs" company="Microsoft">
// Copyright (c) 2012 All Rights Reserved
// </copyright>
// <author>Microsoft</author>
// <date>11/7/2012 10:53:22 AM</date>
// <summary>Implements the RenderReport Workflow Activity.</summary>
namespace.Workflow
    {
    using System;
    using System.Activities;
    using System.ServiceModel;
    using Microsoft.Xrm.Sdk;
    using Microsoft.Xrm.Sdk.Workflow;
  
    using Xrm;

  public class RenderReport : CodeActivity
        {
        /// <summary>
        /// Executes the workflow activity.
        /// </summary>
        /// <param name="executionContext">The execution context.</param>
        protected override void Execute(CodeActivityContext executionContext)
            {
            // Create the tracing service
            ITracingService tracingService = executionContext.GetExtension<ITracingService>();

            if (tracingService == null)
                {
                throw new InvalidPluginExecutionException("Failed to retrieve tracing service.");
                }

            tracingService.Trace("Entered RenderReport.Execute(), Activity Instance Id: {0}, Workflow Instance Id: {1}",
                executionContext.ActivityInstanceId,
                executionContext.WorkflowInstanceId);

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

            if (context == null)
                {
                throw new InvalidPluginExecutionException("Failed to retrieve workflow context.");
                }

            tracingService.Trace("RenderReport.Execute(), Correlation Id: {0}, Initiating User: {1}",
                context.CorrelationId,
                context.InitiatingUserId);

            IOrganizationServiceFactory serviceFactory = executionContext.GetExtension<IOrganizationServiceFactory>();
            IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

            try
                {
                tracingService.Trace("1. Set up a report renderer");

                //string userName = @"xxx";
                //string password = "yyy";
                //string domain = "zzz";
                //tracingService.Trace("   username = {0}", userName);
                //tracingService.Trace("   password = {0}", password);
                //tracingService.Trace("   domain = {0}", domain);
                ReportRenderer renderer = new ReportRenderer(service, context.OrganizationName, this.ReportServer.Get<string>(executionContext), this.Username.Get<string>(executionContext), this.Password.Get<string>(executionContext), this.DomainName.Get<string>(executionContext));

                //ReportRenderer renderer = new ReportRenderer(service, workflowContext.OrganizationName, userName, password, domain);
                // 2. Render the report to a byte array
                tracingService.Trace("2. Render the report to a byte array");

                string reportName = this.ReportName.Get<string>(executionContext);
                tracingService.Trace("   reportName = {0}", reportName);
                Guid TransactionId = this.Transaction.Get<EntityReference>(executionContext).Id;
                //tracingService.Trace("   renewalId = {0}", renewalId.ToString());
                string mimeType;

                byte[] reportData = renderer.Render(reportName, new ReportParameter[]
            {
                new ReportParameter("TransactionId",TransactionId.ToString())
            }, out mimeType);
                //string reportData = renderer.TurnToPdf(reportName, "PDF");
                //tracingService.Trace("   report contains {0} bytes", reportData.Length);
                //tracingService.Trace("   mimeType = {0}", mimeType);

                // 3. Create an annotation
                tracingService.Trace("3. Create an annotation");

                string subject = this.Subject.Get<string>(executionContext);
                tracingService.Trace("   subject = {0}", subject);
                string fileName = this.FileName.Get<string>(executionContext);
                tracingService.Trace("   fileName = {0}", fileName);
                //var xy = new jmh_guntransaction();
                ////xy.jmh_guntransactionId
                var annotation = new Annotation();

                annotation.Subject = subject;
                annotation.ObjectId = new EntityReference(context.PrimaryEntityName, context.PrimaryEntityId);
                annotation.ObjectTypeCode = context.PrimaryEntityName;
                annotation.FileName = fileName;
                //annotation.FileSize = CrmTypes
                annotation.DocumentBody = Convert.ToBase64String(reportData);
                //annotation.DocumentBody = reportData;
                annotation.MimeType = @"application/pdf";
                //annotation.MimeType = "text/plain";
                //annotation.MimeType = @"application\ms-word";

                Guid annotationId = service.Create(annotation);
                tracingService.Trace("   annotationId = {0}", annotationId.ToString());

                // 4. Attach to email
                tracingService.Trace("4. Create attachment for email");

                //EntityReference emailReference = this.Email.Get<EntityReference>(executionContext);
                //if (emailReference != null)
                //    {
                //    tracingService.Trace("   emailId = {0}", emailReference.Id);

                //    Entity attachment = new Entity(EntityNames.ActivityMimeAttachment);
                //    attachment[ActivityMimeAttachmentAttributes.Subject] = subject;
                //    attachment[ActivityMimeAttachmentAttributes.ObjectId] = emailReference;
                //    attachment[ActivityMimeAttachmentAttributes.ObjectTypeCode] = service.GetEntityTypeCode(EntityNames.Email);
                //    attachment[ActivityMimeAttachmentAttributes.FileName] = fileName;
                //    attachment[ActivityMimeAttachmentAttributes.Body] = Convert.ToBase64String(reportData);
                //    attachment[ActivityMimeAttachmentAttributes.FileSize] = reportData.Length;
                //    attachment[ActivityMimeAttachmentAttributes.MimeType] = mimeType;
                //    attachment[ActivityMimeAttachmentAttributes.AttachmentNumber] = 0;

                //    Guid attachmentId = service.Create(attachment);
                //    tracingService.Trace("   attachmentId = {0}", attachmentId.ToString());
                //    }
                //else
                //    {
                //    tracingService.Trace("   no email specified");
                //    }

                // TODO: Implement your custom Workflow business logic.

                EntityReference emailReference = this.Email.Get<EntityReference>(executionContext);
                //Email em = new Email();
                if (emailReference != null)
                    {
                    tracingService.Trace("   emailId = {0}", emailReference.Id);


                    ActivityMimeAttachment attachment = new ActivityMimeAttachment();

                    attachment.Subject = subject;
                    attachment.ObjectId = emailReference;
                    attachment.ObjectTypeCode = emailReference.LogicalName;
                    attachment.FileName = fileName;
                    attachment.Body = Convert.ToBase64String(reportData);

                    attachment.MimeType = mimeType;
                    attachment.AttachmentNumber = 0;

                    Guid attachmentId = service.Create(attachment);
                    tracingService.Trace("   attachmentId = {0}", attachmentId.ToString());
                    }
                else
                    {
                    tracingService.Trace("   no email specified");
                    }
                SendEmailRequest sendrequest = new SendEmailRequest();
                sendrequest.EmailId = emailReference.Id;
                sendrequest.TrackingToken = "";
                sendrequest.IssueSend = true;

                service.Execute(sendrequest);
                // TODO: Implement your custom Workflow business logic.
                }
            catch (FaultException<OrganizationServiceFault> e)
                {
                tracingService.Trace("Exception: {0}", e.ToString());

                // Handle the exception.
                throw;
                }

            tracingService.Trace("Exiting RenderReport.Execute(), Correlation Id: {0}", context.CorrelationId);
            }

        [Input("Purchase order")]
        [ReferenceTarget("salesorder")]
        [RequiredArgument]
        public InArgument<EntityReference> Transaction { get; set; }

        [Input("Report name")]
        [RequiredArgument]
        public InArgument<string> ReportName { get; set; }


        [Input("Subject")]
        [RequiredArgument]
        public InArgument<string> Subject { get; set; }

        [Input("File name")]
        [RequiredArgument]
        public InArgument<string> FileName { get; set; }


        [Input("Report Server")]
        [RequiredArgument]
        public InArgument<string> ReportServer { get; set; }

        [Input("Email")]
        [ReferenceTarget("email")]
        [RequiredArgument]
        public InArgument<EntityReference> Email { get; set; }

        [Input("User name")]
        [RequiredArgument]
        public InArgument<string> Username { get; set; }

        [Input("Password")]
        [RequiredArgument]
        public InArgument<string> Password { get; set; }

        [Input("Domain Name")]
        [RequiredArgument]
        public InArgument<string> DomainName { get; set; }
        }
       
    }
using System;

using System.IO;
using System.Linq;
using System.ServiceModel;
using System.ServiceModel.Description;
using System.Text;

using Microsoft.Xrm.Sdk;
using Microsoft.Crm.Sdk.Messages;
using .ReportExecutionService;
namespace Workflow
    {
    public class ReportRenderer
        {

        //public ReportRenderer(IOrganizationService service, string organisationName, string userName, string password, string domain)
        public ReportRenderer(IOrganizationService service, string organisationName, string ReportServer, string userName, string passWord, string domainName)
            {
         
            this.ReportBasePath = string.Format("/{0}_MSCRM/", organisationName);
        
            this.ReportServerUrl = ReportServer;
            _reportService = new ReportExecutionService.ReportExecutionService();
            _reportService.Url = string.Format("{0}/ReportExecution2005.asmx", this.ReportServerUrl);
            //_reportService.Credentials = new System.Net.NetworkCredential(userName, password, domain); // System.Net.CredentialCache.DefaultCredentials;
            //_reportService.Credentials = System.Net.CredentialCache.DefaultCredentials;
            _reportService.Credentials = new System.Net.NetworkCredential(userName, passWord, domainName);
            }

        private ReportExecutionService.ReportExecutionService _reportService;

        public string ReportBasePath { get; private set; }
        public string ReportServerUrl { get; private set; }
  
        public void RenderToStream(string reportName, ReportParameter[] parameters, out string mimeType, Stream stream)
            {
            byte[] data = this.Render(reportName, parameters, out mimeType);
            stream.Write(data, 0, data.Length);
            }

        public byte[] Render(string reportName, ReportParameter[] parameters, out string mimeType)
            {
            try
                {
                string reportPath = this.ReportBasePath + reportName;

                ExecutionInfo execInfo = new ExecutionInfo();
                ExecutionHeader execHeader = new ExecutionHeader();
                _reportService.ExecutionHeaderValue = execHeader;
                _reportService.Timeout = 9999999;
                execInfo = _reportService.LoadReport(reportPath, null);

                ReportExecutionService.ParameterValue[] reportParameters =
                    parameters.Select(p => new ReportExecutionService.ParameterValue()
                    {
                        Name = p.Name,
                        Label = p.Label,
                        Value = p.Value
                    }).ToArray<ReportExecutionService.ParameterValue>();
                //ReportExecutionService.ParameterValue[] reportParameters =new

                //ParameterValue[] parameters1 = new ParameterValue[1];
                //parameters1[0] = new ParameterValue();
                //parameters1[0].Name = "TransactionId";
                //parameters1[0].Value = "E1E3C748-7D1E-E211-9093-00155D000B45";

                //_reportService.SetExecutionParameters(reportParameters, "en-gb");
                //String SessionId = _reportService.ExecutionHeaderValue.ExecutionID;

                string extension = string.Empty;
                string encoding = string.Empty;
                string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";
                ReportExecutionService.Warning[] warnings = null;
                string[] streamIds = null;


                byte[] data = null;

                data = _reportService.Render("PDF", devInfo, out extension, out encoding, out mimeType, out warnings, out streamIds);
                mimeType = mimeType ?? "application/pdf";
                return data;
                }
            catch (FaultException<OrganizationServiceFault> e)
                {
                //tracingService.Trace("Exception: {0}", e.ToString());

                // Handle the exception.
                throw new InvalidPluginExecutionException("Exception in renderreport: {0}" + e.Message);
                }

            }

        public string TurnToPdf(string reportName, string i_Format)
            {
            // Render arguments
            string encoded = "";
            try
                {
                ReportExecutionService.ReportExecutionService rs = new ReportExecutionService.ReportExecutionService();
                rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
                //rs.Credentials=  new System.Net.NetworkCredential("crmserver", "ntier@123", "ntier");

                byte[] result = null;
                string reportPath = this.ReportBasePath + reportName;
                string format = i_Format;
                string historyID = null;
                string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

                //DataSourceCredentials[] credentials = null;
                //string showHideToggle = null;
                string encoding;
                string mimeType;
                string extension;
                Warning[] warnings = null;
                //ParameterValue[] reportHistoryParameters = null;
                string[] streamIDs = null;

                ExecutionInfo execInfo = new ExecutionInfo();
                ExecutionHeader execHeader = new ExecutionHeader();

                rs.ExecutionHeaderValue = execHeader;
                rs.Timeout = 9999999;
                execInfo = rs.LoadReport(reportPath, historyID);


                //ReportExecutionService.ParameterValue[] reportParameters =
                //    parameters.Select(p => new ReportExecutionService.ParameterValue()
                //    {
                //        Name = p.Name,
                //        Label = p.Label,
                //        Value = p.Value
                //    }).ToArray<ReportExecutionService.ParameterValue>();

                //_reportService.SetExecutionParameters(parameters1, "en-gb");
                //rs.SetExecutionParameters(parameters, "en-us");
                String SessionId = rs.ExecutionHeaderValue.ExecutionID;

                Console.WriteLine("SessionID: {0}", rs.ExecutionHeaderValue.ExecutionID);



                result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
                execInfo = rs.GetExecutionInfo();
                //FileStream st = System.IO.File.Create("d:\\test.pdf");
                //st.Write(result, 0, result.Length);
                //st.Close();
                encoded = System.Convert.ToBase64String(result);
                //encoded = "JVBERi0xLjMNCjEgMCBvYmoNClsvUERGIC9UZXh0IC9JbWFnZUIgL0ltYWdlQyAvSW1hZ2VJXQ0KZW5kb2JqDQozIDAgb2JqDQo8PCAvTGVuZ3RoIDM5IC9GaWx0ZXIgL0ZsYXRlRGVjb2RlID4+IHN0cmVhbQ0KeJzj5SpUMDfSMwASCiZmFkCGmQmILEpVCFfI4+UK5CWkAACtvw05DQplbmRzdHJlYW0NCmVuZG9iag0KMiAwIG9iag0KPDwgL1R5cGUgL1BhZ2UgL1BhcmVudCA0IDAgUiAvTWVkaWFCb3ggWzAgMCA2MTIuMCA3OTIuMF0gL0NvbnRlbnRzIDMgMCBSIC9SZXNvdXJjZXMgPDwgL1Byb2NTZXQgMSAwIFIgL1hPYmplY3QgPDwgPj4gL0ZvbnQgPDwgPj4gPj4gPj4NCmVuZG9iag0KNCAwIG9iag0KPDwgL1R5cGUgL1BhZ2VzIC9LaWRzIFsgMiAwIFIgXSAvQ291bnQgMSA+Pg0KZW5kb2JqDQo1IDAgb2JqDQo8PCAvVHlwZSAvQ2F0YWxvZyAvUGFnZXMgNCAwIFIgPj4NCmVuZG9iag0KNiAwIG9iag0KPDwgL1RpdGxlIDxmZWZmMDA0MzAwNzUwMDczMDA3NDAwNmYwMDZkMDA2NTAwNzIwMDUyMDA2NTAwNzAwMDZmMDA3MjAwNzQ+DQovQXV0aG9yIDw+DQovU3ViamVjdCA8Pg0KL0NyZWF0b3IgKE1pY3Jvc29mdCBSZXBvcnRpbmcgU2VydmljZXMgMTAuMC4wLjApDQovUHJvZHVjZXIgKE1pY3Jvc29mdCBSZXBvcnRpbmcgU2VydmljZXMgUERGIFJlbmRlcmluZyBFeHRlbnNpb24gMTAuMC4wLjApDQovQ3JlYXRpb25EYXRlIChEOjIwMTIxMTE1MTIzODQ0KzA1JzMwJykNCj4+DQplbmRvYmoNCnhyZWYNCjAgNw0KMDAwMDAwMDAwMCA2NTUzNSBmDQowMDAwMDAwMDEwIDAwMDAwIG4NCjAwMDAwMDAxODAgMDAwMDAgbg0KMDAwMDAwMDA2NSAwMDAwMCBuDQowMDAwMDAwMzMzIDAwMDAwIG4NCjAwMDAwMDAzOTUgMDAwMDAgbg0KMDAwMDAwMDQ0NyAwMDAwMCBuDQp0cmFpbGVyIDw8IC9TaXplIDcgL1Jvb3QgNSAwIFIgL0luZm8gNiAwIFIgPj4NCnN0YXJ0eHJlZg0KNzMzDQolJUVPRg==";
                Console.WriteLine("Execution date and time: {0}", execInfo.ExecutionDateTime);

                }
            //catch (SoapException e)
            //{
            //    Console.WriteLine(e.Detail.OuterXml);
            //    encoded = "";
            //}
            catch (FaultException<OrganizationServiceFault> e)
                {


                // Handle the exception.
                throw new InvalidPluginExecutionException("Exception in trun to pdf: {0}" + e.Message);
                }


            return encoded;

            }

        }


    public class ReportParameter
        {
        public ReportParameter(string name, string label, string value)
            {
            this.Name = name;
            this.Label = label;
            this.Value = value;
            }

        public ReportParameter(string name, string value)
            : this(name, name, value)
        { }

        public string Name { get; set; }
        public string Label { get; set; }
        public string Value { get; set; }
        }
    }
 reference:
http://ekoncis.blogspot.in/2012/01/crm-2011-custom-email-with-pdf-report.html
note:
1.SSRS report has to be deployed in report server before you run the workflow


2.create a workflow in which create a email record and pass the id to the workflow activity as it is in
http://www.stefan-scheller.com/2012/04/crm-2011-send-email-including-lines-of-document-pdf-attachment-walkthrough/

2.To add reference report execution service,right click the project->add service reference->advanced->add web reference-> http://<report server>/reportserver/reportexecution2005.asmx















4 comments:

  1. where will get ReportExecutionService in your code

    ReplyDelete
    Replies
    1. To add reference report execution service,right click the project->add service reference->advanced->add web reference-> http://report server name/reportserver/reportexecution2005.asmx

      Delete
  2. report server name means
    and annotation is also not working

    please suggest me

    ReplyDelete
    Replies
    1. 1. Go to SQL server
      2. Open “Reporting service configuration manager”
      3. Go to web service URL & get the url
      4. Browse in Internet explorer (/ reportexecution2005.asmx)
      5. Add it as a service reference in your project

      Delete