Export (0) Print
Expand All

Plan Excel reports against OLAP cubes

Office 2007

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft