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.

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 . 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.

And when you enlarge it, below is the screenshot.

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

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!

Related