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

    SpreadJS allows you to choose a pivot table layout and apply it using the PivotTableLayoutType enumeration.

    Compact Form

    The compact form is the default report layout of a pivot table which displays each Row label in a separate row, but in same column. This layout, as the name suggests, presents data in a compact form by reducing the pivot table width. The following image depicts the compact form layout of a pivot table.

    Compact Form layout of the Pivot table

    The following table explains the behavior and positioning of Row Labels, Row Fields and Subtotals while using compact form layout:

    Row Labels Row Fields Subtotals
    Row Field Label is always above the inner fields labels. All the Row Fields are in a single column. Subtotals can be shown at the Top or Bottom of each group.
    To understand the fields easily, each Row Label is slightly indented, from the above field.

    When you set Subtotals, the row fields are altered as explained below:

    • Top: Subtotals are added to the existing row against row label item
    • Bottom: Subtotals are added to a new row at the bottom
    Row Labels cannot be repeated. For Column Fields, Subtotals are always shown at the Bottom.
    You can change the indentation of the fields in this report layout only. The Row Label remains at the top, even if Subtotals are at the bottom.

    The following example code sets the compact form layout for the pivot table.

    Javascript
    Copy Code
    myPivotTable.layoutType(GC.Spread.Pivot.PivotTableLayoutType.compact);
    

    You can customize the indentation level of row fields using rowLabelIndent option in compact form layout.

    Compact Form layout of the Pivot table

    The following example code sets the indentation in compact form of a pivot table.

    Javascript
    Copy Code
    myPivotTable.options.rowLabelIndent = 12;
    

    Outline Form

    The outline form layout shows each Row Field in a separate column. This layout is useful when you want to show all the field names as heading labels and are not concerned about the width of the pivot table. The following image depicts the outline form layout of a pivot table.

    Outline Form layout of the Pivot table

    The following table explains the behavior and positioning of Row Labels, Row Fields and Subtotals while using the outline form layout:

    Row Labels Row Fields Subtotals
    Each Row Label is defined in a separate row. All the Row Fields are in a separate column. Subtotals can be shown at the Top or Bottom of each group.
    Row Field Label is always above the inner fields labels. Each Row Field shows its name in the column heading.

    When you set Subtotals, the row fields are altered as explained below:

    • Top: Subtotals are added to the existing row against row label item
    • Bottom: Subtotals are added to a new row at the bottom
    Row Labels can be repeated. For Column Fields, Subtotals are always shown at the Bottom.

    The following example code sets the outline form layout for a pivot table.

    Javascript
    Copy Code
    myPivotTable.layoutType(GC.Spread.Pivot.PivotTableLayoutType.outline);
    

    Tabular Form

    The tabular form layout is similar to the outline form layout except the difference that row label for the outer field is on the same row as the first label for the related inner field. Hence, it reduces the number of rows in the pivot table. The following image depicts the tabular form layout of the pivot table.

    Tabular Form layout of the Pivot table

    The following table explains the behavior and positioning of Row Labels, Row Fields and Subtotals while using tabular form layout:

    Row Labels Row Fields Subtotals
    Row Labels for the outer fields are on the same row as the first label for the related inner fields. All the Row Fields are in a separate column. Subtotals can only be shown at the Bottom of each group.
    Row Labels can be repeated. Each Row Field shows its name in the column heading.

    When you set Subtotals at bottom, a new row is added at the bottom.

    For Column Fields, Subtotals are always shown at the Bottom.

    The following example code sets the tabular form layout for the pivot table.

    Javascript
    Copy Code
    myPivotTable.layoutType(GC.Spread.Pivot.PivotTableLayoutType.tabular);
    

    Additional Settings

    Set Subtotals and GrandTotals

    You can set the position of subtotals and grandtotals using the subtotalsPosition and grandTotalPosition options respectively. The subtotalsPosition can be set to none, top or bottom values (as applicable to the layouts mentioned above) whereas the grandTotalPosition can be set to all values (such as none, both, col or row) for all layouts. By default, the subtotalsPosition option is set to “bottom” and the grandTotalPosition option is set to “both”.

    The following example code sets the position of subtotals and grandtotals in the pivot table.

    Javascript
    Copy Code
    // Set SubtotalsPosition to none
    myPivotTable.options.subtotalsPosition = GC.Spread.Pivot.SubtotalsPosition.none;
            
    // Set grandTotalPosition to col
    myPivotTable.options.grandTotalPosition = GC.Spread.Pivot.GrandTotalPosition.col;
    

    Set Repeat Label Items

    For outline and tabular form layouts, you can enable repeating item labels in the outer row fields using the fillDownLabels option.

    The following sample code sets the repeat label items to true in the tabular form layout.

    Javascript
    Copy Code
    // Set repeat label items
    myPivotTable.options.fillDownLabels = true;
    
    Note: This option cannot be applied to Compact form.

    Set Blank Rows

    You can add a blank row after each item using the insertBlankLineAfterEachItem option that helps to understand the data easily.

    The following sample code sets the blank rows after each item in the pivot table.

    Javascript
    Copy Code
    // Set blank rows with layout
    myPivotTable.options.insertBlankLineAfterEachItem = true;
    

    The following image shows the tabular form layout of the pivot table with all the additional settings as explained above.

    Pivot Table layout