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