Content in this post is derived from Implementing and Administering Microsoft Project Server 2010
As many of you already know, Project Server 2010 is finally here, and it brings many powerful new features in tow, including the flexible and approachable Microsoft Excel based reporting infrastructure available in the new Business Intelligence Center. Project Server 2010, along with the Excel Services feature provided by SharePoint Server 2010, now offer the ability to publish Excel-based reports to the web for business users to consume without the need to download and launch them in Excel. These reports draw data from the Project Server Reporting Database or one of possibly several OLAP databases via a built-in collection of Office Data Connection (ODC) files.
Business users can view these web-enabled Excel workbooks in whole, or you can control what they can view by only exposing the data that you choose.
Excel Services allows business users to view an Excel workbook as a web page...
...and it also allows you to very easily embed workbook components, such as tables and charts, into a dashboard (a.k.a. SharePoint web part page).
Whether you are planning to implement Project Server 2010 now or a year from now, I recommend that you take the opportunity to brush up on your Excel skills in preparation for Project Server 2010 reporting. Even if you are currently using Project Server 2007 and do not plan to upgrade for a while, you can still use these concepts in your current system (especially if your Project Server is deployed with MOSS 2007)!
Spend some time learning about the following Excel features and tools, and you will be several steps closer to developing powerful Project Server reports... therefore providing more decision power to the users of your system.
PivotTables and PivotCharts
Although many of us have heard of them, an area in Excel that many people do not fully understand is PivotTables and PivotCharts. A PivotTable is a table that displays a breakdown of data in various ways, such as work hours broken down by year, by region, and by amortization type. This allows the business user to perform a detailed analysis and identify patterns in the data.
To learn more about working with Excel PivotTables and PivotCharts, visit the Office Online web site:
http://office.microsoft.com/en-us/excel/HP101773841033.aspx
Conditional Formatting
Similar to graphical indicators in Project Pro and PWA, Excel Conditional formatting allows you to set up flexible rules to visually format ranges of data, providing business users with quick visual cues to identify the data of interest. Cells of data in a workbook can appear with different background colors, color fill gradients, and icons.
To learn more about working with Excel conditional formatting, visit the Office Online web site:
http://office.microsoft.com/en-us/excel/HP100739391033.aspx
Connecting to External Data
Microsoft Excel can display static data that you manually enter into a workbook, and it can connect to an external data source such as the Project Server Reporting Database or an OLAP database and extract live data for reporting. To extract data from a live data source, Excel uses an Office Data Connection (ODC), which contains information describing the data source, how to connect to the data, and which data to retrieve.
The data connection information can reside directly in the Excel workbook file, or it can reside in a shared ODC file. Project Server 2010 provides several ODC files, making it easy for you to connect to the desired data source, extract the data, and display it in Excel for reporting purposes. These ODC files reside in the Business Intelligence Center.
To learn more about working with Office Data Connections, visit the Office Online web site:
http://office.microsoft.com/en-us/excel/HA101672271033.aspx
Creating Calculated Fields
When building an Excel report, you may need to display a field of data that is not easily available in the data source; in this instance, Excel allows you to build a mathematical expression, similar to a formula in Project Pro or PWA, to calculate the required data based on one or more fields of existing data. This can be especially useful when fetching data from an OLAP cube that has a limited set of available data fields.
To learn more about working with Excel calculated fields, visit the Office Online web site:
http://office.microsoft.com/en-us/excel/HA101672271033.aspx
As I stated previously, these are valuable Excel skills to master, regardless of your Project Server version… so set some time aside now and go back to Excel school.
Good luck!