In part 1 of this 2-part series, I demonstrated how to use the CRM 4.0 Report Writer to create charts that illustrate how your data are distributed across categories. It’s relatively straightforward when you’ve got numeric data that can be “summarized”, but a little less obvious when you don’t.

For example, what if you want a count rather than a sum? Here are some of the more frequent examples I see of this requirement:

• Contacts by sales rep
• Cases by case type or category
• Accounts by industry or territory

The built-in Report Writer doesn’t have a “count” function, so you need something you can sum up. I do this with a custom attribute. Here’s a heuristic step-by-step approach (using the Account by Territory example), that I demonstrate in the video below:

1. Create a custom attribute on Account, call it “counter”.
2. Give it a type of “int”, max and min values of 1.
3. Create an automatic workflow on the create trigger to set the value to 1 for new account records going forward.
4. Figure out the best way to populate that value into already existing records (I use a workflow in the video)
5. Then create the report, using the same steps I showed in part 1. This figure shows the most important step of the Report Wizard, the “Lay Out Fields” dialog:

Here’s the video. Enjoy!

Charting CRM Data Across Categories

You can use the CRM 4.0 Report Writer to easily build graphical summaries of your Dynamics CRM data across categories: accounts by owner or territory, sales by product or pipeline stage, etc.

In my case, however, this only seemed “easy” once I understood how the report writer works! (funny how often it works like that)

In this new “Ten-Minute Tips” format, I’m going to depart from my usual detailed (a.k.a. long!) step by step explanations and try to boil it down to a single key step of the process.

So, assuming you understand the basics of how to create and save reports, if you want to build charts, you need to understand the innocently-named “Lay Out Fields” step of the Report Wizard. I’ll illustrate with a report that displays a bar chart of estimated revenue (using the Opportunity entity). I want to see a “sales pipeline”, and in the example I use the built-in “Status Reason” attribute for the stages of a sales process.  

The notes in this figure summarize this as pithily as I’m capable of:

For more detail…

Here’s the Video

My son thinks this ten-minute video is the third-geekiest ever posted to YouTube, but if you need to create charts like this, you might find it useful: