Use The Right Tool For The Job in Dynamics CRM

Microsoft Dynamics CRM offers users a variety of methods for users to analyze data. Out of the box we have views, filters, advanced find, record counts on views, report builder, charts, dashboards, Excel exports, pivot tables, and other options.

With all of these tools, it is important to have correct expectations and use the right tool for the job.

Remember the law of the instrument. Just because a tool works well for one problem doesn’t mean that it will be the solution to all problems. Also, just because a tool doesn’t work for a given job doesn’t make it a worthless tool.

Take Advanced Find—the beauty of Advanced Find is that it is approachable by users who are not familiar with writing SQL queries. It allows just about any user to build personal views joining multiple entities together. It is both powerful and approachable.

But that doesn’t mean that Advanced Find is the answer to every query question. For very simple filters, Advanced Find may be too powerful, and something like the filter capabilities may be more appropriate.

For very large data sets and very complex filter logic, Advanced Find may also not be the complete answer. For example, Advanced Find returns a list of records but does not aggregate them. If you want to get a total of how much money your customers spent on a specific product over the past ten years, Advanced Find will get you the list of orders, but will not total the amount spent.

The advanced find may be part of the answer, but in this scenario, it is not the entire answer. You can run the Advanced Find and then aggregate the totals using tools like charts or export to Excel and aggregate the totals, or save the Advanced Find and use it as the starting point of a report in the report wizard (which can give totals).

Some tips for determining the right tool for the job:

Be aware of the limitations of various tools. For example, charts are limited to 50,000 records and the view row counter only counts up to 5,000 records. These limitations are designed with performance in mind. If you increase the fetch limit to 500,000, your charts can aggregate
larger datasets, but dashboard and application performance will be less than desirable.
Discover what questions users will want to ask in CRM—this will help determine which tool is optimal for a given question. If users want a list of customers in their territory who have
purchased a product in the past year, Advanced Find is a great fit. If they want to know who has purchased product A but has not purchased product B, Advanced Find is not the best fit.
Once users query the data, what will they want to do with it? I can run an Advanced Find that returns one million records, but what do I want to do with those records? Typically someone isn’t going to want a granular export of 1,000,000 records, they want to aggregate or total the
results in an actionable number.
Don’t ignore the Pivot Table. My favorite reporting tool in CRM is the dynamic pivot table—I can take a list of records and group and pivot the table to quickly answer many different types of questions. Teach a user how to use pivot tables and you will vastly expand the possibilities of what he can do with the data in CRM and empower the user.
For larger data sets and more complex analysis, other Microsoft tools can be useful. This is where SSRS Reports, Analysis Services, and PowerPivot come in. Don’t be afraid of these tools.
Don’t overcomplicate things—use the tools mentioned in number 5 for more complex analysis and larger datasets, however, don’t use a report when a view will work. Use the right tool for the job.

Microsoft Dynamics CRM includes very powerful out of the box business analysis capabilities and also works with other business intelligence tools in the Microsoft stack. By knowing what questions your users want to ask, you can choose your tools appropriately for the job at hand.