Accessing “Special” Activity Data with CRM Report Wizard


Posted on 9th April 2014 by Jukka Niiranen in Tips

, , , , ,

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…)

Configure Outlook Contact Synchronization Settings on Behalf of Your Users


Posted on 4th April 2014 by Jukka Niiranen in Tips

, , , ,

The ability to get your CRM contacts automatically synchronized to your Outlook address book and mobile phone is a very handy feature in Microsoft Dynamics CRM. Why would you ever manually dial the phone numbers of your customers when you can just set them to appear in your smartphone’s list of contacts? Wouldn’t it also be nice to see the customer’s name instead of just an unfamiliar phone number when he or she calls you after your first meeting? These are examples of everyday benefits that not only save time but can also help drive CRM user adoption by showing tangible benefits of having the customer information managed centrally in a database shared by all employees that are in contact with the customers.

The new server-side synchronization in CRM 2013 has made the synchronization process independent of the Outlook client, so that updated information flows between your phone and CRM even when your PC is not online. What has not changed, though, is the lack of an administrative control panel for configuring the synchronization settings for the users. Since CRM by default only offers API calls to manipulate the synchronization filters, most users are likely using the default synchronization settings that come with CRM out of the box (unless they’ve received thorough training on how to configure the filters in the personal options menu). These are not always the optimal way to get the most benefit out of the synchronization functionality.

Once again, XrmToolBox comes to the rescue with its recent Sync Filter Manager addition. This tool gives system administrators the power to create and change the Outlook and offline filters on behalf of the end users. Now you can easily plan the proper synchronization strategy and deploy the correct settings to all users in a controlled manner, instead of needing to visit their PCs or try to get everyone to click the right options.

Since the concept of synchronization filters and templates may not be so easy to grasp for the CRM admin, I decided to make a tutorial that walks you through a few common scenarios for contact management. These cover enabling more than just the single owner of a contact to have the record synchronized to their address books, as well as how to avoid users in administrative roles from getting thousands and thousands of miscellaneous contacts suddenly appearing in their Outlook and mobile phone. You can view the presentation below or go to SlideShare and download it from there if the embedded slides are not showing up.

As a great follow up reading, I recommend you to also have a look at the article on contact management best practices that was recently published in the new CRM Virtuosity blog. This will show you how to take the Outlook contact synchronization even further via modifications to the contact form’s Command Bar in CRM 2013, among other things.

Working with Price List Items in Dynamics CRM

1 comment

Posted on 31st March 2014 by Jukka Niiranen in Annoyances |Tips

, , , , , ,

Despite of the recently refreshed user interface of Dynamics CRM 2013 that offers a much more fluid user experience than previous versions, there are still areas in the application that are not very user friendly. Many of these revolve around product and price information, regarding how it is presented and what actions are allowed on it. In this blog post I will drill into a common scenario that organizations who use CRM for managing price list data may run into and present a few options on how to make their lives easier.

Price List and Price List Item Views

A pet peeve of mine in Dynamics CRM has always been the UI that the Price List entity offers to the end user. As many of the readers of this blog will surely know, price list items are the way how products, units, price lists and the all important price figures come together in the CRM data model. If you want to leverage the product catalog and any price calculation features in the sales module, you’ll need to work with price list items and create at least one of them per each product you plan to include as line items on your opportunities, quotes, orders and invoices.

Unless you’ve built a custom integration to a back-end system that will automatically provide the latest pricing information for CRM, there’s quite a bit of work involved in maintaining individual price list item records when prices change or new products or lists are introduced as a normal part of the day to day business. When a CRM user opens a price list record, a reasonable assumption to make would be that he or she is interested in reviewing the pricing information given to the included products. Unfortunately the Dynamics CRM UI does not make such an assumption, rather it thinks the user is interested in only viewing a list of products and their units but not the actual price information in the amount field. Here’s what the default associated view of the price list items gives us:


Well, that sure looks like a good candidate for some entity customization work. Yes, it does, but there’s a “but”. When you open the customization UI and navigate to the price list item entity, you discover that the views are actually not customizable. Nor can you add any of your own views for that matter, which means you’re stuck with the default UI. If you think that the price list item entity should allow view customization, then there’s a suggestion on Microsoft Connect that you definitely should go and vote for (if you need help in registering to Connect itself, see this post).

Exporting the Price List Item Data to Excel

With this limitation in mind, what are our options of producing a true price list view with product and price information shown side by side? For any Dynamics CRM power user the first thing to come to mind will surely be to export the data into Excel. Unfortunately the uncustomizability of the Price List Item entity also means it has been blocked from showing up in Advanced Find, which would normally be our tool of choice for preparing a CRM data export.

Luckily there’s still an Export to Excel button visible in the ribbon of the price list form when we are viewing the associated price list items view. Clicking this will present us with an option to either export the data in static format (which would just give us the same columns as the current view) or to create a dynamic Excel sheet in two possible formats. Both of the latter options, pivot table and worksheet, present a follow-up dialog where choosing the required columns from the price list item entity and even any parental entity like product is possible.


When you export the view into a dynamic Excel sheet in an on-premises CRM environment, you can actually go and look at the SQL query that the view is using for pulling the data from CRM to Excel. Just click “Change Data Source – Connection Properties – Definition” and copy the query from the Command Text window into Notepad. With a little tweak that removes the reference to the currently viewed price list record we can use the same dynamic Excel sheet to retrieve price list item data for all the price lists in the system.


In the SQL query you’ve copied to Notepad you’ll find a reference to the price list from under which we exported the related price list items. It will look something like this: where  (“productpricelevel0″.pricelevelid = N’CEA84006-AD7B-E311-9405-00155D6214FA’) . Just remove this whole where clause, thus expanding the query to retrieve all records from the price list items table in CRM, regardless of the associated price list. Then with the Excel pivot table tools you can group and filter the data any way you please, effectively creating a price list report that views the latest information from CRM in a layout that best suits our purposes. (more…)

Switch to our mobile site