POWEROBJECTS

718 Washington Ave. N. Suite #101
Minneapolis, MN 55401
View map and all Locations

Send us a message

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.

addons

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

Building a Power View Report with CRM Data

2. Save the xml file.

open_opportunities_retouched

3. Locate the saved file, right-click, and select to open with Excel.

Power View Report

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!

Power View Report

Power View Report

5. Next, save the sheet as an Excel Workbook (*.xlsx).

Power View Report

Power View Report

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.

Power View Report

7. In the Import Data window, select Power View Report and click Ok.

Power View Report

8. You will now be on a new Excel tab with the Power View workspace.

Power View Report

9. In our Opportunity Dashboard, first select Estimated Value and change the new chart to Stacked Column.

Power View Report

10. Drag Close Opportunity to the axis. Ensure Estimated Value is in Values.

Power View Report

11. Next we’ll create a second chart starting with Estimated Value.

Power View Report

12. Change the new chart to a Pie.

Power View Report

13. Drag Owner to Color

Power View Report

14. Start a third chart using a Scatter chart type.Power View Report

15. We will use this configuration of fields:

Power View Report

16. Drag the size to be the full width.

Power View Report

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.

Power View Report

18. Click on any piece of the Owner pie and all three component will reflect our selection:

Power View Report

19. Save as a report in CRM for others to use!

Power View Report

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:

Happy CRM’ing and Reporting!

Avatar for JoeCRM

JoeCRM

Joe CRM is a CRM superhero who runs on pure Microsoft Dynamics CRM adrenaline. As the face of PowerObjects, Joe CRM’s mission is to reveal innovative ways to use Dynamics CRM and bring the application to more businesses and organizations around the world.