Programming MS Office 2000 Web Components Chapter 4 Section 3

Chapter 4 Section 3Pivot Table component terminology

One of the purposes of designing the pivot table component is to make the table column data source and the multi-dimensional data source user interface consistent with the programming model. Although each type of data source has special requirements, we want the two data sources to be identical in appearance and usage.

At the same time, we consider that for business people who only need to extract the necessary data so that they can complete their work, the terms used in the OLAP field are too intuitive. Fortunately, the pivot report function of Excel has already established a set of standard terms, and many users are familiar with it. Therefore, we have followed the terms created in Excel and only changed a few of them, because these terms can better express the concepts in the field of table data and multidimensional data.

I will use the terms described in this section in the rest of this book, which are also used in the pivot control programming model. I will explain what each term represents in the control and what terms it corresponds to in the table data source and multi-dimensional data source.

Figure 4-14 shows a typical pivot report with the elements defined in the following sections highlighted. You can refer to this chart to find the position of each element in the pivot table control.

 

Figure 4-14. The elements in the pivot report.

 

Summary Value

For a table column data source, the summary value is a aggregate function of each detail value of the field (sum, sum, minimum, and maximum ). You can use the autocalc button on the toolbar or use programming to create the aggregate value. By default, because the source data of the table column data source is only a group of Row Records, it does not have a summary value. You can use sum, Count, Min, or Max Aggregate functions to create multiple aggregate values for any field. (Only fields with text attributes can be calculated in total .) You can also use the context menu of the pivot field list or use a program to delete any summary value.

For OLAP data sources, a summary value is a set of multiple values and a combination of values that represent one size in the supercube. All dimensions exposed by the OLAP provider are available in the pivot control. You can use these dimensions in the summary value. Unlike using a table-based data source, when you connect to an OLAP data source, you cannot create additional summary fields. If you need a calculated sum (for example, commodity net profit = commodity price-commodity cost), You must create a calculated size in the hyper cube. The Pivot Table Control exposes the dimension in the form of a summary value.

You can only place the summary fields in the data area (Central Area) of the pivot table control. They are generally used to generate all the values in the pivot table report. Visualized summary fields are displayed in the pivot field list one by one. A unique chart shows that they are summary fields.

 

FieldsetsAnd Fields

The term "field" is often used to describe a column in a table column data result set. It seems to be an important term used by Microsoft products. Although I prefer the term "column", "field" has been used in Microsoft Access and Excel history ". Therefore, the Office Web component uses field and fieldset to describe the result columns of a table column data source. For an OLAP data source, field is used to describe a level, and fieldset is used to describe a layer. A filedset is a collection of associated fields that belong to a layer. For example, the fieldset may include the fields country, state, and city.

The table column data source returns a result set that contains a set of completely unrelated fields. In other words, you cannot know which field and other fields belong to one layer. For example, if you include a country field and a state field in a result set at the same time, it does not indicate that these two fields are a layer-level metadata. Therefore, when a table column data source is used, fields in each result set are independent in the pivot control. Therefore, a field set contains only one field. The exception to this rule is the date field and the date/time field. When a date field is found in the result set, the PivotTable control automatically generates two additional fieldsets for this field, providing a calendar-based time layer for this field. One fieldset contains the year, quarter, month, and day fields, and the other contains the year, Week, and day fields. The pivot control creates these two additional field sets for each date or date/time field in the result set of your table column. Unfortunately, in the current version of the control, you cannot create your own field set layer from a set of result set fields.

When using an OLAP data source, the pivot table control creates a field set for each layer in the supercube. Some OLAP databases also allow you to define multiple layers for one dimension. The pivot control exposes each layer as a separate field set. Each field set contains one field for each level in the OLAP layer. If there is an all level, the control skips this level.

 

Member

The field set in the pivot table control contains a group of Members. A member corresponds to a unique category of a field in the field set. The members are displayed as row or column headers in the crosstab chart and do not scroll, so that they can always be seen.

When using a table column data source, the pivot table component creates a member for each unique value in each result set field. If a given field contains null or blank values, it also creates a blank member.

When using an OLAP data source, the pivot table control creates a member for each element in each layer, including all Members that may exist.

 

Rows, columns, filters, and data areas (axes)

The pivot control contains areas that you can use to build your reports. In programming models, a region is often called an axis. OLAP databases also use the term "axis" to describe a part of the query results. The row area is located in the area on the left of the control, and the row header is displayed in it. You can drag a field to this area to group your data by row. The column area is the area at the top of the control. The list header is displayed in it. You can drag a field to this area to group data by column. You can have any number of fields in these two regions. Of course, this is restricted by the available resources of your system.

The filter area is the entry that crosses the top of the control. You can place the fields to be filtered here and select a value at a time. For example, you may need to view the sales information of a product series, a country, or a salesperson. The data in the report needs to be filtered to display the summary values of only selected members. In filtering, you can place any number of fields. If you need a summary of all Members, you can select the "(all)" member. When using a table column data source, you select a local filter in a filter field on the client. This means that the pivot control still saves all the detailed data on the client, only data is filtered locally. To filter data on the server, you must use the WHERE clause in the Command text used to fill the pivot report.

The data area is located in the center of the report. The Pivot Table Control displays the summary value here. Placing the summary value in this area will result in the pivot table control displaying the value at the intersection of row and column members. You can prevent any number of report summary values in this area. By default, numeric values are displayed in separate columns (???).

The data area can also display any detail row that belongs to a specified summary value. Of course, this function is available only when the data source is a table column data. Therefore, the ole db for OLAP data source only returns the total value instead of the detailed source data after the total value. When operating the table column data source, the pivot table control can show hidden detail data by expanding a total unit and immediately displaying related detail rows. This allows you to simply double-click a number to view the details, so that the pivot table can expand the cells where the number is located and display the details of the total value.

 

Unique name, name, and title

When you use the pivot table component's programming model to write code, you will find that many objects contain three attributes related to their identities, each attribute actually represents some different information.

The uniquename attribute of an object returns the unique name provided by the data source. Many OLAP data sources contain a unique name for creating members, levels, dimensions, and so on. These names are almost unreadable. A unique name is generally an opaque string. You only need to read and use it without having to understand their internal format. However, they can ensure a unique identifier of the object. Here is an example of a unique name returned by the OLAP Service: "[time]. [All]. [1997]. [Q1]". When you search for an object in a collection or set a filter condition, you can use the unique name of an object. It is safest to use a unique name for these tasks because it prevents two different levels of a layer from containing the same member name, or include the same Member names at the same level, such as Portland, Maine, Portland, and Oregon. Both members have the same name ("Portland"), but their unique names are different ("[USA]. [Maine]. [Portland] ". "[USA]. [Oregon]. [Portland] ").

The name attribute of an object is more user-friendly than the uniquename attribute, but it must still be able to identify an object in a set. The initial value of the name attribute is the value in the object's caption attribute. However, if you change the value of the caption attribute for display purposes, the name attribute value does not change. The name attribute can also be used to search for an object in the set or to set a filter condition, however, you should use it only when you are sure that there are no members with the same name in the same layer.

When an object in a report is displayed, the object's caption attribute is used. You should not use it to search for an object in the set or set a filter condition. For example, if your report contains a summary field named sum of extendedprice, you may need to change the name to sales to make it readable. Changing the caption attribute does not affect the object's internal name, but changes the text displayed on the report.