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). (more…)