How data can be sorted on date field in SSRS report in Dynamics CRM?

Introduction

This is a short and simple article which explains how we can avoid sorting issues on date field in SSRS report in Dynamics CRM Fetch Based report.

Understanding Concepts

  • When we write fetch based SSRS report for Dynamics CRM, for complex field type, we always get 2 attributes in SSRS dataset. E.g. Lookup, Option Set, Date and Time, Currency etc.
  • From above 2 attributes, one contains the display string and other contains data value.
  • In Case of Date and Time field, as per below screenshot, we got 2 attributes in SSRS dataset for Est. Close Date field which is of type Date and Time.

dataset-fields

  • If you run the simple query (FetchXml of Open Opportunity view) in Query Designer, you will see data in below way.

query-designer-output

  • As we have display data and value data in separate field, for display purpose we should use display field (estimatedcloseddate in this case) & for data manipulation we should use data field (estimatedclosedateValue in this case).

How to sort data in Tablix based on date field?

  • As we have seen above how the dataset contains the data, we have to select data field in sorting expression to sort the data in required order.
  • To achieve this, go to Tablix properties and Sorting section and configure as per below screenshot:

tablix-sorting-configuration

Note: The Order (A to Z / Z to A) is just to determine Ascending or Descending order. It does not mean we have to provide only string type fields in Sorting Column. SSRS understands the data type and sorts the data accordingly.

Advertisements

How to send SSRS report as a PDF in Email in Dynamics CRM Online

Introduction

In this blog, I have explained how to create an email record in CRM by executing SSRS report and exporting as a PDF attachment to the email. This is frequently asked requirement by many clients and it is very simple to execute SSRS report and export as a PDF in CRM Online.

In this blog, I have kept focus on generating PDF format report using coding. I have also provided the code of generating PDF format report. I have not covered creating email record with attachment and reference links from this blog can be used to achieve the same.

Problem Statement/Assumptions

  1. We have a CRM Online environment with below things setup.
    1. Fetch based SSRS report with name let’s say ‘Quotation.rdl’.
    2. Assume, Quotation.rdl has below GUID.
      • 12f3c105-fd95-44a9-b1af-ad79dfe3ad16
    3. Quotation report is pre-filtered by Quote GUID. (What is pre-filtering?)
    4. On Quotation entity, we have a button called Send Quotation.
      • The purpose of this button is to execute SSRS report, Export it as a PDF, Create Email record with above generated PDF as an attachment & open Email record on successful completion of above operations.

Data/Events flow

data-flow-how-to-send-ssrs-in-pdf-format-in-email-in-crm-online

Step-by-step procedure

  1. Create an action in CRM on Quote Entity.
    1. Input Parameter: PDFString (String type)
    2. Output Parameter: EmailID (EntityReference to Email entity)
    3. Create email record in action definition.
  2. Write a plugin on above custom action that will read input parameter which is a string that contains base64 formatted SSRS report. Create an attachment using the string and attach the same to email.

To know more about actions in CRM: https://msdn.microsoft.com/en-us/library/dn481600.aspx

  1. Create a web resource with below JavaScript code (Code has the commenting to understand the code).

function sendQuoteAsPDFInEmail() {

    //1. Execution starts here.

    var arrReportSession = executeReport(); //2. Execute the ssrs report and capture the response. Response will be an array.

    convertResponseToPDF(arrReportSession); //3. Convert the response in base 64 string i.e. PDF.

}

function executeReport() {

    // GUID of SSRS report in CRM.

    var reportGuid = “12f3c105-fd95-44a9-b1af-ad79dfe3ad16”;

    //Name of the report. Note: .RDL needs to be specified.

    var reportName = “Quotation.rdl”;

    // URL of the report server which will execute report and generate response.

    var pth = Xrm.Page.context.getClientUrl() + “/CRMReports/rsviewer/QuirksReportViewer.aspx”;

    //This is the filter that is passed to pre-filtered report. It passes GUID of the record using Xrm.Page.data.entity.getId() method.

    //This filter shows example for quote report. If you want to pass ID of any other entity, you will need to specify respective entity name.

    var reportPrefilter = ”                      “;

    //Prepare query to execute report.

    var query = “id=%7B” + reportGuid +

        “%7D&uniquename=” + Xrm.Page.context.getOrgUniqueName()

        + “&iscustomreport=true&reportnameonsrs=&reportName=” + reportName

        + “&isScheduledReport=false&p:CRM_QuoteId=” + reportPrefilter;

    //Prepare request object to execute the report.

    var retrieveEntityReq = new XMLHttpRequest();

    retrieveEntityReq.open(“POST”, pth, false);

    retrieveEntityReq.setRequestHeader(“Accept”, “*/*”);

    retrieveEntityReq.setRequestHeader(“Content-Type”, “application/x-www-form-urlencoded”);

    //This statement runs the query and executes the report synchronously.

    retrieveEntityReq.send(query);

    //These variables captures the response and returns the response in an array.

    var x = retrieveEntityReq.responseText.lastIndexOf(“ReportSession=”);

    var y = retrieveEntityReq.responseText.lastIndexOf(“ControlID=”);

    var ret = new Array();

    ret[0] = retrieveEntityReq.responseText.substr(x + 14, 24);

    ret[1] = retrieveEntityReq.responseText.substr(x + 10, 32);

    //Returns the response as an Array.

    return ret;

}

function convertResponseToPDF(arrResponseSession) {

    //Create query string that will be passed to Report Server to generate PDF version of report response.

    var pth = Xrm.Page.context.getClientUrl() + “/Reserved.ReportViewerWebControl.axd?ReportSession=” + arrResponseSession[0] + “&Culture=1033&CultureOverrides=True&UICulture=1033&UICultureOverrides=True&ReportStack=1&ControlID=” + arrResponseSession[1] + “&OpType=Export&FileName=Public&ContentDisposition=OnlyHtmlInline&Format=PDF”;

    //Create request object that will be called to convert the response in PDF base 64 string.

    var retrieveEntityReq = new XMLHttpRequest();

    retrieveEntityReq.open(“GET”, pth, true);

    retrieveEntityReq.setRequestHeader(“Accept”, “*/*”);

    retrieveEntityReq.responseType = “arraybuffer”;

    retrieveEntityReq.onreadystatechange = function () { // This is the callback function.

        if (retrieveEntityReq.readyState == 4 && retrieveEntityReq.status == 200) {

            var binary = “”;

            var bytes = new Uint8Array(this.response);

            for (var i = 0; i < bytes.byteLength; i++) {

                binary += String.fromCharCode(bytes[i]);

            }

            //This is the base 64 PDF formatted string and is ready to pass to the action as an input parameter.

            var base64PDFString = btoa(binary);

            //4. Call Action and pass base 64 string as an input parameter. That’s it.

        }

    };

    //This statement sends the request for execution asynchronously. Callback function will be called on completion of the request.

    retrieveEntityReq.send();

}

  1. Register a JavaScript action on button that calls ‘sendQuoteAsPDFInEmail’ method.
  2. That’s it.

References

  1. Create Email and Attachment in CRM using C#

https://msdn.microsoft.com/en-us/library/hh210217.aspx

https://msdn.microsoft.com/en-us/library/gg328344.aspx

  1. More about Actions in CRM

https://technet.microsoft.com/en-us/library/dn531060.aspx

  1. Snapshot of code to generate Email record with PDF from base64 string.

 generate email with pdf attachment code snapshot