Microsoft Dynamics CRM Team Blog: Using Power View in Excel 2013 to Analyze CRM Data

[Blog bot]

Источник: 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