Excel 2007 – CubeValue Reporting and Excel Services

Within PerformancePoint, and for cubes in general, Excel is usually the reporting tool of choice, or the tool being replaced by Monitoring scorecards.  The pivot table in Excel can only get you so far in terms of set-based reporting.  Sometimes free-form reports are required.

These functions provide this ability in Excel.  

This page presents how to work with Microsoft Excel 2007 features and its redesigned interface. You may also visit our Excel Question page.

Cube functions

CUBEKPIMEMBER function

CUBEMEMBER function

CUBEMEMBERPROPERTY function

CUBERANKEDMEMBER function

CUBESET function

CUBESETCOUNT function

CUBEVALUE function

Plan Excel reports against OLAP cubes

Updated: 2009-02-26

In this article:

• About building Excel reports against OLAP cubes

• OLAP functions

• OLAP formulas

http://technet.microsoft.com/en-us/library/cc263194(office.12).aspx

Once the reports are built, they need to be consumed.  Other than email distribution or publishing to a document library, Excel Services would be the preferred display method for companies using Sharepoint 2007 Portal Server Enterprise. (MOSS)

There are some whitepapers for Configuring Excel Services to support this functionality.

http://technet.microsoft.com/en-us/library/cc263430(office.12).aspx

White paper: Excel Services step-by-step guides

Updated: 2007-03-08

These guides help you learn about Excel Services in Microsoft Office SharePoint Server 2007 functionality and understand the relationships among various components of Microsoft Office SharePoint Server 2007. The procedures in these guides take you step by step through the process of configuring and using Excel Services to build fully functional PivotTable reports, charts, and a variety of other reports. You can also learn how to restrict access to spreadsheet data and connect to an external data source.

Download the following guides as Microsoft Word documents (.doc).

• Setting up the Server - Step 1: Creating a Trusted Location with Excel Services (http://go.microsoft.com/fwlink/?LinkId=84833&clcid=0x409)

• Setting up the Server - Step 2: Configuring Single Sign-On for Excel Services (http://go.microsoft.com/fwlink/?LinkId=84834&clcid=0x409)

• Setting up the Server - Step 3: Creating a Trusted Data Connection Library with Excel Services (http://go.microsoft.com/fwlink/?LinkId=84836&clcid=0x409)

• Building a Report Based on Cube Functions for Excel Web Access (http://go.microsoft.com/fwlink/?LinkId=84828&clcid=0x409)

• Protecting Workbook Intellectual Property (IP) with View Item Rights (http://go.microsoft.com/fwlink/?LinkId=84832&clcid=0x409)

• Using OLAP Formulas, Conditional Formatting, and Charts (http://go.microsoft.com/fwlink/?LinkId=84838&clcid=0x409)

• Building an OLAP PivotTable Report with Excel Services (http://go.microsoft.com/fwlink/?LinkId=84830&clcid=0x409)

• Building a Relational PivotTable Report with Excel Services (http://go.microsoft.com/fwlink/?LinkId=84827&clcid=0x409)

• Building a Report with a Chart for Excel Web Access Based on Cube Functions (http://go.microsoft.com/fwlink/?LinkId=84829&clcid=0x409)

• Creating a Multi-Report Summary Page of Reseller Sales Data (http://go.microsoft.com/fwlink/?LinkId=84831&clcid=0x409)

Excel 2007 - Cube

These Excel Services reports can be consumed as PerformancePoint Monitoring data sources.

http://blogs.msdn.com/b/performancepoint/archive/2007/07/12/how-to-use-excel-services-as-a-data-source.aspx

They can be referenced directly within PerformancePoint Dashboard Designer.

http://office.microsoft.com/en-gb/performancepoint/HA102524991033.aspx