Saturday, January 12, 2013

How to create a Pivot Table in Sharepoint?


Pivot Tables are one of the most powerful features that Microsoft has ever developed for MS Office. Initially it only formed part of Excel, but due to it's popularity it was eventually converted to a "Service" that could be used in Word , Access and many other applications. With the advent of "Excel Services" in MOSS 2007 Enterprise edition, we can now use pivot tables in Sharepoint, running server side.
Before we can view a pivot table embedded as a web part in a Sharepoint site, we first have to create one in MS Excel 2007 and then save the spreadsheet into a document library. The initial steps would therefore be as follows:
1.    Create a site in Sharepoint
2.    Create a Document Library to store the spreadsheet
3.    Create a "New" spreadsheet from the document library

To simulate a proper use of a "Pivot Table" in an enterprise environment I am going to use a back end database as the source of the spreadsheet. Once the spreadsheet has opened in MS Excel, navigate to the data tab on the ribbon and select "From Other Sources".

There is a whole range of data source options to choose from and in a perfect world everyone would be running some form of BI cube on "Analysis Services", but unfortunately that is not the case. I will therefore be using a more realistic example like a SQL Server database. Once you selected the option, a wizard will start the will guide you through the rest of the process.

Step1: Specify the SQL Server name that you will be connecting too and the Log-on credentials.

Step 2: Select the database of your choice from the drop down list and then select the table or view that will be the source.

Step 3: Note that the connection string will be stored as a ODC file and will be automatically uploaded to Sharepoint when we publish. What we need to do now is look at how the users will be authenticated. Select the button at the bottom of the screen called "Authentication Settings".

Step 4: Once the Pivot Table is published to Excel Service in Sharepoint the authentication will be done server side. The default option is NTLM, but the SSO (Single Sign-on Service) can also be used if it has been configured. "None" can only be used if the data is already in the spreadsheet itself. Select "OK" to close this dialogue box and then select "Finish".

Step 5: The final part of the wizard is used to determine how and where you would like to display the data retrieved from the database. In this case we want a "Pivot Chart and Report" and the location can be any cell in the current sheet. Click "OK"
Using the "Pivot Table" wizard in Excel, build a Pivot table to display some data. I am assuming that you know how to do this, otherwise why are you reading this blog. Once you are done, it should look something like this:

Now that the Pivot Table is complete, we need to publish it to Excel Services running on Sharepoint.

To do this , select the "Pearl" in to the top left hand corner of Excel, navigate down the list to an option called "Publish" and then select "Excel Services". This will open the "Save As" dialogue box with some additional options.

The spreadsheet will automatically attempt to save back to the document library from where it was initiated. Give to spreadsheet a name, but don't click save yet, there are some additional options that need to be configured. Select the "Excel Services Options" button.

In the drop down list on the Show tab, select the option called "Items in the Workbook". Then select the checkboxes next to the "Chart" and "Pivot Table" objects. We only want to publish these objects to Excel Services, not the entire workbook or sheets. Click "OK and then "Save". Close Excel and navigate to the site in Sharepoint.
Select "Edit Page" from the "Site Actions" menu and then select "Add Web Part" in a Zone of your choice. This action will then launch the "Web Part Gallery" selection screen.

The web part that we are interested in is called "Excel Web Access". Select the web part from the list by selecting the checkbox and then click "Add". This will then snap the web part into the zone. Once the web part appears, you will notice that it has a hyperlink called "Click here to open tool pane". By selecting this option a tool pane will open on the right hand some with a whole set of configuration options.

One of the options is called "Workbook" with a browse button located next to it. Browse to the spreadsheet that we created earlier and saved to a document library. Once this is done, select "OK" and exit edit mode on the page.

The pivot table should then appear displaying the "Chart" by default. You can toggle between the chart and pivot table by selecting them from the "View" menu on the toolbar. All the filtering capabilities are also available as if you were working in Excel.
The data from the database can also be refreshed by selecting "Update" from the menu.
This is one of the core components used to build Digital Dashboards and it is a vast improvement on Office 2003 OWA (Office Web Components) that use to download as Active X Controls.

1 comment: