译者说明：欢迎访问我的Blog: http://blog.csdn.net/daidaoke2001/
译文中的错误或不当之处望不吝指出，这也是我坚持翻译工作的最大动力。
我的Email：[email protected]
如需转载，请事先通知。
第四章第三节 透视表组件术语
我们设计透视表组件的目的之一，就是使表列数据源和多维数据源的用户界面和编程模型保持一致。虽然每种类型的数据源都有特殊的要求，但我们希望这两种数据源相同的部分能够在外观和使用上保持一致。
同时，我们考虑到对于那些只是需要提取必须的数据，以便能够完成他们的工作的商业人员来说，OLAP领域中使用的那些术语太不直观了。幸运的是，Excel的透视表报表功能已经建立起一套标准的术语，而且很多用户也对其很熟悉了。因此我们沿用了这些Excel建立的术语，仅仅改动了其中的几个，因为这几个术语能够更好的表达表列数据和多维数据领域中的概念。
我会在本书剩下部分中使用本节所介绍的术语，透视表控件的编程模型中使用的也是这些术语。我会解释每一个术语代表控件中的什么内容，以及这个术语对应表列数据源和多维数据源中的什么术语。
图4－14显示了一个典型的透视表报表，并突出显示了下面各节中定义的各元素。参考这个图表可以得知透视表控件中各元素的所在位置。
图4－14。透视表报表中的各元素。
汇总值
对于一个表列数据源来说，汇总值是字段的各明细值的一个合计函数（求总和，求总数，求最小值，求最大值）。可以使用工具条上的AutoCalc按钮或通过编程来创建汇总值，缺省情况下，因为表列数据源的源数据只是一组行记录，所以它没有汇总值。您可以使用Sum,Count,Min,或Max这些合计函数来为任何字段创建多个汇总值。(只有文本属性的字段可以被计算总数。)您也可以使用透视表字段列表的上下文菜单，或者通过程序来删除任何一个汇总值。
对于OLAP数据源来说，汇总值是代表超立方体中的一个尺寸的多个数值和合计值的集合。OLAP提供者暴露的所有尺寸在透视表控件中都可用，您可以在汇总值中随意使用这些尺寸。和使用表列数据源不同，当连接到一个OLAP数据源时，您不能创建额外的汇总字段。如果您需要一个计算出的汇总值（例如商品净利润＝商品价格－商品成本），您必须在超立方体中创建一个计算尺寸。透视表控件会将这个尺寸以一个汇总值的形式暴露出来。
您只能将汇总字段放在透视表控件的数据区域（中央区域）中，它们一般用来生成透视表报表中的所有数值。可视的汇总字段会逐一显示在透视表字段列表中，通过一个独特的图表来说明它们是汇总字段。
Fieldsets和Fields
术语“field”常用来描述一个表列数据结果集中的列，它似乎是微软各产品所使用的一个重要术语。虽然我更愿意使用术语“column”，但是微软Access和Excel历史上一直在使用“field”。因此，Office Web组件使用field和fieldset来描述一个表列数据源的结果列。对于一个OLAP数据源来说，field被用来描述一个级别，fieldset被用来描述一个层。一个filedset是一个共同属于一个层的一些相关联的field的集合――例如，地理这个fieldset可能包括Country，State，City这些field。
表列数据源返回一个包含一组完全不相关的字段的结果集。换句话说，您完全无法知道哪个字段与哪些其它字段共同属于一个层。例如，如果您在一个结果集中同时包含一个Country字段和一个State字段，则没有说明这两个字段是一个层的级别的元数据。因此，当使用一个表列数据源时，每一个结果集中的字段在透视表控件中都是独立的，因而一个字段集中只包含一个字段。这个规则的例外情况是日期字段和日期/时间字段。当透视表控件在结果集中发现一个日期字段时，它自动为这个字段生成两个额外的fieldset，为这个字段提供了基于日历的时间层。一个fieldset包含字段Year,Quarter,Month和Day，而另一个包含了字段Year,Week和Day。透视表控件为您的表列结果集中的每个日期，或日期/时间字段创建这两个额外的字段集。很遗憾，在当前版本的控件中，您不能从一组结果集的字段中创建您自己的字段集层。
在使用OLAP数据源时，透视表控件为超立方体中的每个层创建一个字段集。一些OLAP数据库还允许您为一个维定义多个层。透视表控件会将每一个层暴露为一个单独的字段集。每个字段集为OLAP层中的每个级别包含一个字段，如果存在ALL级别，控件将跳过这个级别。
成员
透视表控件中的字段集包含一组成员，一个成员对应字段集中某个字段中的一个独特类别。成员在交叉报表中显示为行标头或列标头，并不会滚动，这样就总是能够看到它们。
在使用表列数据源时，透视表组件为每个结果集字段中每个独特的值创建一个成员。如果发现了给定字段中包含NULL或空白值时，它也会创建一个Blank成员。
在使用OLAP数据源时，透视表控件在每个层中为每个元素创建一个成员，包括可能存在的All成员。
行，列，过滤，和数据区（轴）
透视表控件包含一些您可用来构建您的报表的区域。在编程模型中区域常被称为轴。OLAP数据库也使用术语“轴”来描述查询结果的一个部分。行区域是位于控件左边的区域，行标头在其中显示，您可以将一个字段拖放到这个区域，来实现将您的数据按行来分组。列区域是横穿控件顶部的区域，列表头在其中显示，您可以将一个字段拖放到这个区域，来实现按列来分组数据。您可以在这两个区域中任意数量的字段，当然，这是受您系统的可用资源限制的。
过滤区是横穿控件顶部的条。您可以将需要过滤的字段放置在这里，一次选择一个值。例如，您可能需要查看一个产品系列、或一个国家、或一个销售人员的销售信息。报表中的数据需要被过滤，以显示仅属于被选中的成员的汇总值。在过滤去中您可以放置任意多的字段，如果您需要所有成员的汇总，可以选择“（ALL）”成员。在使用表列数据源时，您在一个过滤字段中所作的选择是在客户端上的一个本地过滤，这就意味着透视表控件在客户端上仍然保存了所有的细目数据，只是在本地对数据进行了过滤。如果您需要在服务器端对数据进行过滤，您必须在用来填充透视表报表的命令文本中使用WHERE子句。
数据区是位于报表中央的区域，透视表控件在这里显示汇总值。将汇总值放置在这个区域中会导致透视表控件显示行成员和列成员的交叉点的数值。您可以在这个区域中防止任意多的报表汇总值。缺省情况下，数字值被显示在单独的列中（？？？）。
数据区也能够显示属于一个指定汇总值的任何细目行，当然，只有当数据源是表列数据时才具有这个功能。因此OLE DB for OLAP数据源只返回合计值，而不返回这些合计值后面的细目源数据。在操作表列数据源时，透视表控件能够通过展开一个合计单元，并立刻显示相关的细目行的方式来显示隐藏的细目数据。这使得用户可以仅仅双击一个需要查看细目信息的数字，就能使透视表展开这个数字所在的单元格，并显示属于这个合计值的细目数据。
唯一名称，名称和标题
在您使用透视表组件的编程模型来书写代码时，您会发现许多对象都包含与它们的身份相关的三个属性，而每个属性也确实代表了一些不同的信息。
一个对象的UniqueName属性返回了数据源提供的唯一名称。许多OLAP数据源都包含一个创建成员，级别，维等等的唯一名称的方法，而这些名称几乎是不可读的。唯一名称一般是一个不透明的字符串，您只需读取和使用它，而不必了解它们的内部格式；不过，它们能够保证对对象进行唯一标识。这里有一个OLAP服务返回的唯一名称的例子：“[Time].[All].[1997].[Q1]”。当在一个集合中查找一个对象，或在设置一个过滤条件时，您可以使用一个对象的唯一名称。对于这些任务，使用唯一名称是最安全的，因为它防止了在在某个层的两个不同的级别中包含相同的成员名，或在同一个级别中包含相同的成员名，例如Portland,Maine和Portland,Oregon。这两个成员都包含相同的名称（“Portland”），但是它们的唯一名称是不一样的（("[USA].[Maine].[Portland]" vs. "[USA].[Oregon].[Portland]"）。
一个对象的Name属性比UniqueName属性要友好一些，但是它仍然要能标识一个集合中的一个对象。Name属性的初始值是对象的Caption属性中的值，但如果您为了显示的需要改变了Caption属性的值， Name属性值并不会发生变化。Name属性也可以被用于在集合中查找一个对象，或用于设置一个过滤条件，但您应该只在确信同一层中没有相同名称的多个成员的情况下使用它。
当显示报表中的对象时使用对象的Caption属性。您不应使用它来在集合中查找一个对象，或设置一个过滤条件。例如，如果您的报表中包含一个名为Sum of ExtendedPrice的汇总字段，可能为了使名称易读，您需要将这个名称改为Sales。改变Caption属性不会影响对象的内部名称，但它会改变显示在报表上的文本。
附录：英文原文。
PivotTable Component Terminology
One of our goals while designing the PivotTable component was to make the user interface and programming model consistent between tabular and multidimensional data sources. Although each type of data source has special requirements, we wanted the aspects of the two that overlapped to look and feel the same.
At the same time, we thought that the terminology commonly used in the OLAP world was less than intuitive for businesspeople who simply want to retrieve the data they need to perform their jobs. Fortunately, the Excel PivotTable report feature has established a set of standard terminology with which many users are already familiar. We followed the terminology established by Excel, but changed just a few names where there was a better term to express the concept in the worlds of both tabular and multidimensional data.
The terminology presented here is the terminology I will use throughout the rest of the book, and it is the terminology used in the programming model of the PivotTable control. I will explain what each of these terms represents in the control and what the term maps to in both the tabular and multidimensional data source terminology.
Figure 4-14 shows a typical PivotTable report and highlights various elements defined in the following sections. Refer to this diagram to see where the PivotTable control displays a particular element.
Figure 4-14. Various elements of a PivotTable report.
For a tabular data source, a total is an aggregation (sum, count, minimum, or maximum) of the detail values in a field. Totals are created using the AutoCalc toolbar button or the programming model. By default, a tabular data source has no totals since the source data is merely a set of rows. You can create multiple totals for any field using the Sum, Count, Min, or Max summary functions. (Text-based fields can only be counted.) You also can delete any totals you create by using either the context menu in the PivotTable Field List or the programming model.
For an OLAP data source, a total is a collection of values and aggregates that represent a measure in the hypercube. All measures exposed from the OLAP provider will be available to the PivotTable report, and you can include any set of them. Unlike using the tabular data source, you cannot create additional totals when connected to an OLAP data source. If you want a calculated total (such as Store Sales Net = Store Sales - Store Cost), you must create a calculated measure in the hypercube. This measure will be exposed as a total in the PivotTable control.
You can place totals only in the data area (the center) of the PivotTable control, and they typically generate all the numbers in a PivotTable report. The totals available to view are displayed separately in the PivotTable Field List, with a unique icon denoting that they are indeed totals.
The term "field" is often used to describe a column in a tabular data resultset, and it seems to be the predominant term used in Microsoft products. Although I prefer the term "column," Microsoft Access and Excel have too much history using "field." For this reason, the Office Web Components use field and fieldset to describe a result column for a tabular data source. For an OLAP data source, field is used to describe a level and fieldset is used to describe a hierarchy. A fieldset is a collection of related fields that belong together in a hierarchy—for example, the Geography fieldset might contain the fields Country, State, and City.
A tabular data source returns a resultset containing a set of completely unrelated fields. In other words, you cannot know universally that any particular field belongs with any other field in a hierarchy. For example, if you have both a Country and a State field in a resultset, there is no metadata indicating that the two fields are levels of a single hierarchy. For this reason, when using a tabular data source, every result field is independent in the PivotTable control and is therefore a fieldset with just one field. The exceptions to this rule are date fields and date/time fields. When the PivotTable control encounters a date field in the resultset, it automatically generates two additional fieldsets that provide a calendar-based time hierarchy for the field. One fieldset contains the fields Year, Quarter, Month, and Day while the other contains the fields Year, Week, and Day. The PivotTable control creates these two extra fieldsets for each date or date/time field in your tabular resultset. Unfortunately, you cannot create your own fieldset hierarchies from a set of result fields in this version of the control.
When using an OLAP data source, the PivotTable control creates a fieldset for each hierarchy in the hypercube. Some OLAP databases allow you to define multiple hierarchies for a single dimension. The PivotTable control will expose each hierarchy as a separate fieldset. Each fieldset contains one field for each level in the OLAP hierarchy, skipping the All level if it exists.
A fieldset in the PivotTable control contains a set of members, one for each distinct category in each of its fields. Members are displayed as row or column headings in a crosstab report and are frozen to scrolling so that they are always visible.
When using a tabular data source, the PivotTable component creates a member for each distinct value in each result field. It also creates a member called (Blank) if it finds any Null or blank values in a given field.
When using an OLAP data source, the PivotTable control creates a member for each element in each hierarchy, including the All member if it exists.
Row, Column, Filter, and Data Areas (Axes)
The PivotTable control has a few areas that you can use to construct your report. Areas often are called axes in the programming model. OLAP databases also use the term "axis" to describe part of a query result. The row area is the region to the left of the control where the row headings are displayed and on which you can drop a field to group your data by rows. The column area is the region across the top of the control where the column headings are displayed and on which you can drop a field to group your data by columns. You can place as many fields in these two areas as you want, limited of course by your available system resources.
The filter area is the strip across the top of the control. This area is where you place fields that you want to filter by, choosing one value at a time. For example, you might want to see sales information for one product line, one country, or one salesperson. The data in the report is filtered to show the totals attributed to only the selected member. You can place as many fields in the filter area as you want, and if you want the totals for all members in the field, select the "(All)" member. When using a tabular data source, the selection you make in a filter field is used as a local filter on the client, meaning that the PivotTable control still has all the detail data on the client and is simply filtering the data locally. If you want to filter the data at the server, you must use a WHERE clause in the command text used to populate the PivotTable report.
The data area is the region in the center of the report where the PivotTable control displays totals. Totals placed in this area will cause the PivotTable control to display numbers for the intersections of row and column members. You can add as many totals to the report as you want. The numerical values are displayed in separate columns by default.
The data area also is capable of showing any detail rows that are available for a given total. This capability is of course available only when the data source is tabular because OLE DB for OLAP data sources return only aggregates, not the detail source data behind those aggregates. When working with a tabular data source, the PivotTable control can display the detail data behind any aggregate by expanding the aggregate cell and showing the detail rows in place. This allows the user to simply double-click a number about which he or she wants to see more detail, causing the PivotTable to expand the cell occupied by that number and show the detail data rows contributing to that aggregate.
When you start writing code using the PivotTable component's programming model, you will notice that many objects have three properties related to their identity. Each of these properties does in fact represent something different.
The UniqueName property of an object returns its unique name as reported by the data source. Many OLAP data sources have a method for creating unique names for members, levels, dimensions, and so on, and these names are hardly fit for anyone to see. Unique names are intended to be opaque strings that you retrieve and use without trying to understand their internal format; however, they are guaranteed to uniquely identify the object. An example of a unique name from OLAP Services is "[Time].[All].[1997].[Q1]". You can use the unique name of an object when looking it up in a collection or when setting a filter. Often it's safest to do so because this disambiguates cases in which you might have the same member name at two different levels in a single hierarchy or at the same level, as with
The Name property of an object is friendlier than the UniqueName property, but it is still intended to identify an object in a collection. The Name property is initialized to the value of the object's Caption property, but the Name property will not change if you change the Caption property for display purposes. The Name property also can be used to look up an object in a collection or when setting a filter, but you should use it only if you know that you do not have multiple members with the same name in a hierarchy.
You use an object's Caption property when displaying the object in the report. You should not use it to look up an object in a collection or when setting a filter. For instance, if your report has a total named Sum of ExtendedPrice, you might want to adjust it to Sales in the report since that name is easier to read. Changing the Caption property has no effect on the internal names of objects, but it will change what is displayed in the report.
本文地址：http://com.8s8s.com/it/it43946.htm