3. Exporting Dynamic Data to Excel PivotTables
In addition to letting you export data to dynamic Excel worksheets, Microsoft Dynamics CRM lets you export data
to Excel PivotTables. Excel PivotTables give you the ability to
cross-tabulate data to produce summarized reports.
Some people initially struggle with the concept of PivotTables.
Consider the following example, in which you have this table of
activities:
This table consists of flat data in columns and rows. Flat data
generally serves as the basis for a PivotTable. With a data set of
this size, you can easily count the records to summarize the data in a
variety of ways. For example:
-
Four of the activities are phone calls.
-
Mike Snyder is the owner of two activities.
-
Three activities have a due date of 9/7/2010.
However, as your data set grows, summarizing the data at a
glance becomes impossible. With a PivotTable, you can eliminate the
manual calculation and pivot the data to get the
answers. The following table is an example of a PivotTable on the flat
data sample.
With the PivotTable, you can easily see how many activities
exist for each owner by type and in total. It also becomes clear how
many total activities exist by type. You could have pivoted the data
by due date rather than by owner or activity type to see aggregates by
due date.
Important
Although the concept of PivotTables might be intimidating at
first, when you have gained familiarity with the process, you will be
able to create powerful reports very efficiently. Similar to a dynamic
worksheet, a dynamic PivotTable establishes a live link with your
Microsoft Dynamics CRM database. Report setup is a one-time investment
that you can benefit from continually. And learning how to use Excel
PivotTables can help you solve reporting needs from other
business-critical applications as well.
In this exercise, you will export data to a dynamic PivotTable
to organize and summarize your Microsoft Dynamics CRM data.
Note
SET UP Use your own Microsoft
Dynamics CRM installation in place of the site shown in this
exercise. Use the Internet Explorer web browser to navigate to your
Microsoft Dynamics CRM website, if necessary, before beginning this
exercise.
-
In the Workplace area, click
Activities.
The default activities view, My Activities, appears.
-
Click the Export to Excel
button.
The Export Data To Excel dialog box appears.
-
Select Dynamic PivotTable.
Notice that the Select
Columns button becomes active.
-
Click the Select Columns
button.
The Select PivotTable Columns dialog box appears. The
columns that appear in the My Activities grid are selected by
default.
-
Select the check box for the Activity
Status field, and click OK.
This selection will make the Activity Status field available
in the PivotTable.
-
Back in the Export Data to
Excel dialog box, click Export.
The File Download dialog box appears.
-
Click Open.
This opens Excel and displays an empty PivotTable.
Important
You might get a security alert beneath the ribbon in Excel
showing that data connections have been disabled. You can enable
the content by clicking the Options button and selecting Enable
This Content.
-
In the PivotTable field list on the right side of the
screen, drag the Due Date field to
the Row Labels section. Then drag
the Due Date field to the Values section.
The PivotTable shows a count of activities by due
date.
Tip
While you are counting records, you can summarize numeric
information in a variety of ways by changing the calculation type. To change the calculation type, click the arrow next to the
field in the Values section and click Value Field Settings. For
example, you can modify the settings to summarize the data by
Sum or Average.
-
Drag Due Date from the
Row Labels section to the Column Labels section.
The same data is now pivoted in the opposite
direction.
-
Drag the Activity Type field
into the Row Labels section.
The count of activities by due date for each activity type
is now displayed.
-
Drag the Priority field into
the Report Filter section.
Priority now appears at the top of the PivotTable as a
parameter. When you make a selection in the Priority field, the
PivotTable results will refresh for records with the selected
priority.
You can continue to add fields into the PivotTable report to
refine your analysis.
