Use the SharePoint 2010 Excel Service to Create a BI Tool

Excel Services SharePoint 2010

Excel service is an important component of SharePoint Server 2010. You can easily show the excel file in a web page, even though the audiences has no excel. You can also insert the excel items (such as: Pivot Table, Pivot Chart) to a report article to make the data or chart up to date.

Pivot Table is a user created summary table for the original spreadsheet. You create the table by defining which fields to view and how the information should be displayed. Based on your field selections, Excel organizes the data so you see a different view of your data. Pivot table is a main BI tool in Microsoft office system. I could be used to display the data from SQL server, Analysis services, and SharePoint and so on. In additional, you can also create a pivot chart for a pivot table. The chart content will follow the content of the table. This makes a big data visual and easy understanding. There are a lot chart type (example: Pie, Column, line, Bar, Area) to display data, which used to display the different type of data.

Configure the excel services

Before display the pivot table in the SharePoint server, you need to configure the excel services at first. The excel service is a standard services application in the SharePoint 2010, you can find the “Excel Services Application” in the Central Administration -> Application Management -> Management Service Application

Then you can test if the excel service is OK. Set the “Opening Documents in the browser” for a document library and upload an excel file in this document library. Check if this excel could be opened properly.

Note: If you meet the error “The workbook cannot be opened”, you can send check this link

Create the pivot table and pivot chart

We have a SharePoint list which contains the order list for a retail shop. The boss of the retail shop need want to check the total revenue for each product in the 2011.

To get the data in a SharePoint list, the simply way is click the “Export to Excel” button in the list, and then you get a . iqy file.

Then open it with excel, you can get the list table in this excel by default. And then you can create a pivot table use this data and create a pivot chart use the pivot table.

Use the Excel Access Web Part to Display

Upload the excel file to the SharePoint library and then open it in browser, you could see the pivot table and pivot chart in the browser.

As the above picture, you can also filter or sort the value in the pivot table as it did in excel. (But the UI of the excel service is not user friendly)

Create a new page and add the excel access web part.

Open the tool panel and select the excel which you upload former and then enter the chart name in the item.

You can find the chart name in excel file.

After click OK, the pivot chart be display in the page. Using this method, you can add the result in any where you want.

Leave James Yu a message if you wish to know more, or email us at sales@BoostSolutions.com.

Check out new stuff at our website, BoostSolutions!