What is Power View?
Power View is an add-in feature of Microsoft Excel 2013 and is also an add-in for SQL Server 2012 Reporting Services. Power View offers additional visualizations that can easily aggregated as a dashboard with robust filtering, sorting, and formatting features to provide the user nearly endless options. Power View is not related to the PowerObjects PowerPack tools, but a very powerful reporting feature of the Microsoft BI suite.
In this post we will focus on connecting CRM data with Excel and getting started with basic elements as well as setting up a cool bubble chart to ‘play’ our data.
How to Build a Power View Report
Most organizations have Excel power users that are familiar with the ‘Export to Excel’ functionality, which will be our first step.
1. Select a view – in this example, we chose Opportunities – and Export to Excel checking the Dynamic worksheet option. The fields we will include will be:
Owner, StatusCode, Close Probability, Estimated Close Date, Customer, and Estimated Value
2. Save the xml file.
3. Locate the saved file, right-click, and select to open with Excel.
4. In order to see data, you may need to click Refresh from CRM on the Data tab.
Note: You must have the CRM Outlook Client configured to this organization!
5. Next, save the sheet as an Excel Workbook (*.xlsx).
6. To create the Power View report, we need to connect to our exported data. From the Data tab, select Existing Connections and next Tables. Select your exported data and click Open.
7. In the Import Data window, select Power View Report and click Ok.
8. You will now be on a new Excel tab with the Power View workspace.
9. In our Opportunity Dashboard, first select Estimated Value and change the new chart to Stacked Column.
10. Drag Close Opportunity to the axis. Ensure Estimated Value is in Values.
11. Next we’ll create a second chart starting with Estimated Value.
12. Change the new chart to a Pie.
13. Drag Owner to Color
14. Start a third chart using a Scatter chart type.
15. We will use this configuration of fields:
16. Drag the size to be the full width.
17. Give it a meaningful title, and your Dashboard should look something like this. Click the Play button of the bottom chart and you can see the scatter bubbles move and enlarge based on the progress of time.
18. Click on any piece of the Owner pie and all three component will reflect our selection:
19. Save as a report in CRM for others to use!
By leveraging SQL Server 2012 Reporting Services and Microsoft SharePoint Server, you can take advantage of additional Power View functionality and delivery capabilities including the Power View Report Builder tool, the ability to publish workbooks to a gallery in SharePoint, and tabular models deployed to SQL Server Analysis Services.
For more information about CRM reporting, see these posts:
- How to Customize Colors in Dynamics CRM Charts
- CRM 2011 Reporting Options – On-line and On-Premise
- Microsoft Dynamics CRM Online – Currency Fields in FetchXML Reports
- Microsoft Dynamics CRM Pre-Filtering for CRM Reporting
Happy CRM’ing and Reporting!