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

Hidden Tricks of the Workflow Update Step

One of the more powerful, yet under-utilised aspects of workflows is the update step. While many administrators know that it can be used to inject a value into a field, what is not commonly known is there are a few hidden functions that can also be used to manipulate the values in the fields, rather than just replace them.

For all fields, we always have the option of ‘Set To’ and ‘Clear’. For text fields, we also have ‘Append With’ and for int, float and money fields we have ‘Increment By’, ‘Decrement By’ and ‘Multiply By’.

To access these functions, we open the update configuration screen, click into the field of interest and drop down the Operator option in the Form Assistant.

For all of these operators, we can apply a dynamic value via the Add button in the Form Assistant or a static value by entering it into the Default value field in the Form Assistant and clicking OK.

If we want to take two values and update a third field, we will need to use two update steps. One to insert one of our values into the result field and the second update step to apply the second value to the value in the result field.

Finally, for those using version 4, you can still do most of these things. The only difference is there is no ‘Append To’ operator in CRM 4. To achieve the same result, you do an update step and set a dynamic value of “{original attribute} {additional text}”.

E-mailing Attachments En-Masse in CRM

At a pre-sales meeting recently, a client mentioned they deal with foreign students and sometimes need to send all attachments associated to a contact to a government department for reporting purposes e.g. visa records, passport information etc.

This had me stumped because, at the time, I could not think of a simple solution to get around this. Certainly you can add attachments to a contact record but to attach them to an e-mail would require saving each one to the desktop and then attaching them one-by-one to an e-mail (or a bunch of custom code). Even with the new integration to SharePoint in CRM 2011, one would need a custom solution to gather all the documents in the document store and add them to an e-mail.

When I got back to the office I had a flash of inspiration; what about sales literature? A new feature of CRM 2011 is the ability to send all the attachments associated with a sales literature record with one click.

Sure enough, not only can I create a relationship between contacts and sales literature but I can rename sales literature to something more appropriate (Student Documents).

To print, we open up the sales literature record and click the ‘Send as E-mail’ button

Now this solution is not perfect. The two biggest downsides are that you have to retype in contact’s e-mail address once you click the ‘Send as E-mail’ button and I can find no way to rename the ‘Sales Attachments’ label. However, given the potentially expensive alternatives, this provides a lot of bang for minimal buck. As an added bonus, we can use this technique to cluster different kinds of documents for the contact and only print one cluster at a time. Perhaps the clusters are different reporting bodies or perhaps we want to have a set of official documents and a set of internal documents. for each one we simply create a new sales literature (student document) record.