转：用 SSRS report as a PDF 附件 in Email in Dynamics CRM

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

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

3. 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();

}

4. Register a JavaScript action on button that calls ‘sendQuoteAsPDFInEmail’ method.
5. 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

2. More about Actions in CRM

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

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