<cacheField> (PivotCache Field)

Represent a single field in the PivotCache. This definition contains information about the field, such as its source, data type, and location within a level or hierarchy. The sharedItems complex type stores additional information about the data in this field. If there are no shared items, then values are stored directly in the pivotCacheRecords part.

example: 
<cacheField name="Group" numFmtId="0">
  <sharedItems count="3">
    <s v="Pacific"/>
    <s v="North America"/>
    <s v="Europe"/>
  </sharedItems>
</cacheField>

Parent Elements

<cacheFields> (§3.10.1.4)

Child Elements

Subclause

<extLst> (Future Feature Data Storage Area)

§3.2.10

<fieldGroup> (Field Group Properties)

§3.10.1.30

<mpMap> (Member Properties Map)

§3.10.1.58

<sharedItems> (Shared Items)

§3.10.1.90

Attributes

Description

<caption> (PivotCache Field Caption)

Specifies the caption of the cache field.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

<databaseField> (Database Field)

Specifies a boolean value that indicates whether this field came from the source database rather having been created by the application.

A value of on, 1, or true indicates the field is from the source database.

A value of off, 0, or false indicates the field was created by the application.

Note:

The possible values for this attribute are defined by the XML Schema boolean datatype.

<formula> (Calculated Field Formula)

Specifies the formula for the calculated field. This formula is specified by the end-user. Calculated fields can perform calculations by using the contents of other fields in the PivotTable.

In formulas you create for calculated fields or calculated items, you can use operators and expressions as you do in other worksheet formulas. You can use constants and refer to data from the PivotTable, but you cannot use cell references or defined names. You cannot use worksheet functions that require cell references or defined names as arguments, and you cannot use array functions.

Further behaviors and restrictions apply to formulas for calculated fields:

  • Formulas for calculated fields operate on the sum of the underlying data for any fields in the formula. For example, the formula =Sales * 1.2 multiplies the sum of the sales for each type and region by 1.2; it does not multiply each individual sale by 1.2 and then sum the multiplied amounts.

  • Formulas cannot refer to totals.

For more information about formulas see §3.17 in Formulas. For more information about defined names see §3.2.6 in Workbook.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

<hierarchy> (Hierarchy)

Specifies the hierarchy that this field is part of.

The possible values for this attribute are defined by the XML Schema int datatype.

<level> (Hierarchy Level)

Specifies the hierarchy level that this field is part of.

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

<mappingCount> (Member Property Count)

Specifies the number of property mappings for this field.

The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

<memberPropertyField> (Member Property Field)

Specifies a boolean value that indicates whether the field contains OLAP member property information.

A value of on, 1, or true indicates this field contains OLAP member property information.

A value of off, 0, or false indicates this field does not contain OLAP member property information.

The possible values for this attribute are defined by the XML Schema boolean datatype.

<name> (PivotCache Field Name)

Specifies the name of the cache field.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

<numFmtId> (Number Format Id)

Specifies the number format that is applied to all items in the field. Number formats are written to the styles part. For more information see §3.8.31 in Styles.

Note: Formatting information provided by cell table and by PivotTable need not agree. If the two formats differ, the cell-level formatting takes precedence. If you change the layout of the PivotTable, the PivotTable formatting will then take precedence.

The possible values for this attribute are defined by the ST_NumFmtId simple type (§3.18.49).

<propertyName> (Property Name)

Specifies the name of the property if this field is an OLAP property field.

The possible values for this attribute are defined by the ST_Xstring simple type (§3.18.96).

<serverField> (Server-based Field)

Specifies a boolean value that indicates whether the field is a server-based page field.

A value of on, 1, or true indicates this field is a server-based page field.

A value of off, 0, or false indicates this field is not a server-based page field.

Note: this attribute applies to ODBC sources only.

The possible values for this attribute are defined by the XML Schema boolean datatype.

<sqlType> (SQL Data Type)

Specifies the SQL data type of the field. This attribute stores an

ODBC data type and applies to ODBC data sources only. A value is supplied for this attribute only if it is provided to the application.

The following are data types supported by ODBC. For a more information, see the ODBC specification.

  • 0 SQL_UNKNOWN_TYPE

  • 1 SQL_CHAR

  • 2 SQL_VARCHAR

  • -1 SQL_LONGVARCHAR

  • -8 SQL_WCHAR

  • -9 SQL_WVARCHAR

  • -10 SQL_WLONGVARCHAR

  • 3 SQL_DECIMAL

  • 2 SQL_NUMERIC

  • 5 SQL_SMALLINT

  • 4 SQL_INTEGER

  • 7 SQL_REAL

  • 6 SQL_FLOAT

  • 8 SQL_DOUBLE

  • -7 SQL_BIT

  • -6 SQL_TINYINT

  • -5 SQL_BIGINT

  • -2 SQL_BINARY

  • -3 SQL_VARBINARY

  • -4 SQL_LONGVARBINARY

  • 9 SQL_TYPE_DATE or SQL_DATE

  • 10 SQL_TYPE_TIME or SQL_TIME

  • 11 SQL_TYPE_TIMESTAMP or SQL_TIMESTAMP

  • 102 SQL_INTERVAL_MONTH

  • 101 SQL_INTERVAL_YEAR

  • 107 SQL_INTERVAL_YEAR_TO_MONTH

  • 103 SQL_INTERVAL_DAY

  • 104 SQL_INTERVAL_HOUR

  • 105 SQL_INTERVAL_MINUTE

  • 106 SQL_INTERVAL_SECOND

  • 108 SQL_INTERVAL_DAY_TO_HOUR

  • 109 SQL_INTERVAL_DAY_TO_MINUTE

  • 110 SQL_INTERVAL_DAY_TO_SECOND

  • 111 SQL_INTERVAL_HOUR_TO_MINUTE

  • 112 SQL_INTERVAL_HOUR_TO_SECOND

  • 113 SQL_INTERVAL_MINUTE_TO_SECOND

  • -11 SQL_GUID

  • -20 SQL_SIGNED_OFFSET

  • -22 SQL_UNSIGNED_OFFSET

The possible values for this attribute are defined by the XML Schema int datatype.

<uniqueList> (Unique List Retrieved)

Specifies a boolean value that indicates whether the application was able to get a list of unique items for the field. The attribute only applies to PivotTables that use ODBC and is intended to be used in conjunction with optimization features in the application. For example, the application can optimize memory usage when populating PivotCache records if it has a list of unique items for a field before all the records are retrieved from ODBC.

A value of on, 1, or true indicates the application was able to get a list of unique values for the field.

A value of off, 0, or false indicates the application was unable to get a list of unique values for the field.

The possible values for this attribute are defined by the XML Schema boolean datatype.

The following XML Schema fragment defines the contents of this element:

<complexType name="CT_CacheField">
	<sequence>
	<element name="sharedItems" type="CT_SharedItems" minOccurs="0" maxOccurs="1"/>
	<element name="fieldGroup" minOccurs="0" type="CT_FieldGroup"/>
	<element name="mpMap" minOccurs="0" maxOccurs="unbounded" type="CT_X"/>
	<element name="extLst" minOccurs="0" type="CT_ExtensionList"/>
	</sequence>
	<attribute name="name" type="ST_Xstring" use="required"/>
	<attribute name="caption" type="ST_Xstring" use="optional"/>
	<attribute name="propertyName" type="ST_Xstring" use="optional"/>
	<attribute name="serverField" type="xsd:boolean" use="optional" default="false"/>
	<attribute name="uniqueList" type="xsd:boolean" use="optional" default="true"/>
	<attribute name="numFmtId" type="ST_NumFmtId" use="optional"/>
	<attribute name="formula" type="ST_Xstring" use="optional"/>
	<attribute name="sqlType" type="xsd:int" use="optional" default="0"/>
	<attribute name="hierarchy" type="xsd:int" use="optional" default="0"/>
	<attribute name="level" type="xsd:unsignedInt" use="optional" default="0"/>
	<attribute name="databaseField" type="xsd:boolean" default="true"/>
	<attribute name="mappingCount" type="xsd:unsignedInt" use="optional"/>
	<attribute name="memberPropertyField" type="xsd:boolean" use="optional" default="false"/>
</complexType>