DotNetCurry Logo

SharePoint Server 2013 BI - Interactive Reports using Power View in Excel 2013

Posted by: Pravinkumar Dabade , on 1/11/2013, in Category SharePoint
Views: 56540
Abstract: In this article, we will see how easy it is to design interactive and beautiful reports using Excel 2013 Power View with Filters. We will also uploaded the report into our SharePoint 2013 site and display it in our browsers.

In this article, we will see how to build beautiful and interactive reports using “Power View” in Microsoft Excel 2013. Later we will use these reports in SharePoint 2013. For this demonstration, I am going to use the “AdventureWorks” SQL Server Database which can be downloaded for free over here.

Open Excel 2013 and create a new “Blank workbook”. Once the workbook is ready, we will use a power pivot data source for designing Power View reports. Although Power Pivot by default is available in Excel 2013, however it is not enabled. To enable the Power Pivot Add-In, follow these steps.

 

In Excel, go to File > Options. This opens “Excel Options”. Click on Add-Ins > Under “Manage” dropdown box, choose “COM Add-Ins” > click on the “Go” button as shown below

enableaddin

Excel will display the “COM Add-Ins” window. Choose “Microsoft Office PowerPivot for Excel 2013” and click “OK” as shown below –

powerpivot

Once you enable the Power Pivot Add-In, you will see a tab in Excel 2013. If you click this “POWERPIVOT” tab, you will see the following options –

powerpivottoolbar

Let’s get the data from Microsoft SQL Server Database. To get the data from SQL Server, click on the “Manage” button which is shown in the above diagram. This brings up the “PowerPivot for Excel” window. Click on “Get External Data” button. Then choose “From Database” and then “From SQL Server” as shown below –

sqlserverconn

Now provide your SQL Server instance name, use windows authentication and select database “AdventureWorks” as shown below –

sqlconnstr

Click on the “Next” button. In the next window of the wizard, you will be asked whether you want to select the tables and views or you want to write a query. We will make a choice of selecting the tables and views for importing the data. Click on the “Next” button and to view all the tables and views in the next step of our wizard. Now choose “SalesOrderHeader” and click on “Select Related Tables” button. This will select 10 tables.

Now click on “Finish” button to import the selected tables and then close the wizard. This step will create a separate sheet for each table, along with the data.

You can perform different operations here. For example you can add your own columns. In the tool bar of the “Home” tab, click on “Diagram View” as shown below –

digview

This shows you a database diagram view for the selected tables with the relationships. If you want, you can add your own relationship as shown below –

dbdiagram

Actually we don’t require all the above tables. So you can delete all the other tables except “Sales Territory”. Now let’s go back to “Data View”. Close the “PowerPivot for Excel window”. You are now on our main Excel sheet where we will be designing the report.

Designing a Report using Excel 2013 Power View

To design the report, go to the “Insert” tab and click on “Power View”. If you are using “Power View” for the first time, it will ask you to enable the Power view add-in as shown below –

enablepowerviewadin

Click on “Enable” button to bring up the “Power View Report Designer”. On the left hand section, you will see the report designer and a filter section. On the right hand section, you can see all the tables with the fields. In our case, we have only one table that is “Sales Territory”.

Now let’s first name our report as “Adventure Works Sales Report” and apply the “Theme” by choosing the “Themes” dropdown list as shown below –

reporttitle

From the “Power View Fields” section, choose the “Sales Territory” table and drag and drop “SalesYTD” field on our report view in the left hand side section as shown below –

dragndropsalesytd

The default view is set to “Table” for the Power View report. Click on “Bar Chart” and choose “Stacked Bar” as shown below –

setstackbar

Now drag and drop “SalesLastYear” from “Sales Territory” table and drop it on our stacked chart. After this step, drag and drop “Name” field from “Sales Territory” table in the “AXIS” section of our “Power View Fields” as shown below –

setaxis

Now adjust the height and width of your chart and your chart will look similar to the following –

stackedchartready

Now let’s add a filter. To add a filter, drag and drop a “Name” field from the “Sales Territory” table into “Filters” section. If you choose specific sales territory, your chart will get reflected as per your territory choice. We have now finished designing  the “Stacked Bar” chart report.

Let’s beautify this report.

For this, we will drag and drop a “Name” field from “Sales Territory” table below our stacked bar chart. Then click on “Map” from the toolbar section as shown below –

map

Now drag and drop “SalesYTD” from “Sales Territory” into “Size” section which you can find under “Power View Fields”. Also drag and drop the “Name” from “Sales Territory” into “Locations” section of “Power View Fields”.

Now your report should look similar to the following –

finalreport

Pretty Cool! Your report is ready with a geographical map and stacked chart bar. You can also apply the filters from the “Filters” section. Now save the document with the name “AdventureWorksSalesReport”.

Deploying the Report to SharePoint Server

Let’s deploy this report to our SharePoint Server. I am using Office 365 SharePoint Developer site for uploading the Excel 2013 workbook which we just designed sometime back.  Before uploading the documents to your SharePoint site, you must follow some rules –

  1. Your excel workbook size must be less than 10 MB.
  2. To display reports in SharePoint site, you must have the Silverlight plug-in installed.

I have uploaded the “AdventureWorksSalesReport.xlsx” into my “Documents” library as shown below –

documentlib

Now click on the excel workbook. The workbook will get displayed in browser as shown below –

webfinalreport

You can now try filtering the data. Also try clicking on the Bar chart or click on the Bubble of any region, and the filter will automatically get applied on both “Stacked Chart” as well as the Map report.

Conclusion

In this article, we have seen how easy it is to design interactive and beautiful reports using Excel 2013 Power View with Filters. We also uploaded the report into our SharePoint site and displayed it in our browsers.

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Pravinkumar, works as a freelance trainer and consultant on Microsoft Technologies. He is having over 10 years of experience in IT and is also a Microsoft Certified Trainer(MCT). He has conducted various corporate trainings on all versions of .NET Technologies including .NET, SharePoint Server, Microsoft SQL Server, Silverlight, ASP.NET, Microsoft PerformancePoint Server 2007 (Monitoring). He is passionate about learning new technologies from Microsoft. You can contact Pravinkumar at dabade[dot]pravinkumar [attherate] gmail[dot]com


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Oliver Norris on Sunday, January 13, 2013 10:56 PM
Good one mate! Any idea if this can be done with Google Maps too?
Comment posted by Pravinkumar Dabade on Tuesday, January 15, 2013 10:04 AM
Thanks for your comments. You can try this one -

http://office.microsoft.com/en-us/store/google-map-locator-WA103989793.aspx?queryid=99c773f1-56b1-4f4a-8a1f-2a63d1aa1ae6&css=google&CTT=1

Pravinkumar
Comment posted by Savita on Tuesday, March 26, 2013 7:17 AM
please can you share some powerpivot article as well.
Comment posted by kirti on Thursday, April 18, 2013 6:34 AM
Hi pravin,

What do i do when i want to use SQL powerview report to display charts into a sharepoint 2013 provider hosted app. My SQL DB is hosted on windows azure. I do not want to use excel powerview or Sharepoint BI. Thanks
Comment posted by Randy on Friday, June 14, 2013 12:26 PM
Thanks for the article. Is there any way to share an interactive powerview report (not just an image format) with people running a previous version of excel?
Comment posted by Savita on Wednesday, January 8, 2014 5:39 AM
HI Pravin,
I have created this same report but when I upload it on sharepoint 2013 I am not able to see that report only error sign. I tried uploading it on gallery as well.

Thanks
Savita
Comment posted by raj on Wednesday, February 19, 2014 6:57 AM
hi. it was a nice article. is there any way to achieve this interactive reports using SQL tables directly? I mean, here we are importing the sql data to excel. once done, any updated records in sql will not reflect in the excel. So is there any way i can make the excel always sync with the sql data. or can we use above concept with any external list? so that i can make my external list read data from sql tables.

thanks
raj
Comment posted by prashanth on Friday, March 14, 2014 6:18 AM
don't no
Comment posted by fred veliyev on Wednesday, March 26, 2014 11:28 AM
can sharepoint power pivot be share externally.
Comment posted by ofer Gal on Monday, September 8, 2014 7:33 PM
How did you manage to have a legend next to the map? Whatever I do, I get just a cut off map.

Also, is there a way to have powerview on Excel 2010 also?
Comment posted by Ashish on Tuesday, January 6, 2015 5:29 AM
a+1
Thanks Pravin
Comment posted by Jason on Wednesday, February 11, 2015 9:17 AM
Is anyone else experiencing the issue where the dots on the map DO NOT line up with accurate locations?  Germany for me is around florida, Northwest on the map is in South Africa.. its as if the SQL sample guys did not pass Geography in high school.