數(shù)據(jù)集函數(shù)Part1 概述（二）

數(shù)據(jù)集函數(shù)Part1 概述（二）

譯者：apolloh? 來(lái)源：http://blogs.msdn.com/excel 發(fā)表于：2006年7月7日

What are the new CUBE functions?

新增的數(shù)據(jù)集函數(shù)是什么？

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: 我們補(bǔ)充了7個(gè)新數(shù)據(jù)集函數(shù)，同其它工作表函數(shù)一樣，可以在Excel公式中使用。這些函數(shù)允許Excel從 SQL Server Analysis Services (2000 & 2005) 提取數(shù)據(jù)，包括任意成員、子集、匯總值、屬性或KPI（關(guān)鍵業(yè)績(jī)指標(biāo)）。這些數(shù)據(jù)可以放在電子表格的任何地方參與本地計(jì)算，或者作為其他公式的一部份參與計(jì)算。

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. 該函數(shù)通過(guò)定義member_expression（成員表達(dá)式）提取成員或成員組。例如，上個(gè)實(shí)例的公式=CUBEMEMBER (“Adventure Works”, “[Sales Reason].[On Promotion]”)， 從Adventure Works 數(shù)據(jù)集的“銷售成因”維度返回了一個(gè)“促銷”成員。

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 ,035,271.22 which is the aggregated amount in the Adventure Works cube for Gross Profit for Bikes in Fiscal 2004. 該函數(shù)對(duì)數(shù)據(jù)集按member_expression（成員表達(dá)式）參數(shù)變量匯總，返回匯總數(shù)。例如，公式=CUBEVALUE (“Adventure Works”,”[Measures].[Gross Profit]”,”[Product].[Category].[Bikes]”,”[Date].[Fiscal Year].[FY 2004]”) ，返回Adventure Works數(shù)據(jù)集中2004財(cái)政年度自行車的銷售毛利的匯總金額,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. 該函數(shù)通過(guò)set_expression（子集表達(dá)式）參數(shù)的定義提取子集。選項(xiàng)參數(shù)Caption可用來(lái)自定義一個(gè)子集的標(biāo)題，顯示在包含該公式的單元格中。（注意子集本身并不顯示值) 例如，公式=CUBESET (“Adventure Works”,”[Customer].[Customer Geography].[All Customers].children”,”Countries”)分級(jí)返回Customer Geography的國(guó)家名子集，并顯示“國(guó)家”作為單元格的值。

CUBESETCOUNT (set) 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. 該函數(shù)返回一個(gè)子集的記錄數(shù)，其參數(shù)是一個(gè)數(shù)據(jù)子集函數(shù)或子集函數(shù)所在單元格的引用。

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. 該函數(shù)返回子集的前N名的記錄。當(dāng)用Excel建立一個(gè)前N或后N名的報(bào)告時(shí)，它會(huì)很有用。

CUBEMEMBERPROPERTY (connection, member_expression, property) This function returns a property of a member in the OLAP cube. 該函數(shù)返回OLAP數(shù)據(jù)集中某個(gè)成員的屬性。

CUBEKPIMEMBER (connection, kpi_name, kpi_property, ) This function returns a KPI (Key Performance Indicator) from the OLAP cube. 該函數(shù)從OLAP數(shù)據(jù)集返回一個(gè)KPI（關(guān)鍵業(yè)績(jī)指標(biāo)）

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. 數(shù)據(jù)集函數(shù)與其他Excel函數(shù)相比，有個(gè)很大的不同點(diǎn)——當(dāng)它們作為其他數(shù)據(jù)集函數(shù)的參數(shù)時(shí)，有不同的行為表現(xiàn)。

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. 非數(shù)據(jù)集函數(shù)返回值直接顯示在Excel中。例如，SUM函數(shù)會(huì)顯示一個(gè)數(shù)值在單元格中。

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. 數(shù)據(jù)集函數(shù)（除了CUBESETCOUNT 外）的返回值要復(fù)雜許多。除了一個(gè)值顯示在單元格中，還有第二個(gè)隱藏值，可以看作是MDX表達(dá)式（SQL Server Analysis Services使用的多維表達(dá)式語(yǔ)言）的結(jié)果。當(dāng)一個(gè)數(shù)據(jù)聚集函數(shù)使用另一個(gè)數(shù)據(jù)集函數(shù)作為參數(shù)時(shí)，該參數(shù)取MDX值，而不是顯示值。

Here’s an illustration that I hope will help to make this clearer. Here is the asymmetric report that we looked at earlier. 我希望通過(guò)一個(gè)例子來(lái)更清楚地說(shuō)明這個(gè)概念。還是剛才我們看到的那個(gè)例子。

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實(shí)際包含兩個(gè)不同的值。第一個(gè)顯示在單元格中——“促銷”。單元格C3使用了C2為參數(shù)，而其所使用的值實(shí)際上是“[Sales Reason].[Sales Reasons].[Sales Reason].&[2]” ，是MDX返回的該“促銷”成員的唯一值。您可以使用Excel對(duì)象模式選擇C2單元格，然后在VBE立即窗口輸入“?Activecell.MDX”，找到藏在數(shù)據(jù)集函數(shù)中的隱藏值。

And that’s the overview. In my next post, I will provide some examples that illustrate how to use these functions in your reports. 以上是數(shù)據(jù)集函數(shù)的概述。下篇帖子，我將用些示例來(lái)說(shuō)明怎樣在報(bào)表中使用這些函數(shù)。

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

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。