Accessing “Special” Activity Data with CRM Report Wizard

Sometimes a data model that is perfectly valid on a logical level does not enable the system end users to actually leverage the data stored in it. One example of such a design is the way Microsoft Dynamics CRM handles the information collected on the standard (uncustomizable) dialog windows used in the case resolution and opportunity close processes. While the information collected here would often be valuable for managing the business process and analyzing the results (“what information was provided to the customer while closing the case?” or “why did exactly we end up losing this opportunity?”), it cannot be easily accessed in a way that would show data from more than a single record at a time.

This is due to the fact that the case resolution and opportunity close information is not recorded onto the actual business entity itself but rather onto a related activity. There are specific activity types for both of these processes that get created once a user clicks OK on the respective dialog for setting the business record status as closed. This makes sense if we think about the lifecycle of a record like case or opportunity, since the closure is not necessarily a permanent end state. The user can reactivate a case or reopen an opportunity and continue working on it if the circumstances and the business process guidelines dictate this to be the correct route of action, in which case there will eventually be more than one close activity for the business record. The data model therefore needs to support a 1:N relationship between these entities, which is why the design of the out-of-the-box business processes in Dynamics CRM is justified.

The unfortunate side effect of this design is that the system cannot easily produce views of closed cases with both the question and answer information, as these are stored on separate entities. It is equally difficult to view and analyze information regarding won or lost opportunities, as any comments entered by the opportunity owner during the closure event are not available on the opportunity record itself. What makes the situation even more unfortunate is that the Advanced Find UI does not surface these “special” activity types and make them available for custom views, so even extracting the data from the system for ad-hoc analysis in Excel sheets is not directly possible.

One approach that I often recommend to customers is to develop additional business logic that will store the information about the latest case resolution or opportunity close onto custom fields on the case/opportunity entity. I’ve also written a blog article earlier about how ISV tools like North52 Business Process Activities (formerly known as Formula Manager) can be used for building a no-code customization to better leverage case resolution data. This of course will not cover any records created prior to deploying the customization, so accessing historical information is still a challenge.

Reporting on Case Resolution Data

As always, by developing a custom SQL Server Reporting Services report you could access almost any data in CRM and present it exactly the way you want. You’ll need to use Visual Studio and know a thing or two about how to develop SSRS reports for CRM if you take this approach. In the standard user interface of Dynamics CRM there is only the Report Wizard feature available, which in many cases offers quite limited options for designing reports that would go beyond what the inline charts in CRM views can do. This Wizard was originally introduced back in CRM 4.0 when there was no charting or dashboard capability included in Dynamics CRM yet. Once CRM 2011 brought in these new visualization options, the Report Wizard was pretty much abandoned in terms of new functionality development, so today it remains sitting there in its 2007 outfit and looking a bit outdated as a result.

One of the lesser known qualities of the Report Wizard is that you can actually access certain entities and fields with it that are off limits to Advanced Find. This comes in quite handy when dealing with a scenario like the one I described earlier. So, let me show you how to build a Report Wizard report that will provide you better access to case resolution data.

When creating a new report and choosing Report Wizard as the type, you’ll first be taken into a dialog window where you can choose 2 entities that you’re allowed to use in the report. By selecting Activities as the primary record type we’re presented with a list of possible related record types that includes also the “hidden” entities like Opportunity Close or Case Resolution. For our purposes, let’s select the resolutions.

Now we get to the filter criteria screen. Let’s say that we want to build a report on the billable time information recorded into the case resolution entity. We’ll only be interested in resolved cases and case resolutions that contain data in the Time Spent field (this is where the billable time field data in the case resolution dialog gets stored in).

Since we picked the generic Activity entity as the primary record type, we’ll want to narrow it down to only those records where Activity Type equals Case Resolution. Even though we’ve only selected Activity and Case Resolution as the entities in the report, our filter criteria can still access any other entity that’s around them. By choosing the Regarding Case of the activity we could set up a criteria for showing resolutions from only specific types of cases, but for now we’ll just settle for the Resolved Status.

Next it’s time to design the report layout. In a large data set it’s helpful to have the report rows grouped by certain categories, which is what you can do in the top left corner of the layout editor. I’ve selected the Actual End field from the activity as the grouping criteria and defined the grouping to be done on a monthly level.

In the report columns section we can add fields from a variety of different sources, as can be seen from the image below. We’re free to add in fields that are stored on the regarding case record, such as the Owner and Customer. The very reason we’re building a Report Wizard report is that we can now also access fields of the Case Resolution entity, which you couldn’t do in Advanced Find. Let’s select from there the Subject field as well as Time Spent. For the latter one we’ll set the values to be shown as a sum.

Report Wizard allow us to build a report that combines both a chart and a data table. Since we happen to be working with a data set that contains a numerical field (whole number) for Time Spent, we have the option of charting it to summarize the data visually, so let’s take advantage of this. Our grouping option will be populated into the X axis by default and the only numerical field of Time Spent is offered for the Y axis. This suits our needs perfectly, so no editing of options will be required in this screen.

Once we save the report, we can now go and run it to see the results (sorry, no fancy report previews shown in the editor, it’s a “back to 2007” style UX). Depending on the contents of our database and any possible additional criteria configured for the report, we should see a column chart that sums the amount of time spent (in minutes) per our grouping field (actual end month).

Clicking on a bar will take us to the detailed data table. Here we can see the list of case resolutions for the selected month, along with details of who handled the case, for which customers. Subject and Time Spent information from the case resolution records can be viewed in a convenient list, allowing us to scan the entries made by our customer service reps.

You can export the report to Excel, but all the nice layout features will follow you there, so some editing may be required to get access to the raw data for further analysis. You can also click on the Edit Filter button anytime you like to add further criteria to narrow down the resulting data set.

How About Opportunity Close Data?

The process for reporting on information recorded via the opportunity close dialogs will be mostly the same as the one described for case resolutions. If we’re reporting only on non-numerical data, using a chart is not possible (no, Report Wizard can’t build a chart for simply counting records, as strange as it may seem), so that part of the process can be skipped. In the new report’s record type definition dialog screen choose again Activities as the primary record type and set Opportunity Close Activities as the related record type. Then apply the necessary criteria for filtering out other activity types and opportunities that still remain open.

For the report layout we can use a fairly similar strategy as with the Case Resolution report. Actual End is again a good monthly field to group and sort the data. Alongside this we can include another grouping field from the regarding opportunity record, Status, to display opportunity close details separately for won and lost deals. The more detailed status reason information is actually also available from directly the opportunity record, but the final column Description is something we should pick out from the opportunity close entity. Depending on what other opportunity category fields are relevant for our analysis, the resulting report on Opportunity Close data might look something like this:

Exploring with the layout screen of the Report Wizard may reveal some further options that you can use for arranging the data into a more illustrative output, such as record counts, sorting, grouping, averages on numeric fields and so on. As we can see from these examples, even though the Report Wizard no longer has a very central role in the current Dynamics CRM product, it can still prove to be a useful tool in scenarios where Advanced Find and charts fall short.