Plan Excel reports against OLAP cubes
Updated: February 26, 2009
Applies To: Office SharePoint Server 2007
Topic Last Modified: 2009-02-20
In this article:
You can use the new Microsoft Office Excel 2007 online analytical processing (OLAP) functions and formulas to create highly customizable reports and analytic models that can be automatically updated to include the most current business data from external data sources.
By using the new Office Excel 2007 OLAP functions, you can use an Office Excel 2007 spreadsheet as a reporting surface for data from OLAP cubes. You can use the following OLAP functions to import Microsoft SQL Server 2005 Analysis Services data directly into Office Excel 2007 spreadsheet cells for unstructured data analysis:
CUBEMEMBER Sends a Multidimensional Expression (MDX) to an OLAP cube and fetches a member or tuple.
CUBEVALUE Sends one or more MDX to an OLAP cube and fetches an aggregated value.
CUBESET Sends an MDX to an OLAP cube that defines a set in the cube and places the set in a spreadsheet cell.
CUBESETCOUNT Returns the number of elements in a set.
CUBERANKEDMEMBER Fetches the Nth item in a set.
CUBEMEMBERPROPERTY Returns a member property value from an OLAP cube.
CUBEKPIMEMBER Returns a key performance indicator (KPI) goal, value, status, or trend from an OLAP cube.
Excel OLAP formulas provide an additional way to display SQL Server 2005 Analysis Services data in Office Excel 2007 spreadsheets. By using Excel OLAP formulas, you can build reports with greater control of data placement than is possible by using PivotTable reports. In addition, you can use Excel OLAP formulas to write custom MDXs into a report.
This topic is included in the following downloadable book for easier reading and printing:
See the full list of available books at Downloadable content for Office SharePoint Server 2007.