Translator's description: Welcome to my blog: http://blog.9cbs.net/daidaoke2001/
The mistakes in the translation or improper look at it, this is also the greatest driving force I insist on the translation work.
My email: tangtaike@hotmail.com
For reprint, please notify us in advance.
Chapter IV Third Sorteral Pivot Table Parts Terminology
One of our purpose of designing perspective, is the consistent user interface and programming model of the table column data source and multi-dimensional data sources. Although each type of data source has special requirements, we hope that the same parts of these two data sources can be consistent in appearance and use.
At the same time, we have considered those terms used in the OLAP field for commercial people who only need to extract their required data so that they can complete their work. Fortunately, Excel's perspective report function has established a standard term, and many users are very familiar. Therefore, we have used the terms established by these Excel, only to change several, because these terms can better express the concepts in the table data and the field of cubes.
The terms used in this section will be used in the remainder of this book. These terms are used in the programming model of the perspective control. I will explain what terminology represents the contents of the control, and what terms in the terminology corresponding to the table data source and the multi-dimensional data source.
Figure 4-14 shows a typical perspective table, and highlighting each element defined in each section below. Refer to this chart to know the location of each element in the perspective control.
Figure 4-14. Perspective representation of each element in the report.
Summary value
For a list of data sources, the summary value is a total of a total value of the field of the field (summary, the total number, the minimum value, the maximum value). You can use the AutoCalc button on the toolbar or create a summary value by programming. You can use SUM, Count, Min, or Max to create multiple summary values for any field. (Only the field of text attributes can be calculated.) You can also use the context menu of the Poscope field list, or by programs to delete any summary values.
For OLAP data sources, the summary value is a collection of multiple values and total values representing a size in the super cube. All dimensions exposed by OLAP providers are available in the perspective control, you can use these dimensions in the summary value. Unlike the use of the list of data sources, you cannot create additional summary fields when connecting to an OLAP data source. If you need a calculated summary value (such as product net profit = commodity price - product cost), you must create a computing size in the superconfiguration. The perspective table control will expose this size in the form of a summary value.
You can only place the summary field in the data area (central region) of the perspective control, which is generally used to generate all values in the perspective table. Visual summary fields are displayed in the list of perspective table fields one by one, and they are summarized by a unique chart.
Fieldsets and Fields
The term "field" is often used to describe the columns in a table column data result, which seems to be an important term used by Microsoft products. Although I am more willing to use the term "column", Microsoft Access and Excel have been using "field". Therefore, the Office Web component uses Field and FieldSet to describe the result column of a table column data source. For an OLAP data source, Field is used to describe a level, and the fieldset is used to describe a layer. A FileDset is a collection of some associated fields that belong to a layer - for example, geographic FieldSet may include Country, State, City these field.
The table column data source returns a set of result sets that contain a set of completely unrelated fields. In other words, you can't know which fields are together with which other fields belong to a layer. For example, if you also include a country field and a state field in a result set, no metadata of the level of the layer. Therefore, when a list of table is used, the fields in each result set are independent in the perspective control, and thus a field set contains only one field. The exception of this rule is the date field and date / time field. When the perspective control found a date field in the result set, it automatically generates two additional fieldSets for this field, providing this field-based time layer. A fieldset contains fields Year, Quarter, Month, and Day, and the other contains fields Year, Week and Day. Pivot Table Controls Create these two additional field sets for each date of your list of listings, or date / time fields. Unfortunately, in the current version of the control, you cannot create your own field clip from the field of a set of result sets. When using the OLAP data source, the perspective table control creates a field set for each layer in the super cube. Some OLAP databases also allow you to define multiple layers for a dimension. The perspective table control will expose each layer into a separate field set. Each field set contains a field for each level in the OLAP layer, and if there is a ALL level, the control will skip this level.
member
The field set in the perspective table control contains a group of members, a member corresponding to a unique category in a field in the field. Members are displayed in the cross report as a row head or column header, which will not scroll so that they can always see them.
When using a list of data sources, the perspective table component creates a member for each unique value in each result set field. It will also create a Blank member if it finds that the NULL or a blank value is included in a given field.
When using an OLAP data source, the Pivot table control creates a member for each element in each layer, including all ALL members that may exist.
Row, column, filter, and data area (axis)
Pivot table controls contain some areas where you can use to build your report. The region is often referred to as a shaft in the programming model. The OLAP database also uses the term "axis" to describe a portion of the query results. The row area is the area located on the left side of the control, and the row header is displayed there, you can drag and drop a field to this area to deliver your data by line. The column area is the area where the top of the control is displayed, and the list is displayed there, you can drag and drop a field to this area to implement the data by column. You can any number of fields in both areas, of course, this is limited by your system available resources.
The filter area is a bar crossing the top of the control. You can place the field you need to filter here and select a value once. For example, you may need to view a product line, or a country, or a sales information of a sales person. The data in the report needs to be filtered to display only the summary values of the selected members. You can place any more fields in the filtering. If you need the summary of all members, you can choose "(all) members. When using a list of data sources, the options you made in a filter field are local filtering on the client, which means that the perspective controls still save all the detail data on the client, just in local The data was filtered. If you need to filter data on the server, you must use the WHERE clause in command text used to populate the perspective report.
The data area is area in the center of the report, and the perspective control is here a summary value. Place the summary value in this area causes the perspective table control to display the value of the intersection of the line member and the column member. You can prevent any report on this area. By default, the numeric value is displayed in a separate column (???).
The data area can also display any detailed lines belonging to a specified summary value, of course, only this function is only when the data source is a list of tables. Therefore, the OLE DB for OLAP data source only returns the round counter, and does not return fine source data behind these composite values. When operating the table column data source, the perspective table control can display hidden detail data by expanding a total unit and displaying the relevant detailed lines. This allows the user to double-click a number that needs to view the details, so that the perspective is enabled in the cell where the number is located and displays a detail data belonging to this consolidated value. Unique name, name and title
When you use the programming model of the perspective table component to write code, you will find that many objects contain three attributes associated with their identity, and each attribute does represent some different information.
A UNIQUENAME attribute of an object returns the unique name provided by the data source. Many OLAP data sources include a method of creating a member, level, dimension, and so on, and these names are almost unreadable. The only name is generally an opaque string that you only need to read and use it without having to know their internal format; however, they can ensure that the object is uniquely identified. Here is an example of a unique name returned by an OLAP service: "[Time]. [All]. [1997]. [Q1]". When you look for an object in a collection, or when you set a filter condition, you can use a unique name for an object. For these tasks, use the unique name is the safest because it prevents the same member name in two different levels of a layer, or contains the same member name in the same level, such as portland, MAINE And Portland, Oregon. Both members contain the same name ("Portland"), but their only name is different ("[USA]. [MAINE]. [Portland]" vs. "[USA]. [OREGON]. [Portland] ").
An object's Name property is friendly than a uniqueename property, but it still wants to identify an object in a collection. The initial value of the Name property is the value in the Caption property of the object, but if you change the value of the CAPTION property in order to display, the NAME attribute value does not change. The name attribute can also be used to find an object in a collection, or for setting up a filter condition, but you should only use it if you are confident that there is no different names in the same layer.
Use the CAPTION property of the object when you display objects in the report. You should not use it to find an object in the collection or set a filter condition. For example, if your report contains a summary field named SUM OF EXTENDEDPRICE, you may need to make the name easy to read, you need to change this name to Sales. Change the CAPTION attribute does not affect the internal name of the object, but it changes the text displayed on the report.
附 附.
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.
Totals
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 .
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 can not 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.
FIELDSETS AND FIELDS
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 can not 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 There Two Extra Fieldsets for Each Date / Time Field in Your Tabula resultset. Unfortunately, you can not 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.
MEMBERS
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.
..................
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 area as you want, limited of course by your offer 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
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 withportland
,
Maine
and
Portland
,
Oregon
. Both of these Members Have The Same Name ("
Portland
"), but their unique names are different ("
USA
].
Maine
].
Portland
] "vs." [
USA
].
Oregon
].
Portland
] ").
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.
