SpreadJS 14
Features / Pivot Table / Pivot Table Settings
In This Topic
    Pivot Table Settings
    In This Topic

    You can modify the settings of a pivot table to make it more comprehensible.

    Auto Fit Column

    The autoFitColumn method can be used to automatically fit pivot table columns according to the data. The below GIF demonstrates auto fit columns in a pivot table.

    The following example code shows how to auto fit columns in a pivot table.

    JavaScript
    Copy Code
    // Auto fit columns
    myPivotTable.autoFitColumn();
    

    Calculated Fields

    You can add custom fields into the value field area of a pivot table which calculates values using formulas. You can use constants or refer to other fields of the pivot table in the formula.

    The calculated field can be added using addCalcField method. Additionally, you can fetch and remove calculated field information using getCalcFields and removeCalcField methods respectively.

    In the below image, a calculated field "Tax" is added in a pivot table.

    The following example code shows how to add, fetch and remove a calculated field in a pivot table.

    JavaScript
    Copy Code
    // Add Calculated Field "Tax" which is equal to 0.08 % of UnitPrice field
    // UnitPrice is one of PivotTable's measure field
    myPivotTable.addCalcField("Tax", "=UnitPrice*0.08");
    myPivotTable.add("Tax", "Tax Calculated is 0.08 percent of Unit Price", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
    
    // get calculated field
    calcFieldsInfo = myPivotTable.getCalcFields();
    // add Calculated Field's name in new sheet
    for (var i = 0; i < calcFieldsInfo.length; i++) {
        calcFieldSheet.setValue(i + 1, 0, calcFieldsInfo[i].fieldName);
        calcField = calcFieldsInfo[i].fieldName;
        calcFieldSheet.setValue(i + 1, 1, calcFieldsInfo[i].formula);
    }
    
    // remove calculated field
    myPivotTable.removeCalcField(calcField);
    

    Conditional Rule

    You can apply conditional rules on a pivot area using the addConditionalRule method. Additionally, you can fetch or remove any existing conditional rules using the getConditionalRules and removeConditionalRule methods respectively. The conditional rules work as expected irrespective of the changes to pivot Table layout.

    The following image shows color formatting conditional rule in value cells.

    The following example code shows how to apply conditional rule in a pivot table.

    JavaScript
    Copy Code
    // Add conditional rule
    myPivotTable.addConditionalRule([{ dataOnly: true, references: [{ fieldName: "City", items: ["Seattle"] }] }], new GC.Spread.Sheets.ConditionalFormatting.ScaleRule(GC.Spread.Sheets.ConditionalFormatting.RuleType.threeScaleRule, 1 /* LowestValue */, 0, "lightblue", 0 /* Number */, 500, "yellow", 2 /* HighestValue */, 2000, "green"));
    

    Merge and Center Cells With Labels

    You can merge and center justify the cells with labels using the mergeItem option. This option accepts boolean value and works according to the defined layout of a pivot table as described below:

    Options Compact Form Outline Form Tabular Form
    Merge Only Column header Only Column header Both column and row header
    Center Only Column header Both column and row header Both column and row header

    In the below GIF, the cells with labels are merged and centered using the mergeItem option.

    The following example code shows how to merge and center justify the cells with labels in a pivot table.

    JavaScript
    Copy Code
    // Merge and center labeled cells
    myPivotTable.options.mergeItem = true;
    

    Pivot Table ToolTip

    You can enable tooltips in a pivot table to understand the additional meaning of data. The tooltip data appears when the mouse is hovered over pivot data. However, the tooltips are not displayed if there is no clear information in value field. You can use the showToolTip option to show tooltips in the row/column header area, data area, and subtotal/grandTotal area.

    The below image shows tooltips when the mouse is hovered over pivot data.

    The following example code shows how to enable tooltips in a pivot table.

    JavaScript
    Copy Code
    // Show tooltip in pivot table
    myPivotTable.options.showToolTip = true;
    

    Pivot Table Serialize and Deserialize

    You can serialize and save pivot table data using serialize method and restore the serialized pivot table data to an existing pivot table using the deserialize method. Serialization supports the following pivot table data: Fields, Options, Filter, Sort, Layout, PivotTable Position and Theme.

    The following example code serializes and saves pivot table data, prints the serialization information and restores the data to an existing pivot table.

    JavaScript
    Copy Code
    // Using serialize method to save pivot table data
    serialization = myPivotTable.serialize();
    // Printing serialization info
    info = JSON.stringify(serialization, null, 2);
    console.log(info);
    
    // Using deserialize method to restore data
    myPivotTable.deserialize(serialization);
    

    Show Empty Value As

    SpreadJS provides the following options to choose whether to display any custom value in case an empty value exists in the pivot table data source.

    The below image shows empty values as 'No Sales' in a pivot table.

    The following example code shows how to display a string value in place of empty value.

    JavaScript
    Copy Code
    // Display custom value in empty cell 
    myPivotTable.options.showMissing = true;
    myPivotTable.options.missingCaption = "No Sales";
    

    Pivot Table Show Data As

    You can show the pivot table data as different types of calculation values using showDataAs method. This method accepts field name and IPivotShowDataAsInfo object properties. The value fields can be set to different calculations using PivotShowDataAs enumeration.

    In the below GIF, the pivot table shows running total for "Years" field.

    The following example code shows how to display the running total value for Years.

    JavaScript
    Copy Code
    // Using showDataAs method
    myPivotTable.showDataAs("Sum of quantity", { showDataAs: GC.Pivot.PivotShowDataAs.runTotal, baseFieldName: "Years" });