Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications

|

How to Create a Power View Report in Excel with CRM Data

Post Author: Joe D365 | May 30th, 2013

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

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!

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

6 comments on “How to Create a Power View Report in Excel with CRM Data”

  1. If taking a Power View Report created in Excel and importing to CRM, will it require that the reporting server and database be running on SQL 2012, and have Power View add-on installed?
    What about promoting to CRM as report, AND rendered through a Dashboard (web resource)? Is that possible?
    Todd

    1. I was wondering the same thing. I don't see a response from PowerObjects on this question though. I would like to render in a Dashboard.

      1. Hi Zara/Todd - sorry for the delay in replying. So we have a few options.
        One option is to upload the .xlsx file to crm to the reports area. THis then requires the end-users to have excel, and either be crm on premises or if crmonline/partner hosted have the outlook crm client installed.
        The second option is what Todd mentioned -- to have sql 2012 and the powerView addon.
        The 3rd option is coming shorty, but it is to use a new production from Microsoft called PowerBI. This is currently in beta. Heres a blog about this:

        http://blogs.msdn.com/b/powerbi/archive/2013/12/19/dynamics-crm-online-in-power-query.aspx

  2. Hi, thanks for your post, very helpfull !
    Just a question, how did you save your report into CRM?
    Thanks for your reply,
    Emilie

  4. Hi, I´ve got to the point 6 just fine. After that I get a problem. My Existing connections button is greyed out. Any idea why is that and how should I continue?

PowerObjects Recommends