Dynamics CRM 2011 - Creating an e-mail activity with PDF format for custom SSRS report as an attachment

Last updated – 18th Nov 2017

This blog has come up with a Good news for all CRM Developers, who are struggling with PDF loading issues. Sometimes, after implemeting this code, developers can generate the PDF file but when they download and open it, they get an error that the file is currpted or not able to open the PDF file.

This is happening because of the report paramenters. Please perform below steps if you're facing the problem,

First, open your '.rdl' file (e.g. report.rdl) in XML form.
Then, find <QueryParameters> tab in report XML and validate your parameters with the parameters you're passing in the GetReportingSession() function in your code.

Hope this will be helpful.


Last updated – 10th June 2016

This blog post uses SDK.SOAP.js to perform all CRM web service calls.

Please refer following link to know more about the SDK.SOAP.js.
http://ankit.inkeysolutions.com/2014/10/sdksoapjs.html


Recently, I came across a requirement where I had to create a functionality that will create an email activity on “Order” entity that would be having a custom report in PDF format as an attachment. This functionality needs to be executed on a ribbon button click. I couldn’t find any complete example that covers all these functionalities. Hence, in this post, I will be sharing the complete solution to generate PDF file from a custom SSRS report and attach the same to newly created email activity.

Please follow below steps to achieve this functionality:

1) Variable initialization and EmailReport(), a starting point, this method needs be called via JavaScript code.
2) Creating an email activity for the “Order” entity.
3) On this step, we will fetch the SSRS report's GUID by using report name.
4) Creating an attachment for the email activity and binding the report in a PDF format as attachment.

Variable initialization and EmailReport() method that can be called via JavaScript code. – Step 1

This code initializes the variables, which are required in next steps of this post. Plus, it defines two JavaScript methods,

var reportName = "SampleReport"; //Please specify your report name.
var reportId = null;
var fileName = "PDF01"; //Please specify a file which you want to save.
var fromFieldId = null;
var fromFieldEntityName = null;
var toFieldId = null;
var toFieldEntityName = null;
var emailId = null;

//This function is need to be called from action side e.g. Ribbon button click.
function EmailReport() {
  //This function does not work in add mode of form.
  var type = Xrm.Page.ui.getFormType();
  if (type != 1) {
    GetIds(); // Set ids in global variables.
    CreateEmail(); //Create Email
  }
}

//Gets the fromFieldId and toFieldEntityName used to set Email To & From fields
function GetIds() {
  fromFieldId = "00000000-0000-0000-0000-000000000000";//The Guid which needs to be set in form field.
  fromFieldEntityName = "systemuser";//Please specify entity name for which you have specified above Guid. Most probably it's systemuser.
  toFieldId = "00000000-0000-0000-0000-000000000000"; //The Guid which needs to be set in to field.
  toFieldEntityName = "contact";//Please specify entity name for which you have specified above Guid.
}

Creating an email activity for the “Order” entity – Step 2

With this step we will first create an email activity for the “Order” entity. When you create an email activity for an entity you must associate the entity and activity with either To, From or Regarding participation type. In this example the entity “Order” for which the email activity is created, is associated as Regarding in the email activity.

Now, we will have to set “To” and “From” fields of the email activity. The “To” and “From” are Activity parties associated with the email activity.

//Create Email and link it with Order as Reagrding field
function CreateEmail() {
  var id = Xrm.Page.data.entity.getId();
  id = id.replace('{', "");
  id = id.replace('}', "");
  var entityLogicalName = Xrm.Page.data.entity.getEntityName();
  var regardingObjectId = new Sdk.EntityReference(entityLogicalName, id);

  var email = new Sdk.Entity("email");
  email.addAttribute(new Sdk.String("subject", "Your Booking"));
  email.addAttribute(new Sdk.Lookup("regardingobjectid", regardingObjectId));
  var fromParties = PrepareActivityParty(fromFieldId, fromFieldEntityName);
  email.addAttribute(new Sdk.PartyList("from", fromParties));
  var toParties = PrepareActivityParty(toFieldId, toFieldEntityName);
  email.addAttribute(new Sdk.PartyList("to", toParties));
  Sdk.Async.create(email, EmailCallBack, function (error) { alert(error.message); });
}

//This method get entity's id and logical name and return entitycollection of it.
function PrepareActivityParty(partyId, partyEntityName) {
  var activityParty = new Sdk.Entity("activityparty");
  activityParty.addAttribute(new Sdk.Lookup("partyid", new Sdk.EntityReference(partyEntityName, partyId)));
  var activityParties = new Sdk.EntityCollection();
  activityParties.addEntity(activityParty);
  return activityParties;
}

// Email Call Back function
function EmailCallBack(result) {
  emailId = result;
  GetReportId();
}
 

On this step, we will fetch the SSRS report's GUID by using report name. - Step 3

Use below code to fetch the SSRS report GUID using the SSRS report name.

//This method will get the reportId based on a report name

function GetReportId() {
  var columns = "reportid";
  var fetchQuery = new Sdk.Query.QueryByAttribute("report");
  fetchQuery.setColumnSet(columns);
  fetchQuery.addAttributeValue(new Sdk.String("name", reportName));
  Sdk.Async.retrieveMultiple(fetchQuery, RetrieveMultiple);
}

function RetrieveMultiple(entityCollection) {
  if (entityCollection.getEntities().getCount() > 0) {
    var entities = entityCollection.getEntities();
    var getAttribute = entities.getByIndex(0).getAttributes("reportid");
    if (getAttribute != null) {
      reportId = getAttribute.getValue();
    }
  }

  //get reporting session and use the params to convert a report in PDF
  var params = GetReportingSession();
  EncodePdf(params);
}

Creating an attachment for the email activity and binding the report in a PDF format as attachment - Step 4

Once we are done with creating an email activity and fetching the GUID value of the SSRS report in above steps, we will come to this step. Here it is creating an attachment for this newly created email activity.

The function GetReportingSession() gets the required details for converting a report into PDF. Using the function EncodePdf(), the report is converted into PDF format.

//Gets the report contents
function GetReportingSession() {

  var pth = Xrm.Page.context.getClientUrl() + "/CRMReports/rsviewer/reportviewer.aspx";
  var retrieveEntityReq = new XMLHttpRequest();
  var Id = Xrm.Page.data.entity.getId();

  retrieveEntityReq.open("POST", pth, false);
  retrieveEntityReq.setRequestHeader("Accept", "*/*");
  retrieveEntityReq.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
  var orgUniqueName = Xrm.Page.context.getOrgUniqueName();

  //The following line is required only when you have parameters to be passed to your SSRS custom report.
  var reportPrefilter = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'><entity name='salesorder'><all-attributes /><filter type='and'><condition attribute='salesorderid' operator='eq' value='" + Xrm.Page.data.entity.getId() + "' /></filter></entity></fetch>";

var req = "id=%7B" + reportId + "%7D&uniquename=" + orgUniqueName + "&iscustomreport=true&reportName=" + reportName + "&isScheduledReport=false&p:CRM_FilteredSalesOrder=" + reportPrefilter;
  retrieveEntityReq.send(req);


  var StartAfterComment = retrieveEntityReq.responseText.indexOf("e440105867d249ce8a45696677d42bcb") + 32;
  var x = retrieveEntityReq.responseText.lastIndexOf("ReportSession=");
  var ret = new Array();
  ret[0] = retrieveEntityReq.responseText.substr(x + 14, 24); //the session id
  x = retrieveEntityReq.responseText.lastIndexOf("ControlID=");
  ret[1] = retrieveEntityReq.responseText.substr(x + 10, 32); //the control id
  return ret;
}

function EncodePdf(params) {
  var retrieveEntityReq = new XMLHttpRequest();

  var pth = Xrm.Page.context.getClientUrl() + "/Reserved.ReportViewerWebControl.axd?ReportSession=" + params[0] + "&Culture=1033&CultureOverrides=True&UICulture=1033&UICultureOverrides=True&ReportStack=1&ControlID=" + params[1] + "&OpType=Export&FileName=Public&ContentDisposition=OnlyHtmlInline&Format=PDF";
  retrieveEntityReq.open('GET', pth, true);
  retrieveEntityReq.setRequestHeader("Accept", "*/*");
  retrieveEntityReq.responseType = "arraybuffer";

  retrieveEntityReq.onload = function (e) {
    if (this.status == 200) {
      var uInt8Array = new Uint8Array(this.response);
      var base64 = Encode64(uInt8Array);
      CreateEmailAttachment(base64);
    }
  };
  retrieveEntityReq.send();
}

var keyStr = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=";
function Encode64(input) {
  var output = new StringMaker();
  var chr1, chr2, chr3;
  var enc1, enc2, enc3, enc4;
  var i = 0;

  while (i < input.length) {
    chr1 = input[i++];
    chr2 = input[i++];
    chr3 = input[i++];

    enc1 = chr1 >> 2;
    enc2 = ((chr1 & 3) << 4) | (chr2 >> 4);
    enc3 = ((chr2 & 15) << 2) | (chr3 >> 6);
    enc4 = chr3 & 63;

    if (isNaN(chr2)) {
      enc3 = enc4 = 64;
    } else if (isNaN(chr3)) {
      enc4 = 64;
    }
    output.append(keyStr.charAt(enc1) + keyStr.charAt(enc2) + keyStr.charAt(enc3) + keyStr.charAt(enc4));
  }
  return output.toString();
}

var StringMaker = function () {
  this.parts = [];
  this.length = 0;
  this.append = function (s) {
    this.parts.push(s);
    this.length += s.length;
  }
  this.prepend = function (s) {
    this.parts.unshift(s);
    this.length += s.length;
  }
  this.toString = function () {
    return this.parts.join('');
  }
}

//Create attachment for the created email
function CreateEmailAttachment(encodedPdf) {

  //Get order number to name a newly created PDF report
  var orderNumber = Xrm.Page.getAttribute("ordernumber");
  var emailEntityReference = new Sdk.EntityReference("email", emailId);
  var newFileName = fileName + ".pdf";
  if (orderNumber != null)
    newFileName = fileName + orderNumber.getValue() + ".pdf";

  var activitymimeattachment = new Sdk.Entity("activitymimeattachment");
  activitymimeattachment.addAttribute(new Sdk.String("body", encodedPdf));
  activitymimeattachment.addAttribute(new Sdk.String("subject", "File Attachment"));
  activitymimeattachment.addAttribute(new Sdk.String("objecttypecode", "email"));
  activitymimeattachment.addAttribute(new Sdk.String("filename", newFileName));
  activitymimeattachment.addAttribute(new Sdk.Lookup("objectid", emailEntityReference));
  activitymimeattachment.addAttribute(new Sdk.String("mimetype", "application/pdf"));
  Sdk.Async.create(activitymimeattachment, ActivityMimeAttachmentCallBack, function (error) { alert(error.message); });

}

//ActivityMimeAttachment CallBack function
function ActivityMimeAttachmentCallBack(result) {
  Xrm.Utility.openEntityForm("email", emailId);
}

Note: This functionality is tested with Parameterized Custom Report in CRM Online.

---

The simplest and quickest way to insert data for Many-to-Many relationship in Dynamics CRM is the Drag and drop listbox.
http://www.inkeysolutions.com/DynamicCRMDragAndDropListBox.html