Saturday, February 19, 2011

The Seven Ages of Dynamics CRM Reporting

I have just spent three days locked in a room with a client working out the reports they need to come out of CRM. What I got out of the exercise, other than copious notes and writer’s cramp, was a much richer appreciation of the ways we can report out of CRM.

There are, as far as I can tell, seven different ways we can report out of CRM, all with different strengths and weaknesses. Here they are in rough order of complexity.

1. Advanced Find Views and System Views


If we need to slice and dice our data and list the resulting records, it does not get much better than this. These can be set up for everyone (system views) or users can do it themselves and save the results (advanced find view/personal view). We can browse up to parent records (show me all contacts working for a company in the retail industry) and down into child records (show me all companies with sales opportunities over $50,000).

The big downside is there is no aggregation of data. That is, we cannot ask for the sum or average amount of a money field. For that we need something more powerful. Also, we cannot directly print. To print we need to export to Excel and print from there.

2. Entity Forms


This is a bit of a ‘left field’ kind of report. In the workshops, the client wanted a summary form, similar to the out-of-the-box ‘Account Overview’ report, except for opportunities. Different milestones, during the sales process, needed different summaries of the opportunity. With the multiple form capability of CRM 2011, we can now create different forms for our form-based report and then print them by going to File-Print Preview.

The downside here is that the forms are quite rigid in their formatting and can only show fields from that record (or grids of records from child records). For example, an opportunity form cannot show fields from the client record.

3. Word Merge


In some ways this is the extension of printing a form. The word template allows us add graphics, macros and fields from parent records.

The big disadvantage here is it is really hard to add in child records. It can be done, as evidenced by the out-of-the-box quote report, but it is not a simple process. Also, depending on the complexity, it may be simpler to create a SQL Reporting services (SRS), but more of that later.

4. Dashboards


Brand new to CRM 2011 are dashboards. We can drill through to sub-records, we can change the layout and we can mix and match grids, web pages and graphs, all without a single line of code. For displaying data from multiple sources in CRM the dashboard is an excellent choice.

What is the downside? Printing. I am yet to find a way to print a dashboard short of a screenshot. This is a shame because in so many other ways they are great.

5. Dynamic and Static Excel Worksheets


The next level up from views for tabular reporting, Excel worksheets are one of my favourite reporting tools. Practically any list in CRM can be exported to Excel for further manipulation. We can add formulae, formatting and graphs. We also have the option of a static worksheet (a direct dump of the data) or a dynamic worksheet (this sets up a connection between the spreadsheet and the CRM database such that every time the spreadsheet is opened, the data refreshes with the latest information).

There are not many limitations with Excel spreadsheets and most users have enough Excel knowledge to make them deliver exactly what they are after (or know the office Excel guru who can help them out). The only limitation I can think of is we can only export one entity record set at a time but, for most tabular reports, that should be fine.

6. Dynamic Excel Pivot Tables


If you do not know about Pivot Tables and you work with large sets of data, such as is available in a CRM system, you need to learn. Pivot Tables allow us to look at our data, comparing different fields against each other. I once had a client who did not know about Pivot Tables and tried to create a table like the one above using lookup formulae. It took them hours to get right. Using Pivot Tables, this takes minutes to put together.

In the above, I am looking at the status of various leads compared to when they were entered into the system. In one table it becomes obvious that most leads are still new, most were entered in on 9/2/2011 and most have not yet been qualified.

As with worksheets, these are exported with a dynamic connection back to the database so they refresh every time they open.

Again, if this is the format we are seeking for our reporting, there are not too many disadvantages with Pivot Tables, other than getting used to them and being able to work with only one entity record set.

7. SQL Reporting Services (SRS)


The ‘Rolls Royce’ of CRM reporting, this is a free add-on to SQL Server which integrates with CRM. We can access multiple data sources, display the data in either a tabular or form-based format, add in graphics or practically anything else we want. There are very few limitations with what we can do with SRS reports.

The big limitation is to create an SRS report requires technical knowledge beyond the scope of most users. While there is a report wizard within CRM, this is quite limited in what it can achieve and is only good for tabular reporting.

Summing it up

In the case of my workshop, we decided that most of the client’s requirements could be met with Excel reporting. Even though some of their reports were form-based, the client was happy entering in an ‘opportunity number’ to isolate the record of interest and then having an extra worksheet full of ‘vlookups’. When reviewing reporting, consider this table to see which type of report may be right for you.

  Advanced Find and System Views Entity Forms Word Merge Dashboards Excel Worksheets Excel Pivot Tables SRS Reports
Tabular Format Yes No Generally No Yes Yes Yes Yes
Form-Based Format No Yes Yes Generally No Generally No No Yes
Charts No No Generally No Yes Yes Yes Yes
Images and additional content No No Yes Yes Yes Yes Yes
Access Parent Record Fields Yes No Yes Yes Yes Yes Yes
Display Child Records No Yes Generally No Generally No No No Yes
Multiple Data Sources No No No Yes Generally No No Yes
Data Aggregation No No Generally No Yes Yes Yes Yes
Drill-through Yes Yes No Yes No No Yes
Printable Yes, indirectly through Excel export Yes Yes No Yes Yes Yes
User Configurable Yes Generally No Yes Yes Yes Yes Generally No


Michael Dodd said...

There is also the option of going "external" to the native CRM experience by creating Report Models for use with the native SSRS 2008 Report Builder UI.
This allows a reporting guru to create pre-baked formulas, aggregates and other reporting metrics to be dispolayed in a drag-drop format for the end user that can then "slice and dice" the data (similar to building a PivotTable report in Excel, but much simpler and more direct, since it is render in it's own UI and not a worksheet).

Weifeng Jiang said...


Tom said...

Hi, better late than never. Have you ever found a way to print a Dashboard from CRM without doing a screenshot? I've heard rumblings off doing it through javascript but cannot find an example anywhere.

Leon Tribe said...

Hi Tom,

No unfortunately the only way I know is to screenshot it.