Plan Excel reports against OLAP cubes

  • Article

Applies To: Office SharePoint Server 2007

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2016-11-14

In this article:

  • About building Excel reports against OLAP cubes

  • OLAP functions

  • OLAP formulas

About building Excel reports against OLAP cubes

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.

OLAP functions

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.

OLAP formulas

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.

Download this book

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.