How to create SSRS Report in Dynamic 365 CRM

Note : You need to setup your machine to begin with SSRS report development.
Refer here for more details

How to setup Workstation to Create SSRS Reports for Dynamics 365

https://vjcity.blogspot.com/2019/12/how-to-setup-workstation-to-create-ssrs.html

To create reports, launch Visual Studio.

Click on File -> New -> Project

The New Project Dialog will appear.

From the Templates, choose Business Intelligence -> Reporting Services and select Report Server Project.

Once that is loaded, from the Solution Explorer 

on the right-hand side, right click on the Reports folder, choose Add -> New Item

Choose to create a Report file and give it a name.  

The “RDL” extension stands for “Report Definition Language” which is an XML interpretation

of the report you will create.

The report design surface will appear.

The first step is to create a link to your Dynamics 365 system.  

On the left of the report designer window, in the Report Data section, 

Right click on the Data Sources folder and click “Add Data Source”.

If the Report Authoring extensions were installed correctly, you should see “Microsoft

 Dynamics 365 Fetch” as a type. 

Provide the Data Source with a name and enter in your URL for your Dynamics 365 system.

Click on credentials and enter in your Dynamics 365 login and password. 

Note that these will not be “carried” with the report, but just used in the designer. 

When we eventually load the report in Dynamics 365 it will run in the context 

of the logged in user (and applicable security roles will apply)

We need to add a Dataset to pull data from Dynamics 365.  We will be using a query language called “FetchXML”.

While you could construct a FetchXML by hand, it is easier to generate using a tool.

One quick way to get a FetchXML query is from Dynamics 365 Advanced Find.

Looking at the FetchXML file, you can see the structure identifying the entity, the fields and filters.

Again on the Report Data section, right click on the Datasets folder and choose “Add Dataset”

Provide a name, choose to use the embedded dataset that we created earlier and paste in the FetchXML statement.

You should now see a dataset in the Report Data section.

For the first report, we will just drag a “table” component onto the design surface.

Left Click on the table and select Tablix Properties

Provide Dataset Name

Populate the field in the table.

Once we have defined our layout (pretty simple at this point) we can click the “Preview” button to see what our report will look like.

Now that we have our report created, lets load it to Dynamics 365!

Loading the Report to Dynamics 365

Login to Dynamics 365 and choose Sales or Service and click on the Reports icon.

You will see the list of out of the box reports.  Click on the +NEW button to add your new report.

Change Report Type to Existing File and Choose your ContactList.rdl file.

Select Related Record Types and other option as depicted in the image below.

The report should now appear on the list of available reports.  Double click the report to run.

You should now see your report rendered within the context of Dynamics 365.  You can now print or download to a variety of formats.

The report we created in this article needs a lot of formatting to be production ready and VST provide a way to do that.

Refer here my other post to understand a bit more about SSRS Report in Dynamic CRM.

Dynamic CRM SSRS Report best configuration

https://vjcity.blogspot.com/2019/05/dynamic-crm-ssrs-report-best.html

Dynamic CRM SSRS Report Syntax Help

https://vjcity.blogspot.com/2019/05/dynamic-crm-ssrs-report-syntax-help.html

How to setup Workstation to Create SSRS Reports for Dynamics 365

Visual Studio Data Tools

The first step is that you will need to install Visual Studio Data Tools 2015.

Click here to get to the Visual Studio Data Tools download page.

The file SSDTSetup.exe should download.  When complete, run the program to install the tool.  You will need to have administrative permissions on your workstation.

When prompted, choose to install SQL Server Reporting Services.  Click Next and complete the setup.

Dynamics 365 Report Authoring Extension

To write FetchXML based reports, you will need to load the Dynamics 365 Report Authoring Extensions.

The Dynamics 365 Report Authoring Extension is an add-on for SQL Server Data Tools that will allow you to write reports using the FetchXML language.  This currently is the only method to write SSRS reports that can access Dynamics 365 Online data directly.

Click here to download Dynamics 365 Report Authoring Extension

You should have now downloaded the file “CRM9.0-Bids-ENU-i386.exe” (name may change slightly if you are downloading a different language)

Extract the package to a folder on your workstation and continue the install.
I would recommend you choose to get updates to make sure you have the latest version and patches.  Click Next.

There may or may not be updated files to install.  Click Next.

Read the license agreement and click on the accept checkbox and click I Accept to continue.

You may (or may not) be prompted to install some additional updates.  Choose to Install to update these components.

Choose whether or not you want to use Microsoft Update.  Click Next.

Choose the installation location.  Click Next.

The installation process will check the system.  Click Next to continue.

Confirm the installation directory and click Install to initiate the process.

After a few minutes, the installation process will complete.  Click Finish.

The Dynamics 365 Report Authoring extension and SQL Server Data Tools are now installed on your workstation and you can begin to author SSRS reports for Dynamics 365.

If you aren’t familiar with writing Dynamics 365 SSRS reports, check out my post to get started.

InvalidTemplate. The template language function ‘addDays’ expect a timestamp as the first parameter

Solution

Modified le 'addDays(utcNow(),10,'yyyy-MM-dd')'

There are two issues with the filter query expression. First, the expression should look like this:

addDays(utcNow(), 180, 'yyyy-MM-dd')

In addition, there need to be single quotes around the expression.

Date of Last Activity in Dynamic CRM

In Dynamics CRM, the Sales team wants to know the last activity done for a lead, contact, or account.

We have an out-of-the-box field called Modified On that displays the date when a record was last updated. 

Modified On does not keeps a track of various activities like tasks, emails, phone calls that are associated with the actual records. We need a way to track when was the last activity completed for any record.

Demo Screenshots

Leads Date of Last Activity got updated as to when you create or update any associated activity.

Here is a solution that consists of a custom field and a few lines of JavaScript code.

1. Create a custom field : 'Date of Last Activity'

We need to create a field on every parent entity (like Account, Contact, Lead) where we wish to know when was the most recent or last activity performed.

Note : Field name must be the same for all respective entities where the Last Date of Activity needs to be updated or change the script accordingly.

new_dateoflastactivity

2. Create a custom JavaScript Webresource 

Jscript Content

function onSaveUpdateDateOfLastActivity(context)

{
var serverUrl = Xrm.Page.context.getClientUrl() + "/api/data/v9.1/";
var formContext = context.getFormContext();
var regardingObj = formContext.getAttribute("regardingobjectid");

if (regardingObj != null)

{

    var regardingObjValue = regardingObj.getValue();

    if (regardingObjValue != null)

    {           

        var entityTypeName = regardingObjValue[0].entityType;

        var entityId = regardingObjValue[0].id.replace("{", "").replace("}", "");

           

        var entity = {};

        entity.new_dateoflastactivity = new Date();

        var req = new XMLHttpRequest();

        req.open("PATCH",serverUrl + entityTypeName + "s(" + entityId + ")", false);

        req.setRequestHeader("OData-MaxVersion", "4.0");

        req.setRequestHeader("OData-Version", "4.0");

        req.setRequestHeader("Accept", "application/json");

        req.setRequestHeader("Content-Type", "application/json; charset=utf-8");

        req.onreadystatechange = function ()

        {

            if (this.readyState === 4)

            {

                req.onreadystatechange = null;

                if (this.status === 204)

                {

                    //Success - No Return Data - Do Nothing

                } else

                {

               // You can remove the below alert if you do not wish the customer to face such error.

                   Xrm.Utility.alertDialog(this.statusText 

               + "\n DateOfLastActivity field not found for an entity : " + entityTypeName );

                }

            }

        };

        req.send(JSON.stringify(entity));           

    }

}

}

3. Register OnSave Event on each Activity Type (tasks, emails, phone calls)

Remember to tick for Pass execution context as the first parameter

I hope you find this solution worthwhile, please let me know if you have any such business scenario and you are looking for any technical solution for it.

Thanks.

Vipin Jaiswal

Vipinjaiswal12@gmail.com

Most common JavaScript methods for Dynamic 365 CRM

In today's article, I will list out the most used JavaScript methods that are used in Dynamic 365 CRM. I am often being asked where to have a quick reference for learning client-side scripting in Dynamic CRM. Keeping that in my mind, I am listing few methods that one should be handy and ready with.

Note : A basic understanding of Dynamic CRM is a must to understand method and article consider only listing method with minimum description for methods.

Here is another blog post for having Guidelines to write good JavaScript code in Microsoft Dynamic CRM

Refer here for - Refreshing Rollup field using JavaScript

debugger;   // The one which is favorite to all who got stuck  

// Context Methods

var UserId      = Xrm.Page.context.getUserId();

var UserName    = Xrm.Page.context.getUserName();

var orgName     = Xrm.Page.context.getOrgUniqueName();

var clientUrl   = Xrm.Page.context.getClientUrl()

var currentUserRoles = Xrm.Page.context.getUserRoles();

context.getEventArgs().preventDefault();    // Explicity preventing the save.

//NOTE: context must be passed as a function param

// Xrm.Page.data.entity Methods

var entityId       = Xrm.Page.data.entity.getId();

var entityId       = Xrm.Page.data.entity.getId().replace('{', '').replace('}', '');

var entityName     = Xrm.Page.data.entity.getEntityName();
var entitySetName  = Xrm.Page.data.entity.getEntitySetName();

var isDirty        = Xrm.Page.data.entity.getIsDirty();

var AttrName       = Xrm.Page.data.entity.getPrimaryAttributeValue();

//Lookup set: one Statement

Xrm.Page.getAttribute("uomid").setValue([{ id: "69DAC10E-96BC-4F84-8BE9-29F84FF4401C", name: "Default Unit", entityType: "uomschedule" }]); // Unit Group

// Refresh and Focus

Xrm.Page.data.refresh();

Xrm.Page.ui.refreshRibbon();

// Page Save Methods

Xrm.Page.data.entity.save(); // ** Just saves current record

Xrm.Page.data.entity.save("saveandclose") // ** Does a save and close

Xrm.Page.data.entity.save("saveandnew") // ** Save and new

// Field Events

Xrm.Page.getControl("new_fieldname").setFocus();    // Setting Focus to a Field

Xrm.Page.getAttribute("new_fieldname").setSubmitMode("always"); // Explicit Save

Xrm.Page.getAttribute('new_fieldname').fireOnChange();  // Explicity calling FireOnChange event

Xrm.Page.getControl('new_fieldname').setDisabled(true);

Xrm.Page.getAttribute("new_OptionSetName").getSelectedOption().value;   // Get Selected Value from an optionSet

Xrm.Page.getAttribute("new_myfield").setRequiredLevel("none");          // Not Required

Xrm.Page.getAttribute("new_myfield").setRequiredLevel("recommended");   // Business Recommended

Xrm.Page.getAttribute("new_myfield").setRequiredLevel("required");      // Business Required

var formType = Xrm.Page.ui.getFormType();
var formType = executionContext.getFormContext().ui.getFormType();

// Create   1

// Update   2

// ReadOnly 3

// Setting Visibility or Enabling or Disabling

Xrm.Page.ui.controls.get("new_fieldname").setVisible(false);    // Hiding a Field

Xrm.Page.ui.tabs.get("tab_unique_name").setVisible(false);      // Hiding a Tab

Xrm.Page.ui.tabs.get("tab_5").sections.get("tab_5_section_4").setVisible(false);    // Hiding a Section

// Field Level Notification

Xrm.Page.getControl("fieldname").setNotification("message");

Xrm.Page.getControl("fieldname").clearNotification();

// Form Level Notification

MSCRM.ERRORNotification = "ERROR";

MSCRM.WarningNotification = "WARNING";

MSCRM.INFONotification = "INFO";

Xrm.Page.ui.setFormNotification("message", type, unique_number);

Xrm.Page.ui.clearFormNotification(number);

// Custom Filter Query for Lookup Control

Xrm.Page.getControl('eks_accountRating').addCustomFilter('');

// Open an Entity Form

Xrm.Utility.openEntityForm('incident', Xrm.Page.data.entity.getId());


// Open a Web-Resource
var windowOptions = { height: 400, width: 400 }
Xrm.Navigation.openWebResource("new_example",windowOptions);

// Business Process Flow Methods
var activeStageName      = Xrm.Page.data.process.getActiveStage().getName();

var selectedStageName    = Xrm.Page.data.process.getSelectedStage().getName();

"Next" "Previous" = executionContext.getEventArgs().getDirection()

// Binds javascript methods on BPF events
executionContext.getFormContext().data.process.addOnStageChange(functionname)
Xrm.Page.data.process.addOnStageChange(Opportunity.Functions.ValidateSelectedStageOnSave, executionContext);
executionContext.getFormContext().data.process.addOnStageSelected(functionname)

// SubGrid events

count = Xrm.Page.getControl("ln_subgrid_quotes").getGrid().getTotalRecordCount();

// Confirm or Prompt Dialog box 

var confirmStrings = { text: "Please confirm to put Opportunity On-Hold", title: "Confirmation Dialog" };

var confirmOptions = { height: 200, width: 450 };

Xrm.Navigation.openConfirmDialog(confirmStrings, confirmOptions).then(

    function (success)

    {

        if (success.confirmed)

        {

            base.attr.setValue(executionContext, "ln_onholddate", Date.now());

            Opportunity.Functions.ReadOnly(executionContext, true);

            base.attr.unlock(executionContext, "header_statuscode");

            base.attr.unlock(executionContext, "statuscode");

            console.log("Dialog closed using OK button.");

        }

        else

        {

            base.attr.setValue(executionContext, "statuscode", 1);

            console.log("Dialog closed using Cancel button or X.");

        }

    });

// Make form Read Only

ReadOnly(executionContext, true); // will make all field ReadOnly

function ReadOnly(executionContext, flag)

{

    Xrm.Page.ui.controls.forEach(function (control, index)

    {

        if (Opportunity.Functions.doesControlHaveAttribute(control))

        {

            control.setDisabled(flag);

        }

    });

}

function doesControlHaveAttribute(control)

{

    var controlType = control.getControlType();

    return controlType != "iframe" && controlType != "webresource" && controlType != "subgrid";

}

// Few Utility JavaScript Methods

console.log("Message: " + msg + ". Error: " + ex);

console.error("Message: " + msg + ". Error: " + ex);

alert("Message: " + msg + ". Error: " + ex);

var returnBool = !isNaN(monthVariable);

var month = parseInt(monthVariable);

 

// Formatting Date

this.setDateOptions = function () {

    Date.prototype.formatMMDDYYYY = function () {

        return this.getMonth() + "/" + this.getDate() + "/" + this.getFullYear();

    }

}

// Formatting Date and Time with AM / PM 

function formatAMPM()

{

    var date = new Date();

    var hours = date.getHours();

    var minutes = date.getMinutes();

    var ampm = hours >= 12 ? 'PM' : 'AM';

    hours = hours % 12;

    hours = hours ? hours : 12; // the hour '0' should be '12'

    minutes = minutes < 10 ? '0' + minutes : minutes;

    var strTime = (date.getMonth() + 1) + "/" + date.getDate() + "/" + date.getFullYear() + " " + hours + ':' + minutes + ' ' + ampm;

    return strTime;

}

// Validating Start and End Date

function ValidateMaintenanceEndDate(executionContext)

{

       var formContext = executionContext.getFormContext();

       var startDateField = formContext.getAttribute("new_maintenancestart");

       var endDateField = formContext.getAttribute("new_maintenanceend");

       var endDateFieldControl = formContext.getControl("new_maintenanceend");

       var startDate = startDateField.getValue();

       var endDate = endDateField.getValue();

 

       if (startDate != null && endDate != null)

       {

             startDate = new Date(startDate.toISOString().substr(0, 10));

             endDate = new Date(endDate.toISOString().substr(0, 10));

 

             endDateFieldControl.clearNotification("ErrEndDate");

 

             if (startDate >= endDate)

             {

                    endDateFieldControl.setNotification("cannot be before or equal to Maintenance Start.", "ErrEndDate");

             }

             else

             {

                    endDateFieldControl.clearNotification("ErrEndDate");

             }

       }

}

// Formatting Phone Number

function formatPhoneNumber(phoneNumber)

{

    if (phoneNumber.length != 10) {

        return phoneNumber;

    }

    var piece1 = phoneNumber.substring(0, 3); //123

    var piece2 = phoneNumber.substring(3, 6); //456

    var piece3 = phoneNumber.substring(6); //7890

 

    return kendo.format("({0})-{1}-{2}", piece1, piece2, piece3);

}

 

 

 

// Converting HTML content to Plain Text

function convertHtmlToPlainText(htmltext)

{

    htmltext = htmltext.replace(/<style([\s\S]*?)<\/style>/gi, '');

    htmltext = htmltext.replace(/<script([\s\S]*?)<\/script>/gi, '');

    htmltext = htmltext.replace(/<\/div>/ig, '\n');

    htmltext = htmltext.replace(/<\/li>/ig, '\n');

    htmltext = htmltext.replace(/<li>/ig, '  *  ');

    htmltext = htmltext.replace(/<\/ul>/ig, '\n');

    htmltext = htmltext.replace(/<\/p>/ig, '\n');

    htmltext = htmltext.replace(/<br\s*[\/]?>/gi, "\n");

    htmltext = htmltext.replace(/<[^>]+>/ig, '');

 

    return htmltext;

}

var temp = document.createElement("div");

temp.innerHTML = html;

return temp.textContent || temp.innerText || "";

 

myHTML.replace(/<[^>]+>/g, '');

// Comparing two Guids

function GuidsAreEqual(guid1, guid2) {

    // compares two guids

    var isEqual = false;

    if (guid1 == null || guid2 == null) {

        isEqual = false;

    } else {

        isEqual = (guid1.replace(/[{}]/g, "").toLowerCase() == guid2.replace(/[{}]/g, "").toLowerCase());

    }

    return isEqual;

}

// SET TIME OUT : Executes a function, after waiting a specified number of milliseconds.

// Syntax : setTimeout(function, milliseconds)

setTimeout(function () { Xrm.Page.ui.clearFormNotification("clearallnotification"); }, 10000);

// SET INTERVAL : repeats the execution of the function continuously.

var intervalId = setInterval(

    function () {

        if (loChildWin.closed) {

            clearInterval(intervalId);

        }

    },500);

// Encoding an URI Component

var _FilterT = "/TeamSet?$select=Name,TeamId&$filter=Name eq '" + encodeURIComponent(teamname) + "'";

Normal__URI  = "https://vicity.com/my test.asp?name=ståle&car=saab";

Encoded_URI = "https%3A%2F%2Fvicity.com%2Fmy%20test.asp%3Fname%3Dst%C3%A5le%26car%3Dsaab";

// Decoding a URI Component

var search = decodeURIComponent(location.search);

var params = search.substr(search.indexOf("?") + 1);

params = params.split("&");     // split param and value into individual pieces

// Converting String to a Byte Array

function packStringToByte(str) {

    var bytes = [];

    for (var i = 0; i < str.length; i++) {

        var char = str.charCodeAt(i);

        bytes.push(char >>> 8);

        bytes.push(char & 0xFF);

    }

    return bytes;

}

// Opens a Random Url

function OpenRandomUrl(url) {

    if (url != null)

        window.open(url);

    else

        alert("Page not found");

}

// Create Base64 Object

var Base64 = { _keyStr: "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=", encode: function (e) { var t = ""; var n, r, i, s, o, u, a; var f = 0; e = Base64._utf8_encode(e); while (f < e.length) { n = e.charCodeAt(f++); r = e.charCodeAt(f++); i = e.charCodeAt(f++); s = n >> 2; o = (n & 3) << 4 | r >> 4; u = (r & 15) << 2 | i >> 6; a = i & 63; if (isNaN(r)) { u = a = 64 } else if (isNaN(i)) { a = 64 } t = t + this._keyStr.charAt(s) + this._keyStr.charAt(o) + this._keyStr.charAt(u) + this._keyStr.charAt(a) } return t }, decode: function (e) { var t = ""; var n, r, i; var s, o, u, a; var f = 0; e = e.replace(/[^A-Za-z0-9\+\/\=]/g, ""); while (f < e.length) { s = this._keyStr.indexOf(e.charAt(f++)); o = this._keyStr.indexOf(e.charAt(f++)); u = this._keyStr.indexOf(e.charAt(f++)); a = this._keyStr.indexOf(e.charAt(f++)); n = s << 2 | o >> 4; r = (o & 15) << 4 | u >> 2; i = (u & 3) << 6 | a; t = t + String.fromCharCode(n); if (u != 64) { t = t + String.fromCharCode(r) } if (a != 64) { t = t + String.fromCharCode(i) } } t = Base64._utf8_decode(t); return t }, _utf8_encode: function (e) { e = e.replace(/\r\n/g, "\n"); var t = ""; for (var n = 0; n < e.length; n++) { var r = e.charCodeAt(n); if (r < 128) { t += String.fromCharCode(r) } else if (r > 127 && r < 2048) { t += String.fromCharCode(r >> 6 | 192); t += String.fromCharCode(r & 63 | 128) } else { t += String.fromCharCode(r >> 12 | 224); t += String.fromCharCode(r >> 6 & 63 | 128); t += String.fromCharCode(r & 63 | 128) } } return t }, _utf8_decode: function (e) { var t = ""; var n = 0; var r = c1 = c2 = 0; while (n < e.length) { r = e.charCodeAt(n); if (r < 128) { t += String.fromCharCode(r); n++ } else if (r > 191 && r < 224) { c2 = e.charCodeAt(n + 1); t += String.fromCharCode((r & 31) << 6 | c2 & 63); n += 2 } else { c2 = e.charCodeAt(n + 1); c3 = e.charCodeAt(n + 2); t += String.fromCharCode((r & 15) << 12 | (c2 & 63) << 6 | c3 & 63); n += 3 } } return t } }

// Encode the String                                

var EncodedString = Base64.encode(valuFromContext);

url = portNumber + "MyCustomPage/" + EncodedString;

Please let me know what the other common methods you used while scripting in Dynamic CRM.

Thanks,

Vipin Jaiswal