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.
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 short video below shows a brief demo of the end result:
Making this work
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.
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.
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:
Some other resources that will help you make interactive dashboards:
- Editing the CRM Sitemap: http://msdn.microsoft.com/en-us/library/cc150833(v=MSDN.10).aspx
- CRM Report Writers Guide: http://msdn.microsoft.com/en-us/library/bb955081(v=MSDN.10).aspx
CRM MVP Matt Wittemann
His CRM blog is at http://icu-mscrm.blogspot.com