2.2.5.3.2 Source Data

  • Article

A PivotCache (section 2.2.5.3) can be based on four different types of source data. The type of source data is specified by the sxvs field of the SXVS record (section 2.4.317).

When the source data type is SHEET as specified by SXVS, the data is read from the range specified by a DConRef (section 2.4.86), DConName (section 2.4.85), or DConBin (section 2.4.83) record. If the range is a table, then the PivotCache will have one cache field (section 2.2.5.3.5) for each column of the table using the column header captions for cache field names, and the data region values of the table are used as source data values, specified by cache records (section 2.2.5.3.12). Otherwise the PivotCache has one cache field for each column of the range, using the values in the first row of the range for cache field names, and all other rows are used as source data values, specified by cache records.

When the source data type is SCENARIO as specified by SXVS, no new source data is available for the PivotCache and the PivotCache cannot be refreshed. A snapshot of the source data might be available in the cache records.

When the source data type is EXTERNAL, as specified by SXVS, the source data is read from an external data source. There is an associated external connection (section 2.2.8) that is used to obtain data from the external data source. The external connection is specified by the sequence of records that conforms to the DBQUERY rule (section 2.1.7.20.3) in the sequence of records that conforms to the PIVOTCACHEDEFINITION rule (section 2.1.7.20.3) and the combination of other records as specified in section 2.2.8. For a non-OLAP PivotCache (section 2.2.5.3.4), the source data is a rectangular table and the PivotCache (section 2.2.5.3) has one cache field for each column of the table using the column header captions for cache field names, and the rows of the table are used as source data values, specified by cache records.

If the source data is external and the external connection is an OLAP Connection (section 2.2.8.3.1) then the PivotCache MUST be an OLAP PivotCache. For an OLAP PivotCache, the source data is handled by the data provider specified by the associated OLAP Connection and the PivotCache MUST NOT have cache records.

When the source data type is CONSOLIDATION as specified by SXVS, the source data is read from one or more ranges. For more details, see the section 2.2.5.3.2.1.

When a pivot field (section 2.2.5.4.3) is on the page axis (section 2.2.5.4.9.1) of the PivotTable view (section 2.2.5.4), the pivot field is a server-based page field if the fServerBased field of the SXVDEx record (section 2.4.310) is equal to 1 and the fServerBased field of the associated SXFDB record (section 2.4.283) is equal to 1. A server-based page field is a pivot field on the page axis that causes the query that is used to retrieve source data for populating the PivotCache to be parameterized. The query is parameterized according to the page filter criteria, as specified in section 2.2.5.4.9.1. This feature can only be used for an ODBC PivotCache.