Microsoft Excel : Using PivotTables to Analyze Data From a SharePoint List

Using PivotTables to Analyze Data From a SharePoint List: Today's author, Diego Oppenheimer, a Program Manager on the Excel team, talks about connecting PivotTables to data stored in SharePoint Lists.

Many of us on the Excel team have been approached by customers asking us how to create a connection to a SharePoint list. SharePoint lists can be exported easily by creating a Microsoft Office Excel Web Query. The connection can then be tied directly to lists or PivotTables in the workbook like any other Data Connection.

I have a SharePoint List that we use to upload and keep track of all project management reports. The list contains information like the project manager that uploaded it, the date, and the name of the reports as well as other automated fields created by SharePoint to make my life easier. Using the filters provided by SharePoint gives me a great way navigate my list quickly and easily but I am more interested in creating a macro view of the team reports so an easy solution would be to connect my SharePoint List to a PivotTable. The fact that my SharePoint List has more than 250 rows makes the use of a PivotTable even more appealing.

Creating the Connection and the PivotTable:

Once we have located the SharePoint list we want to export we go to "Export to Spreadsheet" in the "Actions" menu button.

Microsoft Excel : Using PivotTables to Analyze Data From a SharePoint List

---
Pierre Erol GIRAUDY
Club MUG et MOSS FRANCE
http://clubmoss2007.org

MOSS 2007 and Business Intelligence

Microsoft Office SharePoint Server 2007 and Business Intelligence With Microsoft Office SharePoint Server 2007, your organization has a fully integrated enterprise portal that can help you improve employee interaction.
Powerful new features can help you integrate structured and unstructured data, see different levels of detail, and interact with spreadsheets, key performance indicators (KPIs), and dashboards.
Because the content is in SharePoint Server, you have access to built-in native capabilities such as enterprise search, content management, communication, and collaboration.
The release of Office SharePoint Server 2007 introduces Excel Services, which provides access to real-time, interactive Office Excel 2007 spreadsheets in a Web browser, enabling you to easily protect, share, and reuse spreadsheets. See the related ressources :
Office SharePoint Server 2007 Product Page
Office SharePoint Server 2007 BI Page
Office Excel Services Business Value
Demo: Enable Excel Services and Data Connections for a SharePoint Team Site
---
P. Erol GIRAUDY
Président du Club MOSS 2007 et MUG (MVP MOSS).
http://clubmoss2007.org/
http://www.microsoft.com/france/mstechdays/

Excel 2007 can now do two-way sync to a SharePoint List

In Excel 2003 you were able to sync amendments both from a Excel 2003 spreadsheet to a SharePoint List and from a SharePoint List to an Excel 2003 spreadsheet. But in Excel 2007 this is no longer the case...

Well it was until Microsoft released an Excel 2007 Add-in.

This is a must install if you are upgrading to Office 2007 from Office 2003, otherwise your users will cry.
The Excel 2007 Add-in, can be downloaded from: http://www.microsoft.com/downloads/details.aspx?FamilyID=25836e52-1892-4e17-ac08-5df13cfc5295&DisplayLang=en

There is also an accompaning MSDN article "Publishing and Synchronizing Excel 2007 Tables to SharePoint Lists": http://msdn2.microsoft.com/en-us/library/bb462636(office.11).aspx

...

P. EROL GIRAUDY

Writing a Filter Provider Web Part for Windows SharePoint Services 3.0

Writing a Filter Provider Web Part for Windows SharePoint Services 3.0: "Writing a Filter Provider Web Part for Windows SharePoint Services 3.0"

Read It :

Introduced in Windows SharePoint Services 3.0, filter Web Parts use the Web Part connection support to provide filter criteria from one Web Part (the "provider" Web Part) to another Web Part (the "consumer" Web Part). This makes it possible for developers to build more sophisticated applications incorporating the 2007 Microsoft Office system by enabling users to provide search and filter criteria.

To learn how to package and deploy the Geographic Region filter provider Web Part created in this Visual How To, and how to improve the appearance and configurability of a Web Part, see Deploying and Optimizing a SharePoint Web Part That Calls Excel Web Services.

Explore It :

Filter Web Parts Overview

Windows SharePoint Services Web Part Infrastructure

Walkthrough: Writing a Simple Filter Consumer Web Part Sample

ITransformableFilterValues Interface (Microsoft.SharePoint.WebPartPages)

EROL