Dynamics CRM 2011 has lots of important new feature, and the most visually obvious one is probably visualizations: the general term for the charts, dashboards and other ways of graphically representing the information in your CRM database. In this article I’ll start by providing a brief survey of the primary visualization techniques in Dynamics CRM 2011. Following that, I’ll drill down on what is probably the least obvious of these approaches, but potentially one of the most useful.
Survey of CRM 2011 Visualization Techniques
I count four main approaches to visualizing CRM 2011 information. The only caveat here is that these are all out of the box techniques, that is: things you don’t have to pay extra money for! Judging by the large number of BI/Analytics vendors exhibiting CRM 2011 add-ons at the recent Convergence show, there are plenty of additional techniques if your requirements outstrip what the core feature-set can provide. For now, though, let’s stick to the out of the box features and see how far they get us.
Approach #1: Inline Visualization
This is probably the most obvious one, since you can see it whenever you navigate to a Dynamics CRM 2011 data grid. For example, navigate to opportunities, expand the Chart Pane to the right of the grid, and select from any of the available charts for a graphical version of the data in the currently selected view. Here’s what these look like:
Approach #2: Reports
While you might think of reports as primarily non-graphical, that’s not really correct. Even the Dynamics CRM 2011 Report Wizard allows you to create charts for aggregated data, and if you can branch out a little and use SQL Server Reporting Services, you can go flat-out chart crazy. And by default, reports created with the Report Wizard are available from forms of the record type the report is created for, and will automatically use the current record as a filter for the report. In my opinion, this is a great feature and one that isn’t used as much as it should be. For example, I wrote a custom report, “Account Revenue History”, which displays a column chart of booked opportunity revenue, grouped by account. By default, it’s available on the Run Report menu from the account form, and it will filter the report by the current record:
Approach #3: Dashboards
If you’re the inquisitive type and you’ve had any experience with CRM 2011, you’ve probably seen these, since they’re pretty hard to avoid! (If you haven’t yet, click Workplace and then Dashboards.) Dynamics CRM 2011 dashboards are forms, and in fact they’re the first forms we’ve seen in Dynamics CRM that are not tightly bound to data the way account, contact and opportunity forms are. Dashboards contain components, which can include charts but also a few other things such as Lists, Web Resources and IFrames. Dynamics CRM 4.0 gave users the ability to create personal dashboards, but as I pointed out in an article about that, the users who need dashboards the most are the users least likely to want to create them. So one big win in CRM 2011 is the ability to create System Dashboards that can be centrally deployed and pushed out to whoever needs to see them.
Here’s a custom dashboard I use a lot. It’s based on information from some custom entities supplied by CoreMotives, an add-on I use for email marketing and analytics. I’ve got CoreMotives-supplied tracking code hooked up on the Trick Bag, so, right within my production CRM 2011 I can track web analytics like page views, tie them back to lead or contact records and all manner of cool stuff. But this article’s about visualizations, so here’s my Web Content Trends dashboard:
Approach #4: Record-Centric Dashboards
While it’s true that dashboards are forms, it’s also true forms can be turned into dashboards. I’m not crazy about the term I came up with to describe them, but I definitely like the functionality, which is what the rest of this article describes and shows you how to do. The following figure shows a customized version of the account form, displaying information from related records (opportunities, in this example) in a dashboard format. In the next section I’ll describe how to build a record-centric dashboard like this:
Imagine navigating to an account record, opening its form, and seeing a collection of charts and other visualizations with information associated to the account: sales history and forecast, service cases, activities and the like. That’s what I mean by the term record-centric dashboard, and while it applies equally well to any record type, I’ll illustrate it here with accounts. The approach I take utilizes an important new feature in Dynamics CRM 2011: the ability to create multiple forms per entity.
If you’ve seen demonstrations of this, chances are they were for what’s being referred to as “role-tailored forms”. This term generally refers to creating customized data entry experiences, appropriate to different users’ roles. For example, executives and outside sales reps might need a simple version of an opportunity form to enter the basic information about a sales opportunity; analysts and inside sales reps might need a more complex version of the opportunity form to back-fill the important details the heavy-hitters don’t have time for.
In scenarios like this one, different versions of the opportunity form will be assigned to different security roles. Users assigned to those security roles will only see the form appropriate to their role.
Record-centric dashboards, on the other hand, use forms for an altogether different function: rather than data entry, it uses forms for data display. In this scenario, a single version of the account form will be used by everybody in the organization with permissions to add or edit account records. A second version of the form will be available but won’t be used for any data entry: this will be our dashboard form.
There’s another new CRM 2011 feature that will come in handy here: Sub-Grids. The more I use these the more I like them. The most common use of sub-grids is to display a data grid of child records related to a parent record (e.g., on the account form display a sub-grid of related opportunity records.) But for the dashboard scenario, we’ll use the ability of a Sub-Grid to display a chart that visually depicts aggregates for the child records, rather than the records themselves.
Here’s how to create a record-centric dashboard like the one I showed in the previous figure. I’ll break it down into __ main parts:
Part 1: create a new form for the account record type to serve as the dashboard form:
- Navigate to the accounts data grid, click the Customize tab, and then click Customize Entity.
- Click Forms, and then click the first form in the list with a Form Type of Main. Double-click it to open it,
- Click Save As on the ribbon, type “Dashboard” in the Name field, and then click OK.
You’re now editing the new Dashboard form. Remove everything from the form except for the Account Name field.
Hint: The quickest way to do this is to select each of the tabs in turn, starting with the Details tab, and click Remove on the ribbon. The default account form has five tabs: General, Details, Contacts, Notes & Activities, and Preferences. The Details, Contacts, and Notes & Activities tabs can all be removed. By default the Preferences tab has the Owner field on it. It’s a required field, so move it to the General tab first and then remove Preferences. Then, remove the Description, Shipping Information, and Address sections from the General tab. Finally, from the Account Information section on the General tab, remove all fields except for the required ones (by default, Account Name and Owner). When finished, your “dashboard” form should look like this, in the Form Editor. Notice I’ve removed everything from the Header section also:
To see an even more dramatically empty form, preview it. Here’s the entire form before you add anything to it:
Part 2: Add the Dashboard components:
- Go back into the form editor if necessary, make sure Body is selected on the ribbon, and click the Insert tab.
In the Tab section, click One Column to insert a one-column tab. Double-click the tab to open the Tab Properties dialog, and enter Sales in the Label field. Accept the rest of the defaults so it looks like this:
Then click OK.
By default, when you insert a tab, the system inserts a section, which you need to contain fields, controls and so forth. If you look, you’ll notice that the default section has two columns. Double-click inside the Section area to open the Section Properties dialog, and then click the Formatting tab on the dialog.
On the Formatting tab, select the Four columns option. This may come down to taste in form design, but I like the four column option since it provides the most flexibility in the layout options for the components of the dashboard. And when it comes to dashboards, you generally want lots of flexibility when it comes to layout options for dashboard components. Here’s what it should look like, just before you click the OK button:
- If you want to add more tabs, feel free, but my articles are generally too long anyway, so I’ll just do one tab for now and you can generalize it from there!
Part 3: Add Sub-Grids to display the dashboard’s charts:
- Make sure you’re inside the Sales tab, click the Insert tab on the ribbon, and then click Sub-Grid.
You’ll see the List or Chart Properties dialog. As you can see from the following figure, there are a lot of properties you can tweak. Here are the Display properties I used in this example:
Here are a couple of the most important points about these properties:
- The chart – Actual Revenue by Month – is a custom chart I created, since the system charts didn’t quite have what I wanted.
- Notice I’ve selected the Show Chart Only option, and de-selected Display Chart Selection. I was going for a controlled presentation so users would not be able to do things like select charts that might not “work” in this context.
Click on the Formatting tab. Again, since controlling the presentation is important for dashboards, properties like these can take some experimenting to get right. The chart I’m currently configuring is going to end up in the upper-right part of the tab, it will span the second two columns and have sixteen rows.
Click OK to close out of the dialog.
After that I added two more components to my account dashboard; the process is similar to what I just showed, and for each one I needed to create a custom chart.
Part 4: Modify the Form Properties and Navigation and Assign Security Roles
Let’s take a look at the finished product again for reference. After I get everything just right and publish the entity, I can effectively toggle back and forth between the out of the box Information form and the new Dashboard form. Here’s the standard form:
Then use the Form Selector, shown in the following figure, to select the Dashboard version of the form:
Here are a few of the finishing touches I did in the form editor to get it as close as I could:
- Notice I removed all of the links in the Common, Marketing and Processes sections in left navigation. You can actually remove navigation entirely. That’s what I wanted to do since it would free up all of that space for the dashboard. The problem with that is…then you cannot access the form selector, so you wouldn’t be able to toggle back and forth between the forms! There’s probably a work-around for this. Let me know if you come up with one!
When you create a custom form the way I showed above, by default it’s only available to users with the System Administrator and System Customizer roles. Since the purpose of this little exercise was to create a dashboard form that would be available to everybody, I needed to click the Assign Security Roles button in the form editor, and select the Display to Everyone option:
This makes the dashboard form available to any user who has read privileges to the account entity. It does not override your security model, so if a user cannot see accounts, they won’t be able to see the dashboard form either!
- One final note: notice the All-Time Sales chart in the upper left part of the dashboard. As many of you know, it’s unfortunately difficult in Dynamics CRM to get all-up totals of child records from the context of the parent. There are certainly other ways to do it, but here’s one way that works in the dashboard context: I created a custom bar chart for Won Opportunities that groups the data by customer. When you filter a chart like that by the customer record – as the record-centric dashboard approach does – you get the sum total of Won Opportunities across all time for the account record. Normally I won’t tolerate charts with a single data point, but in this case I’ll make an exception!