SharePoint 2013 - Excel Services (BI Reporting)

Background:
Business intelligence (BI) is a set of theories, methodologies, architectures, and technologies that transform raw data into meaningful and useful information for business purposes. SharePoint 2013's Excel Services have made BI reports publishing a lot easier.

In this post, I am going to create a dynamic report using Power View in Excel and then will publish it in SharePoint 2013/Online. I will use a sample MS Access database (RptSampl.mdb) provided by Microsoft to build a new report. Same concepts covers the reports development for MS SQL Server. This post has following three sections:

1. Importing the Data 
2. Designing a Report 
3. Publishing a Report to SharePoint.

Lets get started.

Importing the Data into a Excel Report

---

1. Download sample database from Microsoft's site.
2. Launch MS Excel 2013 and go to POWERPIVOT tab.


1. If you don't see this menu then enable it by going to File -> Options.
2. Under Add-Ins select Manage: COM Add-ins and then hit Go.
   
3. Check Microsoft Office PowerPivot for Excel 2013 and then hit OK.
   
3. POWERPIVOT tool bar will look like this
   
4. Select Manage and the following window will appear where we will connect to the database, import the data and will arrange the relationship between tables.
   
5. Select Get External Data -> From Database -> From Access
   
6. Select RptSampl.mdb from your downloads  folder and it doesn't not need any login and password then hit Next.
   
7. Choose Select from list of tables and views to choose the data to import in the next screen and hit Next again
8. Select the tables you want to to add in your report. I will select Products and Suppliers then hit Finish.
   
   You will see data from two tables will get imported into Excel. It will also increase the size of the Excel file. Select Close after data import.
9. You can select Diagram View from the tool bar to see the relation between tables.
   
10. Hit Save and it will save the data and close the Dialog.

Designing a Excel Report

---

1.  Go to INSERT tab and select Power View in Reports section.
   
2. This will open a new work sheet, select Power View tab, show you the imported tables so we can design our report here.
   
3. Expand tables and select fields that needs to be displayed in the report. For this report I will select ProductName and UnitPrice columns from Products table.
   
4. This is your basic report. You can apply filter by clicking the filter button and can perform sorting by clicking the title of the column inside the report.
   
5. You can present the information in a dynamic Chart too by selecting Bar Chart from DESIGN tab
   
   And your report should look like this
   
6. You can add other columns like UnitsInStock, UnitsOnOrder to see how they will look in the report's chart.
   
7. Save the Excel work book in local folder with the name Report.xlsx.

Publishing a Excel Report to SharePoint Online/2013

---

1. Upload the Report.xlsx to a document library in SharePoint Online.
2. Click the Report.xlsx inside the document library and SharePoint will open the report inside the browser with Excel Online which is a part of Office Web App (OWA).
   

And thats how you develop and publish a BI report using Excel Services.