Archive

Posts Tagged ‘Charting’

Custom Charting Capabilities in Microsoft Dynamics CRM 2011

February 4, 2011 2 comments

Microsoft Dynamics CRM 2011 comes with in-built visual analytics. You can create charts to view aggregations of your CRM data over various dimensions, providing nice visual and interactive tools to analyze data. But, there is more to it.

It also provides you with some space alongside the records-grid. While this space is generally used for the built-in charts, it can potentially be used for well, anything. CRM 2011 also goes a long way in defining an extensibility story – a way to deploy your own custom pages on the server. Yes, you’ve got it right – I am referring to web resources. Would it not be great if you could get your web resource to show up alongside the grid? Well, that is precisely what we let you do.

Create your own custom chart

Let us start by creating a simple “Hello World” page, and making it show up alongside the Accounts grid.

1. Open your favorite text-editor, and type in the following HTML:

<html>

  <head>

    <title>Hello World Page</title>

  </head>

<body>

  <div style="height:49%" ></div>

  <div style="text-align:center">

  <b>A 'Hello World' web resource for the Accounts grid</b>

</div>

</body>

</html>

This is an elementary HTML page that shows a line of bold text centered horizontally as well as vertically.

2. Create an HTML Web Resource on CRM Server by uploading the above page. In case you are not familiar with web resources, you will need to do the following:

a. Navigate to Settings Customizations. Click on Customize the System to open up the Default Solution.

b. Click on New → Web Resource on the grid tool-bar to launch the Web Resource form.

c. Upload the file created in (1) above. Give the web resource a name, say new_helloWorld.

d. Click Save.

clip_image002

3. Now we will create a custom chart that uses this web resource. We can do so easily by using the Import Chart feature:

a. Navigate to Accounts grid and open the chart pane.

b. Click on Import Chart in the Charts tab of the ribbon.

clip_image004

Here’s the XML we want to import:

<visualization>

  <primaryentitytypecode>account</primaryentitytypecode>

  <name>Hello World</name>

  <webresourcename>new_helloWorld</webresourcename>

</visualization>

Note that the XML definition above contains a reference to the web resource created in (2).

Once the chart is successfully imported, we will see the following:

clip_image006

That was pretty easy, wasn’t it? But that was not a very useful chart though. Let us move on to something better.

Multiple charts sharing the same web resource

Consider a hypothetical problem. Let us say, I have accounts spread across different countries in the world and I would like to see their locations on a map beside the accounts grid. I would not want one map cluttered with all the accounts – rather I would want to focus on one country/region at a time, and see accounts located in only that country. To solve this problem, I would want to create different charts – each centering the map on a particular country (with a zoom factor according to the size of the country) and displaying only accounts from that country.

It would be possible to create multiple web resources addressing different map regions – however, that is not a very scalable solution. Creation of a web resource is generally a system customizer’s job – we would not want a system-customization for every new country/region that we would want to include.

Instead, what we will do is create a single ‘parameterized’ web resource. The web resource will contain the general logic for plotting accounts on a map – however, this logic would be driven by certain parameters which would be provided at runtime by the chart that is being rendered.

How does a web resource know which chart is being rendered?

If you have worked with web resources placed on forms/dashboards, you would be aware of the data query-string parameter. This parameter essentially lets the same web resource render differently in different contexts – you specify the value of this parameter (if required) at the time of adding the web resource to a particular form/dashboard.

For web resources rendering as charts, we do not let you explicitly specify a value for this parameter. Instead this parameter is always set to (a URL-encoded form of) the following format:

visid=<Visualization ID>&vistype=<Visualization Type>&viewid=<View ID>&viewtype=<View Type>

Therefore, we can get the ID and type of the chart being rendered by parsing the data query-string parameter.

Data and Presentation Descriptions of the Chart

Both System and User Charts have the following two fields.

  • DataDescription: This defines the data that is to be shown in the chart.
  • PresentationDescription: This defines the presentation and formatting of the chart.

For charts that use the in-built charting capabilities of CRM 2011, the values of the above fields have well-defined syntax and semantics. For our custom set of charts, we will continue using these fields to represent the data and presentation properties of the chart – however, we will define our own syntax and semantics for these fields based on our needs.

  • DataDescription: The country/region whose accounts are to be shown on the map.
  • PresentationDescription: This will need to convey two things: the coordinates of the center of the map, and the zoom factor. Let this be a comma-separated list of three numbers – a latitude, a longitude, and a zoom factor.

So here’s the basic idea – the web resource first figures out which chart is being rendered, then it reads the data and presentation descriptions of that chart, and parses them appropriately to extract the parameters that it needs to render the map at runtime.

Going into more detail, we will be doing the following in sequence.

  • Parse the query-string to extract the value of the data parameter. Parse this value again to obtain the ID and type of the visualization being rendered.
  • Retrieve DataDescription and PresentationDescription fields of the visualization using CRM oData end-point.
  • Parse the PresentationDescription to obtain the values of latitude, longitude and zoom-factor. Use Bing Map APIs to load a map with these values.
  • Get the country/region specified in DataDescription. Retrieve all Accounts from this country/region by making a GET request to a CRM oData URI with appropriate filters.
  • Geocode each of the retrieved account locations using Bing Map APIs, and plot them on the map.

Here’s one way you may write the web resource.

<html>

<head>

    <title>Accounts on Bing Maps</title>

    <script type="text/javascript" src="http://ecn.dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.3"></script>

    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>

    <script type="text/javascript" src="ClientGlobalContext.js.aspx"></script>

 

    <script type="text/javascript">

        var map;

 

        // Function to construct key-value pairs from a query string.

        function getParametersFromQuery(query) {

            var parametersDictionary = new Array();

            var parameters = query.split('&');

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

                var keyAndValue = parameters[i].split('=');

                parametersDictionary[unescape(keyAndValue[0])] = unescape(keyAndValue[1]);

            }

            return parametersDictionary;

        }

 

        // Function that makes a GET request to the CRM REST end-point, and invokes a callback with the results.

        function retrieveFromCrmRestApi(url, callback) {

            $.ajax({

                type: "GET",

                url: GetGlobalContext().getServerUrl() + "/XRMServices/2011/OrganizationData.svc" + url,

                contentType: "application/json; charset=utf-8",

                dataType: "json",

                success: function (data) {

                    callback(data.d);

                }

            });

        }

 

        // Function that retrieves the corresponding CRM chart, and invokes the callback when successful.

        function loadChartFromCrm(callback) {

            var parameters = getParametersFromQuery(window.location.search.substring(1));

            parameters = getParametersFromQuery(parameters["data"]);

 

            var id = parameters["visid"].substr(1, 36);

            var type = parameters["vistype"];

            var url = (type == "1111" ? "/SavedQueryVisualizationSet" : "/UserQueryVisualizationSet")

                + "(guid'" + id + "')?$select=DataDescription,PresentationDescription";

            retrieveFromCrmRestApi(url, callback);

        }

 

        var locations = new Array();

        function plotAccountLocations(accounts) {

            if (accounts.length > 0) {

                var account = accounts.pop();

                var address = account.Address1_City + ', ' + account.Address1_Country;

                map.Find(null, address, null, null, 0, 1, false, false, false, false,

                    function (shapeLayer, results, places, moreResults, error) {

                        if (null != places && places.length > 0) {

                            var place = places[0];

                            var newShape = new VEShape(VEShapeType.Pushpin, place.LatLong);

                            newShape.SetTitle(account.Name);

                            newShape.SetDescription(address);

                            locations.push(newShape);

                        }

                        // When we have found (or not found) the current account,

                        // recursively call the same function to find the next one.

                        plotAccountLocations(accounts);

                    });

            }

            else {

                var shapeLayer = new VEShapeLayer();

                map.AddShapeLayer(shapeLayer);

                shapeLayer.AddShape(locations);

            }

        }

 

        function loadAccountsFromCrm(dataDescription) {

            var url = "/AccountSet?$select=Address1_Country,Address1_City,Name";

            if (null != dataDescription) {

                // Filter accounts based on country specified in data description.

                url += "&$filter=Address1_Country eq '" + dataDescription + "'";

            }

            retrieveFromCrmRestApi(url,

                function (data) {

                    var results = data["results"];

                    var accounts = new Array();

                    for (resultKey in results) {

                        accounts.push(results[resultKey]);

                    }

                    // Once accounts are retrieved from CRM Server, plot their locations on map.

                    plotAccountLocations(accounts);

                }

            );

        }

 

        function getMap(presentationDescription) {

            // Set center and zoom defaults.

            var center = null;

            var zoom = 4;

            if (null != presentationDescription) {

                // Calculate map-center and zoom from the presentation description.

                var arguments = presentationDescription.split(',');

                if (arguments.length > 1) {

                    center = new VELatLong(arguments[0], arguments[1]);

                }

                if (arguments.length > 2) {

                    zoom = arguments[2];

                }

            }

            map = new VEMap("map");

            map.LoadMap(center, zoom, VEMapStyle.Road, true, VEMapMode.Mode2D, false, 0);

            window.onresize = function (event) { map.Resize(document.body.clientWidth, document.body.clientHeight); };

            window.onresize(null);

        }

 

        function loadMap() {

            // First, get the chart object from CRM Server.

            loadChartFromCrm(

                function (chart) {

                    // Once we have retrieved the chart, format the map based on the chart's presentation description.

                    getMap(chart.PresentationDescription);

                    // Get Accounts from CRM Server based on the chart's data description, and plot them on the map.

                    loadAccountsFromCrm(chart.DataDescription);

                }

            );

        }

    </script>

</head>

<body onload="loadMap()">

    <div id="map"></div>

</body>

</html>

The code-snippet above uses a bunch of asynchronous callbacks – however, the basic sequence of invocation of functions is the following.

  • loadMap: Handler for the body.onload event.
  • loadChartFromCrm: Retrieve DataDescription and PresentationDescription of the current chart from the CRM 2011 oData end-point. Note the double-call to a function that parses a query-string.

var parameters = getParametersFromQuery(window.location.search.substring(1)); parameters = getParametersFromQuery(parameters[“data”]);

[The value of the “data” parameter is essentially a query-string within a query-string.]

  • getMap: Use Bing Map APIs to load a map with the center and zoom-factor specified in presentation-description.
  • loadAccountsFromCrm: Retrieve account records from CRM 2011 oData end-point, filtered by the country specified in data-description.
  • plotAccountLocations: Add push-pins on the map to represent each of the account records retrieved above.

Now let us go ahead and create a web resource on CRM Server with the above HTML. Let us call it new_accountsOnMap. It would now be pretty simple to create various charts using it. Let us start with Accounts in the U.S.

Import the following chart Xml:

<visualization>

  <primaryentitytypecode>account</primaryentitytypecode>

  <name>Account Locations in U.S.</name>

  <datadescription>U.S.</datadescription>

  <presentationdescription>39.8,-98.5,4</presentationdescription>

  <webresourcename>new_accountsOnMap</webresourcename>

</visualization>

This is what you will see:

clip_image008

 

Similarly, the following chart Xml gives you Accounts in India.

<visualization>

  <primaryentitytypecode>account</primaryentitytypecode>

  <name>Account Locations in India</name>

  <datadescription>India</datadescription>

  <presentationdescription>21,78,4</presentationdescription>

  <webresourcename>new_accountsOnMap</webresourcename>

</visualization>

clip_image010

Essentially, you have created your own parallel charting infrastructure for CRM 2011 that others can use to create system or user charts as appropriate. Like any other chart, such charts will automatically show up in the chart selector beside the records grid. And yes, they can also be placed on dashboards if so desired.

I have made available for you a Microsoft Dynamics CRM 2011 Managed solution containing the web resource and two system charts based on it.

Cheers!

Arko Nasipuri