ExcelScript.ShowAsRule interface

Package:

ExcelScript

Remarks

Examples

/**
 * The script changes the display for "Crates Sold at Farm".
 * The field shows each value's difference
 * from the value of the "Lemon" in the same row.
 * If the row has no value for "Lemon", the field shows "#N/A".
 */
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");

  // Get the row hierarchy "Type".
  const typeField = farmPivot.getRowHierarchy("Type").getFields()[0];

  // Change the data hierarchy to show each value as the difference
  // from the value of the "Lemon" in that row.
  const rule: ExcelScript.ShowAsRule = {
    calculation: ExcelScript.ShowAsCalculation.differenceFrom,
    baseField: typeField,
    baseItem: typeField.getPivotItem("Lemon")
  }
  farmSales.setShowAs(rule);

  // Set the name of the field to match the new behavior.
  farmSales.setName("Difference from Lemons of Crates Sold at Farm");
}

Properties

baseField	The PivotField to base the ShowAs calculation on, if applicable according to the ShowAsCalculation type, else null.
baseItem	The item to base the ShowAs calculation on, if applicable according to the ShowAsCalculation type, else null.
calculation	The ShowAs calculation to use for the PivotField. See ExcelScript.ShowAsCalculation for details.

Property Details

baseField

The PivotField to base the ShowAs calculation on, if applicable according to the ShowAsCalculation type, else null.

baseField?: PivotField;

Property Value

ExcelScript.PivotField

baseItem

The item to base the ShowAs calculation on, if applicable according to the ShowAsCalculation type, else null.

baseItem?: PivotItem;

Property Value

ExcelScript.PivotItem

calculation

The ShowAs calculation to use for the PivotField. See ExcelScript.ShowAsCalculation for details.

calculation: ShowAsCalculation;

Property Value

ExcelScript.ShowAsCalculation