ExcelScript.DataPivotHierarchy interface

Package:

ExcelScript

Represents the Excel DataPivotHierarchy.

Remarks

Examples

/**
 *  This sample sorts the rows of a PivotTable.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get an existing PivotTable.
  const pivotTable = workbook.getPivotTable("Farm Sales");

  // Get the data hierarchy to use as the basis of the sort.
  const valueFieldToSortOn: ExcelScript.DataPivotHierarchy = pivotTable.getDataHierarchy("Sum of Crates Sold Wholesale");

  // Get the row to sort.
  const rowToSort: ExcelScript.RowColumnPivotHierarchy = pivotTable.getRowHierarchy("Farm");

  // Sort the "Farm" row's only field by the values in "Sum of Crates Sold Wholesale".
  rowToSort.getFields()[0].sortByValues(ExcelScript.SortBy.descending, valueFieldToSortOn);
}

Methods

getField()	Returns the PivotFields associated with the DataPivotHierarchy.
getId()	ID of the DataPivotHierarchy.
getName()	Name of the DataPivotHierarchy.
getNumberFormat()	Number format of the DataPivotHierarchy.
getPosition()	Position of the DataPivotHierarchy.
getShowAs()	Specifies if the data should be shown as a specific summary calculation.
getSummarizeBy()	Specifies if all items of the DataPivotHierarchy are shown.
setName(name)	Name of the DataPivotHierarchy.
setNumberFormat(numberFormat)	Number format of the DataPivotHierarchy.
setPosition(position)	Position of the DataPivotHierarchy.
setShowAs(showAs)	Specifies if the data should be shown as a specific summary calculation.
setSummarizeBy(summarizeBy)	Specifies if all items of the DataPivotHierarchy are shown.
setToDefault()	Reset the DataPivotHierarchy back to its default values.

Method Details

getField()

Returns the PivotFields associated with the DataPivotHierarchy.

getField(): PivotField;

Returns

ExcelScript.PivotField

getId()

ID of the DataPivotHierarchy.

getId(): string;

Returns

string

getName()

Name of the DataPivotHierarchy.

getName(): string;

Returns

string

getNumberFormat()

Number format of the DataPivotHierarchy.

getNumberFormat(): string;

Returns

string

getPosition()

Position of the DataPivotHierarchy.

getPosition(): number;

Returns

number

getShowAs()

Specifies if the data should be shown as a specific summary calculation.

getShowAs(): ShowAsRule;

Returns

ExcelScript.ShowAsRule

getSummarizeBy()

Specifies if all items of the DataPivotHierarchy are shown.

getSummarizeBy(): AggregationFunction;

Returns

ExcelScript.AggregationFunction

setName(name)

Name of the DataPivotHierarchy.

setName(name: string): void;

Parameters

name

string

Returns

void

setNumberFormat(numberFormat)

Number format of the DataPivotHierarchy.

setNumberFormat(numberFormat: string): void;

Parameters

numberFormat

string

Returns

void

setPosition(position)

Position of the DataPivotHierarchy.

setPosition(position: number): void;

Parameters

position

number

Returns

void

setShowAs(showAs)

Specifies if the data should be shown as a specific summary calculation.

setShowAs(showAs: ShowAsRule): void;

Parameters

showAs

ExcelScript.ShowAsRule

Returns

void

Examples

/**
 * The script changes the display for "Crates Sold at Farm".
 * It shows the percentage of the grand total, 
 * instead of the default sum.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the PivotTable named "Farm Pivot".
  const farmPivot = workbook.getPivotTable("Farm Pivot");

  // Get the data hierarchy "Sum of Crates Sold at Farm".
  const farmSales = farmPivot.getDataHierarchy("Sum of Crates Sold at Farm");

  // Show the data as a percentage of the grand total.
  farmSales.setShowAs({
    calculation: ExcelScript.ShowAsCalculation.percentOfGrandTotal
  });
}

setSummarizeBy(summarizeBy)

Specifies if all items of the DataPivotHierarchy are shown.

setSummarizeBy(summarizeBy: AggregationFunction): void;

Parameters

summarizeBy

ExcelScript.AggregationFunction

Returns

void

Examples

/**
 * This script changes how the data in a PivotTable is aggregated.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the first PivotTable in the workbook.
  const pivotTable = workbook.getPivotTables()[0];
  
  // Set the first data hierarchy to summarize with an average value, instead of a sum.
  const dataHierarchy = pivotTable.getDataHierarchies()[0];
  dataHierarchy.setSummarizeBy(ExcelScript.AggregationFunction.average);
}

setToDefault()

Reset the DataPivotHierarchy back to its default values.

setToDefault(): void;

Returns

void