This article concentrates on enhancing your knowledge on Pivot Tables in SpreadsheetML.

 

A pivot table is a great reporting tool that sorts and sums independent of the original data layout in the spreadsheet. It simplifies large data sets and aggregate information by rows/columns.

 

Consider the following data was has to be aggregated as a Pivot Table in a spreadsheet.

Employee

Designation

Location

Salary

E1

D1

L1

1000

E2

D1

L2

500

E3

D2

L1

1500

E4

D2

L2

1600

E5

D1

L2

800

E6

D3

L3

2000

 

The Pivot Table in the spreadsheet would look this way –

In Open XML, the Pivot Table has two parts, the pivotTables and the pivotCache.

 

pivotTables

The pivotTables is where the part (pivotTable1.xml) for the pivotTableDefinition is present. The Pivot Table is always associated with the work sheet and hence the worksheet part-relationship item contains a relationship to the Pivot Table parts. In the worksheet relationship (sheet1.xml.rels), we will have the entry as below –

<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">

  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotTable" Target="../pivotTables/pivotTable1.xml"/>

</Relationships>

 

The pivotTableDefinition represents PivotTable root element for non-null PivotTables. A single sheet can contain multiple Pivot Tables and each of these will have a distinct pivotTableDefinition.

The different elements contained in the definition are –

·         location (Location information)

·         pivotFields (Collection of fields)

·         rowFields (Fields on the row axis)

·         rowItems (Items on the row axis with specific values)

·         colItems (Items on the column axis with specific values)

·         dataFields (Fields on the report filter region)

·         pivotTableStyleInfo (Style information)

 

Example –

<pivotTableDefinition ....>

  <location ref="G8:H12" ..../>

  <pivotFields count="4">

    <pivotField showAll="0">

      <items count="7">

        ....

      </items>

    </pivotField>

    <pivotField axis="axisRow" showAll="0">

      <items count="4">

        ....

      </items>

    </pivotField>

    <pivotField showAll="0">

      <items count="4">

        ....

      </items>

    </pivotField>

    <pivotField dataField="1" showAll="0"/>

  </pivotFields>

  <rowFields count="1">

    <field x="1"/>

  </rowFields>

  <rowItems count="4">

    <i>

      <x/>

    </i>

    <i>

      <x v="1"/>

    </i>

    <i>

      <x v="2"/>

    </i>

    <i t="grand">

      <x/>

    </i>

  </rowItems>

  <colItems count="1">

    <i/>

  </colItems>

  <dataFields count="1">

    <dataField name="Sum of Salary" ..../>

  </dataFields>

  <pivotTableStyleInfo name="PivotStyleLight16" showRowHeaders="1" />

</pivotTableDefinition>

 

As seen from the above example, the pivotTable concentrates on the layout and format of the Pivot Table on the worksheet. It has the information of what fields are on the row axis, the column axis, report filter, and values areas of the PivotTable. For the report filter we can see that under dataField we have used Sum as a filter, the other possible filters are Count, Average, Max, Min, Product, Count Numbers, StdDev, Var.

 

pivotCache

The pivotCache is where the parts (pivotCacheDefinition1.xml) for the pivotCacheDefinition and pivotCacheRecords (pivotCacheRecords1.xml) are present. The pivotCache contains the fundamental data of the Pivot Table to be aggregated. The Pivot Table part-relationship item contains a relationship to the Pivot Table Cache Definition part. In the pivotTable relationship (pivotTable1.xml.rels), we will have the entry as below –

<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">

  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheDefinition" Target="../pivotCache/pivotCacheDefinition1.xml"/>

</Relationships>

 

The pivotCacheDefinition defines each field in the source data (cacheField), including the name (name), the string resources of the instance data (for shared items - sharedItems), and information about the type of data that appears in the field (numFmtId).

 

Example –

<pivotCacheDefinition ….. r:id="rId1" ….. recordCount="6">

  <cacheSource type="worksheet">

    <worksheetSource ref="A1:D7" sheet="Sheet1"/>

  </cacheSource>

  <cacheFields count="4">

    <cacheField name="Employee" numFmtId="0">

      <sharedItems count="6">

        <s v="E1"/>

        <s v="E2"/>

        <s v="E3"/>

        <s v="E4"/>

        <s v="E5"/>

        <s v="E6"/>

      </sharedItems>

    </cacheField>

    <cacheField name="Designation" numFmtId="0">

      <sharedItems count="3">

        <s v="D1"/>

        <s v="D2"/>

        <s v="D3"/>

      </sharedItems>

    </cacheField>

    <cacheField name="Location" numFmtId="0">

      <sharedItems count="3">

        <s v="L1"/>

        <s v="L2"/>

        <s v="L3"/>

      </sharedItems>

    </cacheField>

    <cacheField name="Salary" numFmtId="0"> ..... </cacheField>

  </cacheFields>

</pivotCacheDefinition>

 

The pivotCacheDefinition part also defines pivot items that are shared among the pivotTable and pivotRecords parts.

 

The different elements contained in the definition are –

·         cacheSource (underlying data source)

·         cacheFields (collection of field definitions in data source)

·         cacheField (information about each single field)

 

The pivotCacheRecords is a collection of records in the Pivot Cache. Here the actual data of the Pivot Cache is stored and it also stores the count of number of records in the cache.

The pivotCacheDefinition part defines each field in the pivotCacheRecords part, including field name and information about the data contained in the field. Pivot Table Cache Definition part-relationship item contains a relationship to the Pivot Table Cache Records part. In the pivotCacheDefinition relationship (pivotCacheDefinition1.xml.rels), we will have the entry as below –

<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">

  <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheRecords" Target="pivotCacheRecords1.xml"/>

</Relationships>

 

The pivotCacheRecords has each data record of the Pivot Cache stored in the r (PivotCache Record) element. In turn this Pivot Cache Record has the data stored as its type like, b for Boolean or d for Date Time or e for Error Value or m for No Value or n for Numeric or s for Character Value or x for Shared Items Index.

 

Example –

<pivotCacheRecords ….. count="6">

  <r>

    <x v="0"/>

    <x v="0"/>

    <x v="0"/>

    <n v="1000"/>

  </r>

  <r>

    <x v="1"/>

    <x v="0"/>

    <x v="1"/>

    <n v="500"/>

  </r>

  <r>

    <x v="2"/>

    <x v="1"/>

    <x v="0"/>

    <n v="1500"/>

  </r>

  <r>

    <x v="3"/>

    <x v="1"/>

    <x v="1"/>

    <n v="1600"/>

  </r>

  <r>

    <x v="4"/>

    <x v="0"/>

    <x v="1"/>

    <n v="800"/>

  </r>

  <r>

    <x v="5"/>

    <x v="2"/>

    <x v="2"/>

    <n v="2000"/>

  </r>

</pivotCacheRecords>