Why would you use a Power View report over using SQL Server Report Services (SSRS) or CRM’s Advanced Find? To create a good-looking, ad-hoc presentation! SSRS requires planning, thus is not an ad-hoc solution. However, using the data retrieved from an Advanced Find query, you can apply the visually appealing features of Excel’s Power View as an interactive report. Besides being a solution for ad-hoc report generation, the biggest advantage is that anybody can use Power View, provided the appropriate permissions, without the assistance of a developer.
For this walk-through, you will need to install:
- Excel 2013 Power View Component Object Model (COM) Add-in
- Dynamics CRM 2013 Outlook Client
In this example, you will create a report leveraging data from CRM in Excel 2013 Add-In Power View. Like Power Pivot, Power View is a COM Add-in for Excel 2013.
1. First export the data you want to use as a dynamic worksheet
When you export verify all the columns that you want to report on are included.
2. Note that your document will export as an XML file. Open Excel 2013 and find the exported XML file.
3. Click on “Enable Content” if a security warning appears.
4. If you don’t see the expected data, click on “Refresh from CRM”
5. Once the data is visible Save As .xlsx
6. Next open up Power View.
Note: Ensure the Power View COM Add-In and Outlook Client are installed. These items must be installed in order for you to connect and view the data.
You will not see the Power View option in the .xml format. You can find it in the Insert tab of the ribbon.
7. Create the report that you want, Save.
8. To make this report available in CRM:
- Open CRM and navigate to the Reports entity
- Click on “Add New”
- For the Report Type drop down option chose “Select Existing File” and navigate to the saved Power View xslx report.
- Enter a relevant name and categorization for this report.
- Click on “Save and Close”
- The report is now is available for others to see with updated data and when the report is opened, it will launch the saved Power View report in excel.
Note: You will need to enable the data to ensure it is updated.
I found that my data would refresh with current changes but my Power View graph wouldn’t. If you click “refresh all” on the data worksheet this should resolve the problem.
If your data is still not updating and you used a dynamic worksheet check this support article by Microsoft.
If you wish to share your report, you will need to enable it for the organization.
Here are some resources to help enhance your Power View Report:
- Intro to Power View
- Power View: Explore, visualize, and present your data
- Charts and Other Visualizations in Power View
Enjoy being able to provide your clients, bosses, team with up-to-date, “pretty” ad-hoc reports.