Источник:
http://blogs.msdn.com/b/crm/archive/...-crm-data.aspx
==============
Now that Power View is built into Excel 2013, business users can easily perform advanced analytics of their real time CRM data in either an online or on-premise deployment. You can use the Export to Excel feature in Microsoft Dynamics CRM to export CRM data, and then you can create a matrix, bar chart, data map, etc. with a few simple clicks. In addition, you don’t need to work in the IT department to perform these steps, as this functionality is designed to be run by the end user.
Note: This procedure reflects the use of Excel 2013 with Microsoft Dynamics. When performing this procedure, please ensure your Account list view contains records have valid values for
Relationship Type,
Industry,
Country, and
Territory and that these columns are contained within your list view.
To use Power View with Microsoft Dynamics CRM Online to perform BI Analytics on CRM data, perform the following steps.
Note:The steps below are shown in the Outlook client but can also be performed for static exports within Internet Explorer.
1. In Microsoft Dynamics CRM for Outlook, view the list of Accounts.
Note: It is preferable to use a view that is already configured to display the
Relationship Type,
Industry,
Country, and
Territory fields. While you can add them to the view during the Dynamic Export process, the example will flow better if these fields area already exposed within the view.
2. On the Ribbon bar, click
Export to Excel.
3. In the
Export Data to Excel dialog box, select
Static Worksheet, and then click
Export.
4. In the
File Download dialog box, click
Open to open the file in Excel.
Important: If you receive the prompt below, click
Yes to proceed.
In the body of the worksheet, you should now see the data from the list view.
5. Save the file in the latest Excel Workbook (.xlsx file) format.
Note: Power View controls cannot be inserted into an .xml file, but you can insert them in an Excel workbook.
Now we need to select all records from the CRM query.
6. With your mouse, select all data values in Excel.
7. On the Ribbon bar, on the
Home tab, in the
Styles section, select
Format as Table, and then select a table style option.
With the table created, we can begin to insert our power View controls.
8. On the Ribbon bar, on the
Insert tab, click
Power View.
Note: If the
Microsoft Excel Add-ins dialog box appears, click
Enable.
This will add a worksheet named
Power View1 to the Excel workbook.
Now let’s restrict the columns displayed on the Power View1 worksheet.
9. In the top-right corner of the screen, under
Power View Fields, deselect all check boxes except for the ones associated with the following fields:
- Client Name
- Industry
- Relationship Type
10. On the Ribbon bar, on the
Design tab, click
Table, and then from the drop-down list, select
Matrix.
11. On the right, under
Drag fields between areas below, configure text box titles and associated fields as follows:
- ∑ Values - Client Name (displays as # Count of Client Name)
- ROWS - Country / Region
- COLUMNS - Relationship Type
12. Resize the matrix so that is occupies the left half of the window, as shown in the following graphic:
Now let’s create a chart.
13. On the
Power Vew1 worksheet, select the matrix and then copy and paste it (using the keyboard copy [Ctrl + C] and paste [Ctrl + V] functions) to the right of the existing matrix.
The worksheet now contains side-by-side copies of the same matrix.
14. Select the matrix on the right-hand side, on the
Design tab, click
Bar Chart, and then from the drop-down list, select
Stacked Bar.
15. Resize the chart control, under
AXIS remove
Country/Region, and then add
Territory.
After completing this step, the window should appear similarly to the following graphic:
Note: You can now filter on the matrix values by clicking on the bar sections in the chart.
16. Click on the chart bar representing Latin America.
The matrix on the left is updated to display only the selected data, which in this case is that for Latin America.
17. Click the white space of the chart to turn off the filter and show all values.
Now let’s create a Bing Map in a separate Power View worksheet.
18. Switch to the Accounts worksheet and if necessary, select
that the table you created in steps 6 and 7.
Note: If the
Insert Power View dialog box appears, select
Create a Power View sheet, and then click
OK.
A new Power View worksheet is inserted in the workbook.
Now let’s restrict the columns displayed on the
Power View2 worksheet.
19. In the top-right corner of the screen, under
Power View Fields, deselect all check boxes except for the ones associated with
the following fields:
- Client Name
- Industry
- Relationship Type
20. On the Ribbon bar, on the
Design tab, click
Table, and then from the drop-down list, select
Card.
21. Resize the card so that is occupies the left third of the window, as shown in the following graphic:
Now let’s create a map.
22. On the
Power View2 worksheet, select the card, copy and paste it (using the keyboard copy [Ctrl + C] and paste [Ctrl + V] functions) to the right of the existing card, and then resize it so that it occupies two-thirds of the window.
The worksheet now contains side-by-side copies of the same card.
23. With the right card selected, on the Ribbon bar, on the
Design tab, click
Map.
24. With the Map control selected, on the right, under
Drag fields between areas below, configure text box titles and associated fields as follows:
- ∑ Size - Client Name (displays as # Count of Client Name)
- LOCATIONS - Country / Region
- COLOR - Relationship Type
When finished, the window should appear similarly to the following graphic:
Note: You can now filter on the card values by clicking on pie charts of the specific countries within the map.
25. Click on the pie chart for Germany.
The card on the left is updated with values only for the selected data, in this case that for Germany.
26. Click the white space of the map to reset the filter to all values.
Summary
As you can see, the Power View functionality built into Excel 2013 enables business users to easily perform advanced analytics of their CRM data, whether they are working in a cloud-based or on-premises deployment.
Jack Bender, Solution Architect
Microsoft Dynamics CRM
Источник:
http://blogs.msdn.com/b/crm/archive/...-crm-data.aspx