Dynamic Reports with Microsoft Dynamics CRM with Pivot Tables

Microsoft Dynamics CRM 2011 provides native capability to allow you to export any screen or view into Excel. That export can be a static file with just the data on the screen as a snapshot or as a Dynamic export in the form of an Excel Pivot Table. You have an option to select the fields you want to include in the Pivot Table. Once you have exported the view as a Dynamic Pivot Table, you can then slice and dice the data for further analysis and save the Pivot Table report for future use.

You can open the saved Excel workbook at a later time and refresh it (Data > Refresh from CRM or Refresh All) to update the Pivot Table to reflect the up-to-date information from Dynamics CRM 2011 without having to log into Dynamics CRM. This is incredibly powerful as the report is now on your desktop and every time you open the Excel file, you will have updated numbers.

Here’s an example of a Dynamic report showing the Open Sales Opportunities. The Pivot Table shows open sales opportunities in various pipeline phases by territory. Note the “Refresh from CRM” and “Refresh All” buttons in the Data ribbon. Whenever I want to refresh the data, I would just click the button and my graphs and data will refresh to give me the latest sales pipeline.

The Pivot Table in this Dynamic report shows Open Sales Opportunities in various sales pipeline phases. By clicking the “Refresh from CRM” and “Refresh All” buttons in the Data ribbon, the data and graphs refresh to give the latest sales pipeline.

If you want to make this Pivot Table report available to the other CRM users, you can upload the Excel workbook containing your report to Dynamics CRM. You do this by simply going to the reports area in the workplace and start creation of a new report – specify that it is for an existing file and browse to the location where you saved your Excel workbook and your Excel report containing the Dynamic Pivot Table; it is now available to the other users.

The data displayed in the Pivot Table will depend on the user’s privileges set in CRM. For example, when a user opens the Excel workbook created in the example above (open opportunities by territory), the user viewing this report would see only the opportunities that he or she can otherwise see in CRM.

Dynamic Worksheets and Dynamic Pivot Tables require the use of the Excel add-in which is part of the Microsoft Dynamics CRM 2011 for Microsoft Office Outlook client. You can download the Microsoft Dynamics CRM 2011 for Microsoft Office Outlook found here.

You can take advantage of Pivot Table reports using the valuable data in CRM, and find the answers to questions like:

1. Which territories performed best and what products are they selling?
2. Which products are selling best over time?
3. What are the top reasons for why we lost opportunities?
4. What are the Win and Loss ratios by Lead Source?
5. Which Marketing Campaigns performed well?
6. Who were the top performing Customer Service Representatives (based on case resolution)?

Both a PivotTable report and a PivotChart report enable you to make informed decisions about critical data in your Dynamics CRM system. You can learn how to visualize summary data in a PivotTable report, and to easily see comparisons, patterns, and trends here.

This post is written by Abi Shende. Abi is a Team Leader in Microsoft Dynamics CRM with Ignify. Ignify is winner of the Microsoft Partner of the Year in 2011 and provides CRM, eCommerce and ERP, software solutions to businesses and public sector organizations. Ignify has been included as the fastest growing business in North America for 5 years in a row by Deloitte, Inc Magazine and Entrepreneur Magazine and ranked as one of 100 most innovative companies in the world in the Red Herring Global 100 in 2011. If you are seeking assistance to maximize your efforts with your CRM project contact Ignify at 562-219-2001 or email us at [email protected].

By Ignify, a Microsoft Dynamics CRM partner in California