Friday 23 November 2012

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

Saturday 10 November 2012

Dynamics CRM 2011 – Date Time field in custom SSRS report

While using any Date field in your Report you have to be careful about the user’s time zone. Dynamics CRM 2011 stores the date in UTC format so the developer needs to convert it to the preferred date format of user.

Solution

In Report Properties Reference Section add a reference to Microsoft.Crm.Reporting.RdlHelper assembly. Also add a parameter CRM_UserTimeZoneName and set its default value to GMT Standard Time. Whenever you use the date field on report convert the UTC format of date to the user timezone. Use the expression given below:

=CStr(Format(IIf(IsNothing(Fields!ink_billingperiodfromutc.Value),
                            Nothing,                            CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime
                                  (Fields!ink_billingperiodfromutc.Value,                                    Parameters!CRM_UserTimeZoneName.Value))),"dd MMM yyyy"))

I hope this will be helpful.

Friday 2 November 2012

Dynamics CRM 2011 - How to set Optionset as datasource in Silverlight Combobox

In this post we will see how we can bind a Silverlight Combobox with values of a field of type CRM Optionset.
In the example the combobox is within a grid. The combobox code in the designer would be as given below:
The Designer :
<sdk:DataGridTemplateColumn Header="Category" Width="95" CellStyle="{StaticResource CRM2011_DataGridCellStyle}">

            <sdk:DataGridTemplateColumn.CellTemplate>

                <DataTemplate>

                    <TextBlock Text="{Binding Category}" />

                </DataTemplate>

            </sdk:DataGridTemplateColumn.CellTemplate>

            <sdk:DataGridTemplateColumn.CellEditingTemplate>

                <DataTemplate>

                    <ComboBox SelectedItem="{Binding Category, Mode=TwoWay}"                                 

                                      DisplayMemberPath="Value"

                                        SelectedValuePath="Key"                                                                       

                                  ItemsSource="{StaticResource CategoryListing}" Height="22" HorizontalContentAlignment="Stretch" VerticalContentAlignment="Stretch"/>

                </DataTemplate>

            </sdk:DataGridTemplateColumn.CellEditingTemplate>

        </sdk:DataGridTemplateColumn>

In the above code the Static Resource CategoryListing is of type Dictionary. The Value is set as the DisplayMemberPath property and Key is set in the SelectedValuePath property.
In the example we have taken the ItemsSource as Static Resource .So in the codebehind the static resource should be assigned with values before InitializeComponent() is called.

The Model class :

ExpenseNoteModel is the class bound with the Datagrid.
The property “Category” is set in the SelectedItem property. The selected Item would normally display the value as “[key,value]” format where as we would like to display simply the value as selected item. To do so we have used string functions while setting and getting the Category. We set the key property while Set of Category . So we can use the key property of the model class to set selected value for a field.

public class ExpenseNoteModel : ink_expensenote

    {

        private string selectedCategory;

        private string returnValue;

        public string Category

        {

            get

            {

                if (String.IsNullOrEmpty(selectedCategory) == false)

                {

                    string[] category = selectedCategory.Split(',');

                    if (category.Length > 0)

                    {

 

                        returnValue = category[1].TrimEnd(']');

                    }

                }

                return returnValue;

            }

            set

            {

                selectedCategory = value;

                if (String.IsNullOrEmpty(selectedCategory) == false)

                {

                    string[] category = selectedCategory.Split(',');

                    if (category.Length > 0)

                    {

 

                        key = int.Parse(category[0].TrimStart('['));

                    }

                }

            }

        }

        public int key

        {

            get;

            set;

        }

        public Dictionary<int, string> Categories

        {

            get;

            set;

        }

    }

The codebehind:
The function below gets the Optionset labels and values and set them in a dictionary type object.

Dictionary<int,string> categoryListing=new Dictionary<int,string>();  

  

//Add the below lines in the Initialization method of your class

 

public ExpenseNotePage()

{

    this.Resources.Add("CategoryListing", categoryListing);

    GetOptionSetLabels();

    InitializeComponent();

}

 

 

  /// <summary>   

    /// Get options set labels for specified entity's optionset type attribute   

    /// </summary> 

    /// <param name="entityName"></param>   

    /// <param name="attributeName"></param>   

    private void GetOptionSetLabels()

    {

      try

      {

        OrganizationRequest request = new OrganizationRequest();

        request.RequestName = "RetrieveAttribute";

        request["EntityLogicalName"] = "ink_expensenote";

        request["LogicalName"] = "ink_category";

        request["MetadataId"] = Guid.Empty;

        request["RetrieveAsIfPublished"] = true;

        IOrganizationService service = SilverlightUtility.GetSoapService();

        service.BeginExecute(request, new AsyncCallback(OnGetOptionSetLabelsComplete), service);

      }

      catch (Exception ex)

      {

        throw ex;

      }

    }

 

 

 

 

    /// <summary>  

    /// Retrieve the results  

    /// </summary>  

    /// <param name="result"></param>  

    private void OnGetOptionSetLabelsComplete(IAsyncResult result)

    {

      //Get the original query back from the result.  

      OrganizationResponse response = ((IOrganizationService)result.AsyncState).EndExecute(result);

 

      if (response != null && response.Results.Count > 0)

      {

        //Get the actual optionset meta data  

        categories = ((EnumAttributeMetadata)(response.Results[0].Value)).OptionSet;

 

        for (int j = 0; j < categories.Options.Count; j++)

        {

          categoryListing.Add(int.Parse(categories.Options[j].Value.ToString()),categories.Options[j].Label.UserLocalizedLabel.Label.ToString());          

        }       

      }

    }