An Interactive Funnel on a Microsoft Dynamics CRM Dashboard
Guest blogger and CRM MVP Matt Wittemann is the COO at North Carolina (USA) based C5 Insight, a Microsoft Dynamics CRM and SharePoint consulting practice. A five-time MVP award recipient, he has been working with CRM since shortly after its initial release, and has architected and deployed numerous CRM implementations, including one that received a national award from Microsoft. He is a frequent speaker, writer and contributor to the CRM newsgroups and user community.
Dashboards are always an integral part of CRM, and recently I was inspired by the sales funnel in the Microsoft Dynamics CRM Online dashboard to incorporate similar functionality for an on-premises project I was working on.
Figure 1 – The CRM Online Dashboard Showing a Sales Pipeline Funnel
In CRM Online, if you click the sales funnel, the “Open Opportunities” view opens in a new page. Even though you can set it up to show a selected list below the charts, the list doesn’t have any interaction with the charts that are displayed.
I wanted to go a step further. In the sales dashboard I was building, I liked the idea of having the Opportunities list right below my charts, but I also wanted it to be more interactive: click a part of the sales funnel, and have the corresponding opportunities displayed beneath it. For example, if the blue part of the funnel represents opportunities that are in the “Discovery” stage, then I want the view beneath to refresh to show the opportunities in that part of the funnel.
The dashboard consists of a simple HTML page, Salesdashboard.htm, which is stored in the ISV folder in the CRM website root. (The Sitemap is edited to add a link to this webpage to CRM’s main navigation.) The HTML basically consists of some iFrames to hold the three parts of my dashboard, my sales funnel, a gauge, and a view from CRM.
(Notice that my dashboard charts were not uploaded into CRM. Rather, I uploaded them directly to the SSRS website, and the links to them contain some parameters that tell the charts how to render and not display the standard toolbars. Also, note that the iframe containing my default Opportunities view has both an id value and a name. The javascript below will only be able to reference this iframe as the target based on the “name” value.)
I created views for the Opportunity entity that corresponded to the sales stages that my funnel will show (Discovery, Solution Development, Proposal Preparation, etc.). Each of these views has its own URL, which you can get by navigating to the view, clicking the More Actions menu item, and copying its shortcut.
Figure 2 Copying the Shortcut to a Particular View
In SQL Server Reporting Services 2008, where I built the sales funnel chart (sorry, funnels aren’t an available chart type in SSRS 2005), it’s easy enough to add links on a chart to a URL. Right-clicking on the data series brings up the properties dialog, where you can specify the action you want to happen when a user clicks on that area of the chart.
Figure 3 Series Properties for the Sales Funnel
But you run into a couple of problems pretty quickly: 1) I have more than one URL since my sales funnel represents six different sales stages, each with its own view; and 2) My sales funnel is displayed inside an iframe, and the Opportunities are in another iframe – there’s nowhere to set the target for the URL in the chart Actions so it knows where to load the view I want.
Here’s what makes this all work:
1) The first problem can be overcome by storing the relevant view shortcut URLs in the report. I copied the shortcuts for all of my views and stored them in my funnel report as parameters, for easier reference. Each parameter looks something like this:
2) The second problem can be overcome by embedding some javascript behind the Action for the chart. Back on the “Action” options for the Series Properties, I chose the “Go to URL” option, and used the following function:
