Excel Home

数据集函数Part1 概述(二)

译者:apolloh  来源:http://blogs.msdn.com/excel

What are the new CUBE functions?


We have implemented seven new CUBE functions that can be used in Excel formulas just like any other function in Excel. These functions permit Excel to fetch data from SQL Server Analysis Services (2000 & 2005), including any member, set, aggregated value, property, or KPI (Key Performance Indicator) from the OLAP cube. This data can then be placed anywhere in the spreadsheet, intermingled with other local calculations and/or within other formulas. Here are the seven new CUBE functions:
我们补充了7个新数据集函数,同其它工作表函数一样,可以在Excel公式中使用。这些函数允许Excel从 SQL Server Analysis Services (2000 & 2005) 提取数据,包括任意成员、子集、汇总值、属性或KPI(关键业绩指标)。这些数据可以放在电子表格的任何地方参与本地计算,或者作为其他公式的一部份参与计算。

CUBEMEMBER (connection, member_expression,)
This function will fetch the member or tuple defined by the member_expression. For example, (from the illustration above,) the formula: =CUBEMEMBER (“Adventure Works”, “[Sales Reason].[On Promotion]”) returns the member named “On Promotion” from the “Sales Reason” dimension of the Adventure Works cube.

该函数通过定义member_expression(成员表达式)提取成员或成员组。例如,上个实例的公式=CUBEMEMBER (“Adventure Works”, “[Sales Reason].[On Promotion]”), 从Adventure Works 数据集的“销售成因”维度返回了一个“促销”成员。

CUBEVALUE (connection, [member_expression_1], [member_expression_2], …)
This function will fetch the aggregated value from the cube filtered by the various member_expression arguments. For example, the formula: =CUBEVALUE (“Adventure Works”,”[Measures].[Gross Profit]”,”[Product].[Category].[Bikes]”,”[Date].[Fiscal Year].[FY 2004]”) returns the value $5,035,271.22 which is the aggregated amount in the Adventure Works cube for Gross Profit for Bikes in Fiscal 2004.

该函数对数据集按member_expression(成员表达式)参数变量汇总,返回汇总数。例如,公式=CUBEVALUE (“Adventure Works”,”[Measures].[Gross Profit]”,”[Product].[Category].[Bikes]”,”[Date].[Fiscal Year].[FY 2004]”) ,返回Adventure Works数据集中2004财政年度自行车的销售毛利的汇总金额$5,035,271.22

CUBESET (connection, set_expression, , [sort_order], [sort_by])
This function will fetch the set that is defined by the set_expression parameter. Optional parameters allow you to specify the ordering of the set as well as the caption to be displayed in the Excel cell that contains this formula. (Note that the set itself won’t have a display value. For example, the formula: =CUBESET (“Adventure Works”,”[Customer].[Customer Geography].[All Customers].children”,”Countries”) returns the set of countries in the Customer Geography hierarchy and shows “Countries” as the cell’s display value.

该函数通过set_expression(子集表达式)参数的定义提取子集。选项参数Caption可用来自定义一个子集的标题,显示在包含该公式的单元格中。(注意子集本身并不显示值) 例如,公式=CUBESET (“Adventure Works”,”[Customer].[Customer Geography].[All Customers].children”,”Countries”)分级返回Customer Geography的国家名子集,并显示“国家”作为单元格的值。

This function returns the number of items in a set. Typically the argument to this
function will be a CUBESET function or a reference to a CUBESET function.


CUBERANKEDMEMBER (connection, set_expression, rank, )
This function returns the Nth item from a set. This can be very useful when building a Top N (or Bottom N) report in Excel.


CUBEMEMBERPROPERTY (connection, member_expression, property)
This function returns a property of a member in the OLAP cube.


CUBEKPIMEMBER (connection, kpi_name, kpi_property, )
This function returns a KPI (Key Performance Indicator) from the OLAP cube.


CUBE functions provide MDX to other CUBE functions
There is one way in which the CUBE functions are significantly different from any other Excel functions. This is in the way that they behave when they are passed in as arguments to other CUBE functions.


Functions that are not CUBE functions return a value which is displayed in the Excel spreadsheet cell. For example, the result of a SUM function will be a number that is displayed in the cell.

CUBE functions (except for CUBESETCOUNT) return a result which is more complex than this. CUBE functions return two distinct values. One is the value that is displayed in the cell. But there is also a second hidden value which can be thought of as an MDX expression (MDX is the Multi-Dimensional eXpression language used by SQL Server Analysis Services) defining the result. When one CUBE function uses another CUBE function as its argument, the argument takes on the MDX value rather than the display value.
数据集函数(除了CUBESETCOUNT 外)的返回值要复杂许多。除了一个值显示在单元格中,还有第二个隐藏值,可以看作是MDX表达式(SQL Server Analysis Services使用的多维表达式语言)的结果。当一个数据聚集函数使用另一个数据集函数作为参数时,该参数取MDX值,而不是显示值。

Here’s an illustration that I hope will help to make this clearer. Here is the asymmetric report that we looked at earlier.


Cell C2 actually contains two separate values. The first is the display value that you see in the cell, namely “On Promotion”. But when the formula in cell C3 uses cell C2 as an argument, the value that it obtains from cell C2 is actually “[Sales Reason].[Sales Reasons].[Sales Reason].&[2]” which is the MDX unique name for the “On Promotion” member. You can find this hidden value behind the CUBE function using Excel’s object model by selecting cell C2 and then typing “?Activecell.MDX” into the immediate window in the Visual Basic Editor.
单元格C2实际包含两个不同的值。第一个显示在单元格中——“促销”。单元格C3使用了C2为参数,而其所使用的值实际上是“[Sales Reason].[Sales Reasons].[Sales Reason].&[2]” ,是MDX返回的该“促销”成员的唯一值。您可以使用Excel对象模式选择C2单元格,然后在VBE立即窗口输入“?Activecell.MDX”,找到藏在数据集函数中的隐藏值。

And that’s the overview. In my next post, I will provide some examples that illustrate how to use these functions in your reports.

Published Thursday, February 02, 2006 8:59 PM by David Gainer
Filed Under: Formulas and functions, Analysis Services

注:本文翻译自http://blogs.msdn.com/excel ,原文作者为David Gainer(a Microsoft employee),Excel Home 授权转载。严禁任何人以任何形式转载,违者必究。

未经允许不得转载:ExcelHome » 数据集函数Part1 概述(二)

本站特聘法律顾问:李志群律师   沪ICP备11019229号-2

沪公网安备 31011702000001号