Show consolidated summation of values in Funnel chart in Dynamics CRM

Yes, I saw exactly the same line in the draft customer requirement sheet. My prompt reply would have been ‘No’ to the customer for this requirement. But as a consultant, we all know the perils of saying a direct ‘No’ to the customer. Smile

To be specific to the requirement, we had a  dashboard showing a funnel chart for opportunities with opportunity estimated value for different stages. What the customer wanted to show a sum of the values for all the opportunities just below the chart. As usual, we tried to propose them so many alternatives but customer was adamant to having the solution the way they wanted.

So here I was scratching my head over the internet trying to find a possible solution, preferably OOB to achieve the same. However to my disappointment I could find anything. So I decided to try it all out myself. So here is the approach I followed to achieve the same.

Before you move ahead and read the solution, one thing to let you all know. The below steps are all unsupported customizations and might not work with a future update for Microsoft CRM.

So if you are reading this line, you are also on the same boat as I am Smile. So without wasting further time, let us jump to the solution straightway.

Step1:

Create a HTML web-resource and place it in a section on the dashboard with the minimum width and height possible so that it does affect the appearance of the dashboard. The purpose of this web-resource is to read the funnel chart when the dashboard loads.

Step 2:

Enter the jscript code needed to read the funnel chart section. Below is the sample of the HTML file and the full javascript code. Please note that you have to refer jQuery.js and XrmServiceToolkit.js file in the code. Both these files are available for download from internet.

<html>
<head>
    <title></title>
    http://../scripts/libraries/jquery.js
    http://../Scripts/XrmServiceToolkit
   
        function attachEventToFrame() {
            setTimeout(“setEvents();”, 3000)
        }

        function setEvents() {
            //  debugger;
            var contentIFrame = window.top.document.getElementById(“contentIFrame0”);
            if (contentIFrame != null) {
                // get the dashboard frame.
                var dashboardFrame = contentIFrame.contentWindow.document.getElementById(“dashboardFrame”);

                if (dashboardFrame != null) {
                    // get all the iFrames under the DashboardFrames.
                    var chartIFrames = dashboardFrame.contentWindow.document.getElementsByTagName(“iframe”);
                    var regEx = /^\w+_vizIframe$/;

                    for (var count = 0; count                         if (regEx.test(chartIFrames[count].id)) {
                            var frame = chartIFrames[count];

                            // get the crmchart element.
                            var crmchart = frame.contentWindow.document.getElementById(“CrmChart”);

                            if (crmchart != null && crmchart.alt == “Chart: Funnel Value by Stage“) {

                                // run a fetchxml query and get the sum of opportunity value
                                var fetch = “” +
                                               “” +
                                                 “opportunityvalue
‘ aggregate=’SUM’ alias= ‘Opportunity_Value’/>” +
                                             “” +
                                             “”;
                                //var value = ”;
                                var FetchedValue = XrmServiceToolkit.Soap.Fetch(fetch);
                                var value = FetchedValue[0].attributes.Opportunity_Value.formattedValue;
                                $(crmchart).parent().append(“
Sum :” + value + ““);
                                $(frame).on(‘load’, iframeloaded);
                            }
                        }
                    }
                }
            }
        }

        function iframeloaded(e) {
            debugger;
            var frame = $(this).get(0);

            var crmchart = frame.contentWindow.document.getElementById(“CrmChart”);
            $span = $(crmchart).parent().find(“#sum_total_custom”);

            // run a fetchxml query and get the sum of opportunity value
           
var fetch = “” +
                           “” +
                             “‘opportunityvalue
aggregate=’SUM’ alias= ‘Opportunity_Value’/>” +
                         “” +
                         “”;

            //var value = ”;
            var FetchedValue = XrmServiceToolkit.Soap.Fetch(fetch);
            var value = FetchedValue[0].attributes.Opportunity_Value.formattedValue;

            if ($span.length > 0) {
                $span.html(“Sum :” + value);
            }
            else {
                $(crmchart).parent().append(“
Sum :” + value + ““);
            }
        }
   
</head>
<body onload="attachEventToFrame();">
</body>
</html>

You can copy and paste the same in your HTML webresource. However please pay attention to the highlighted variables

  • attacheEventToFrame: Function called on body onload
  • opportunityvalue: the schema name of the field on which you want the summation in the opportunity funnel chart
  • fetch: put the same fetchxml xml that is used by the opportunity funnel chart
  • Chart: funnel value by stage: This is basically the title of the chart. If your chart is showing some different title, you should use the same title here.

Now when you publish your dashboard you should see like below.

 

image

 

And when you enlarge it, below is the screenshot.

image

 

The summation here is just a dummy. So adding not necessarily will add up to the total value Smile

P.S – This will work only when you view the chart from the Dashboard. However if you open this chart from the context of a view, it would not work.

Hope this helps!

Advertisements

Author: Debajit

I am a Dynamics CRM Most Valuable Professional (MVP) with 10 years of experience in Microsoft .NET Technologies and 7 years of dedicated experience in Microsoft Dynamics CRM. I have worked with companies like Microsoft, SanDisk, PwC, TMF Group and have extensive experience of implementing complex CRM solutions from both offshore and client side. Currently the face of XrmForYou.com with significant experience in delivering corporate training on Dynamics CRM and have already delivered multiple projects to client through XrmForYou.com Author of multiple tools on codeplex including the 'Role Based Views' and 'CRM-Sharepoint Metadata manager & Attachment Extractor' which are available for commercial use under XrmForYou.com For consulting/ training, drop me a note at info@xrmforyou.com or visit our website www.xrmforyou.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s