Tuesday, August 04, 2009

Custom workflow action which renders and sends a report for Microsoft Dynamics CRM 4.0 with email

Idea of this custom workflow action is following:
1. Give user possibility to run and export report in one action.
2. Insert this exported report as attachment into email.
3. Sent this email to recipient.


I've created a new project. Type of project is 'Class Library', Framework - 3.0.

First step is to add all required references to reporting service, Workflow assemblies and CRM assemblies:

Reporting Services:





Workflow assemblies:



And Microsoft CRM SDK assemblies.

As a result I retrieved such project:



Second step - is to create a custom workflow action which does't have a required code but can be deployed to CRM. So the code of such class:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Crm.Workflow;
using System.Workflow.Activities;
using System.Workflow.ComponentModel;

namespace SendReportAction
{
[CrmWorkflowActivity("Execute and send a report")]
public class SendReport : SequenceActivity
{
protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
return ActivityExecutionStatus.Closed;
}
}
}


Next step is to declare properties for worflow action (url of reporting services web service, report to run, recipient of email with report - in my case a systemuser):

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Crm.Workflow;
using System.Workflow.Activities;
using System.Workflow.ComponentModel;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;

namespace SendReportAction
{
[CrmWorkflowActivity("Execute and send a report")]
public class SendReport : SequenceActivity
{
protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
return ActivityExecutionStatus.Closed;
}

public static DependencyProperty ServiceURLProperty = DependencyProperty.Register("ServiceURL", typeof(string), typeof(SendReport));

[CrmInput("ServiceURL")]
public string ServiceURL
{
get
{
return (string)base.GetValue(ServiceURLProperty);
}
set
{
base.SetValue(ServiceURLProperty, value);
}
}

public static DependencyProperty ReportNameProperty = DependencyProperty.Register("ReportName", typeof(string), typeof(SendReport));

[CrmInput("ReportName")]
public string ReportName
{
get
{
return (string)base.GetValue(ReportNameProperty);
}
set
{
base.SetValue(ReportNameProperty, value);
}
}

public static DependencyProperty MailRecipientProperty = DependencyProperty.Register("MailRecipient", typeof(Lookup), typeof(SendReport));

[CrmInput("MailRecipient")]
[CrmReferenceTarget("systemuser")]
public Lookup MailRecipient
{
get
{
return (Lookup)base.GetValue(MailRecipientProperty);
}
set
{
base.SetValue(MailRecipientProperty, value);
}
}
}
}


Next step - is to write code which will create an email:

if (MailRecipient != null && !MailRecipient.IsNull && !MailRecipient.IsNullSpecified)
{
IContextService contextService = (IContextService)executionContext.GetService(typeof(IContextService));
IWorkflowContext workflowContext = contextService.Context;
ICrmService crmservice = workflowContext.CreateCrmService();

email mail = new email();

activityparty fromparty = new activityparty();
fromparty.partyid = new Lookup();
fromparty.partyid.type = EntityName.systemuser.ToString();
fromparty.partyid.Value = workflowContext.UserId;
mail.from = new activityparty[] { fromparty };

activityparty toparty = new activityparty();
toparty.partyid = new Lookup();
toparty.partyid.type = EntityName.systemuser.ToString();
toparty.partyid.Value = MailRecipient.Value;
mail.to = new activityparty[] { toparty };

mail.subject = "Report Subscription";
mail.sender = "crm@example.com";

mail.description = "Report Subscription";

mail.ownerid = new Owner();
mail.ownerid.type = EntityName.systemuser.ToString();
mail.ownerid.Value = workflowContext.UserId;
Guid createdEmailGuid = crmservice.Create(mail);
}


Next step is to execute and export report:

Reporting.SessionHeader sessionheader = null;
byte[] result;
string encoding;
string mimetype;
Reporting.ParameterValue[] parametersUsed = null;
Reporting.Warning[] warnings;
string[] streamids;

BasicHttpBinding binding = new BasicHttpBinding(BasicHttpSecurityMode.TransportCredentialOnly);
binding.Security.Message.ClientCredentialType = BasicHttpMessageCredentialType.UserName;
binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows;
binding.Security.Transport.ProxyCredentialType = HttpProxyCredentialType.None;
binding.Security.Transport.Realm = string.Empty;
EndpointAddress endpoint = new EndpointAddress(ServiceURL);

Reporting.ReportingServiceSoapClient client = new Reporting.ReportingServiceSoapClient(binding, endpoint);
client.ClientCredentials.Windows.AllowedImpersonationLevel = TokenImpersonationLevel.Impersonation;

client.Render(ref sessionheader, ReportName, "Excel", null,
null, null, null, null, out result, out encoding, out mimetype,
out parametersUsed, out warnings, out streamids);


Next step is to attach retrieved file to the email:

activitymimeattachment attach = new activitymimeattachment();
attach.activityid = new Lookup(EntityName.email.ToString(), createdEmailGuid);
attach.body = System.Convert.ToBase64String(result);
attach.subject =
attach.filename = "Report.xls";
attach.filesize = new CrmNumber(result.Length);
attach.mimetype = @"application/vnd.ms-excel";

crmservice.Create(attach);


And sent the email:

SendEmailRequest sendrequest = new SendEmailRequest();
sendrequest.EmailId = createdEmailGuid;
sendrequest.TrackingToken = "";
sendrequest.IssueSend = true;

crmservice.Execute(sendrequest);


Full code of this custom workflow action:


using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Crm.Workflow;
using System.Workflow.Activities;
using System.Workflow.ComponentModel;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.SdkTypeProxy;
using System.ServiceModel;
using System.Security.Principal;

namespace SendReportAction
{
[CrmWorkflowActivity("Execute and send a report")]
public class SendReport : SequenceActivity
{
protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
if (MailRecipient != null && !MailRecipient.IsNull && !MailRecipient.IsNullSpecified)
{
IContextService contextService = (IContextService)executionContext.GetService(typeof(IContextService));
IWorkflowContext workflowContext = contextService.Context;
ICrmService crmservice = workflowContext.CreateCrmService();

email mail = new email();

activityparty fromparty = new activityparty();
fromparty.partyid = new Lookup();
fromparty.partyid.type = EntityName.systemuser.ToString();
fromparty.partyid.Value = workflowContext.UserId;
mail.from = new activityparty[] { fromparty };

activityparty toparty = new activityparty();
toparty.partyid = new Lookup();
toparty.partyid.type = EntityName.systemuser.ToString();
toparty.partyid.Value = MailRecipient.Value;
mail.to = new activityparty[] { toparty };

mail.subject = "Report Subscription";
mail.sender = "crm@example.com";

mail.description = "Report Subscription";

mail.ownerid = new Owner();
mail.ownerid.type = EntityName.systemuser.ToString();
mail.ownerid.Value = workflowContext.UserId;
Guid createdEmailGuid = crmservice.Create(mail);

Reporting.SessionHeader sessionheader = null;
byte[] result;
string encoding;
string mimetype;
Reporting.ParameterValue[] parametersUsed = null;
Reporting.Warning[] warnings;
string[] streamids;

BasicHttpBinding binding = new BasicHttpBinding(BasicHttpSecurityMode.TransportCredentialOnly);
binding.Security.Message.ClientCredentialType = BasicHttpMessageCredentialType.UserName;
binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows;
binding.Security.Transport.ProxyCredentialType = HttpProxyCredentialType.None;
binding.Security.Transport.Realm = string.Empty;
EndpointAddress endpoint = new EndpointAddress(ServiceURL);

Reporting.ReportingServiceSoapClient client = new Reporting.ReportingServiceSoapClient(binding, endpoint);
client.ClientCredentials.Windows.AllowedImpersonationLevel = TokenImpersonationLevel.Impersonation;

client.Render(ref sessionheader, ReportName, "Excel", null,
null, null, null, null, out result, out encoding, out mimetype,
out parametersUsed, out warnings, out streamids);

activitymimeattachment attach = new activitymimeattachment();
attach.activityid = new Lookup(EntityName.email.ToString(), createdEmailGuid);
attach.body = System.Convert.ToBase64String(result);
attach.subject =
attach.filename = "Report.xls";
attach.filesize = new CrmNumber(result.Length);
attach.mimetype = @"application/vnd.ms-excel";

crmservice.Create(attach);

SendEmailRequest sendrequest = new SendEmailRequest();
sendrequest.EmailId = createdEmailGuid;
sendrequest.TrackingToken = "";
sendrequest.IssueSend = true;

crmservice.Execute(sendrequest);
}

return ActivityExecutionStatus.Closed;
}

public static DependencyProperty ServiceURLProperty = DependencyProperty.Register("ServiceURL", typeof(string), typeof(SendReport));

[CrmInput("ServiceURL")]
public string ServiceURL
{
get
{
return (string)base.GetValue(ServiceURLProperty);
}
set
{
base.SetValue(ServiceURLProperty, value);
}
}

public static DependencyProperty ReportNameProperty = DependencyProperty.Register("ReportName", typeof(string), typeof(SendReport));

[CrmInput("ReportName")]
public string ReportName
{
get
{
return (string)base.GetValue(ReportNameProperty);
}
set
{
base.SetValue(ReportNameProperty, value);
}
}

public static DependencyProperty MailRecipientProperty = DependencyProperty.Register("MailRecipient", typeof(Lookup), typeof(SendReport));

[CrmInput("MailRecipient")]
[CrmReferenceTarget("systemuser")]
public Lookup MailRecipient
{
get
{
return (Lookup)base.GetValue(MailRecipientProperty);
}
set
{
base.SetValue(MailRecipientProperty, value);
}
}
}
}


Next step is to build and publish developed custom workflow action:



And now I can use this step in CRM. As an example I'll create on demand workflow which will send 'Contacts' report (this report have no parameters but my custom workflow action can be extended with parameters to be used in report).

This workflow:









Save and publish it and the result of work:













As you see - this works. But there is one trick. To make this workflow action work you have to run Microsoft CRM Asynchronous Service under account which have access to Reports used in the workflow.

25 comments:

  1. на днях попробую, инструкция подробней некуда, как раз для меня :)

    ReplyDelete
  2. Thank you very much for this blogpost, excelent!!!

    ReplyDelete
  3. I'm having trouble adding the Service Reference to Reporting Services I 'm getting the error message:

    The HTTP request is unauthorized with client authentication scheme 'Anonymous'. The authentication header received from the server was 'NTLM'.
    The remote server returned an error: (401) Unauthorized.
    If the service is defined in the current solution, try building the solution and adding the service reference again.

    Do you have any idea of how to get around this?

    ReplyDelete
  4. Check following urls. I hope the will help you:
    http://support.microsoft.com/kb/215383
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;896861

    ReplyDelete
  5. Hi a331k, fantastic blogpost! just the solution I've been searching for.

    I am running a report on the account entity to show the number of cases for that account. Can you tell me how I can pass the account from the workflow to the report I am creating with your plugin?

    Many Thanks,

    Rory

    ReplyDelete
  6. Hi a331k,

    Can you further explain about the last sentence.

    "As you see - this works. But there is one trick. To make this workflow action work you have to run Microsoft CRM Asynchronous Service under account which have access to Reports used in the workflow."

    Don't really get it. By the way, my worklow Status Reason is stucked with Waiting. I wonder what is the problem.

    Thanks.

    ReplyDelete
  7. Hello Andriy,

    Do you know about issues with referencing the report server? I'm unable to get a reference, and not sure how to resolve. I see info here: http://www.trentswanson.com/post/2008/01/Calling-SharePoint-web-services-using-Visual-Studio-20082c-WCF2c-and-Windows-Auth.aspx, which is helpful, but I don't know where to edit config file. Any thoughts?

    Cheers,
    gg

    ReplyDelete
  8. Rory. To pass parameters to the report you should redevelop this workflow action.

    ReplyDelete
  9. Ryan. It seems that some exception occurred in action. Try to switch on trace of CRM, repeat workflow and check the log - Exception must be there.

    ReplyDelete
  10. Sheri, what do you do to receive a reference?

    ReplyDelete
  11. Hi Andry,
    Regarding this,
    "To make this workflow action work you have to run Microsoft CRM Asynchronous Service under account which have access to Reports used in the workflow"
    The CRM Async Service Runs under the "Network Service" Account, Where as the access to the report is only for the MS CRM Users.
    Does this mean we have to run the CRM Async Service from one of the MS CRM User? Will it be a good idea to run the Crm Async Service from one CRM USer account considering it will effect all the running workflows as well.
    Thanks.

    ReplyDelete
  12. It works, and works well. I've changed the approach to pass credentials to such worflow actions - I pass it there using input parameters.

    ReplyDelete
  13. Hello Andriy,
    Thank you for the post.
    As for Havenro's comment - I used the following link - http:\\'ServerName'\ReportServer\ReportService2005.asmx, and it is working.

    ReplyDelete
  14. I must be really slow, can you show the changes to interface with ssrs 2008 please?

    ReplyDelete
  15. Hi, Steve. Try following:
    http:///reportserver/reportexecution2005.asmx

    ReplyDelete
  16. I did do that, but did you do a service reference to that or a web reference. I also have a lot of issues with the new methods in the reportexecution as well as the number of parameters on the render.

    ReplyDelete
  17. I did a service reference in this custom workflow activity. In several projects I used web reference and I have no problems with it... I will redevelop this solution for one of my customers. So I will be able to share new solution.

    ReplyDelete
  18. Hi Andriy,

    Thanks for article...It looks what it should be :)

    ReplyDelete
  19. Hi Andriy,
    I am not very expert in all this but this is what my charity is trying to do, be able to email a pdf reciept to all our donors.

    Is this avaliable as a plugin file for me to add via the plugin registration tool.

    Thanks

    ReplyDelete
  20. Andriy the report that I generate contains lots of garbage characters - basically it doesn't look like the same report when I run it manually through CRM.

    any ideas on how I can get rid of this..?

    Hope you can help,

    Tamim.

    ReplyDelete
  21. Hello,
    Unfortunately I don't know how does report rendering work inside. You should contact Reporting Services specialists regarding this issue.

    Kind regards,
    Andriy.

    ReplyDelete
  22. Hello;
    It is very helpfull article but i have another problem . I want to attach file to an e-mail message throuth workflow. I have a file stored on a share on a server. I want to start workflow and enter a path of the file in WF.
    The worhflow should upload the file in to the e-mail message and send a message. Does anybody have any idea how to do this.

    ReplyDelete
    Replies
    1. Hello Robert,

      Actually the code provided in article can be used for your custom workflow action.

      Delete
  23. Andrii,

    This is cool, really cool. Have you tried it in CRM 2011? It appears your code is for 4.0, and while I do not see a reason why it should not work, I just wanted to ask.

    Thanks for sharing your knowledge.

    Cheers,

    Oliver

    ReplyDelete