Pivot Table Item Slicer

SpreadJS provides a Pivot Table Item Slicer that enables filtering a pivot table with a single click. You can filter an aggregate target by connecting multiple pivot tables using a slicer. It can filter a pivot table's data using manual filters. It offers a user-friendly interface, enabling you to manage the filter state of the row and column fields of your pivot table efficiently.

The PivotTableItemSlicer class provides various slicer-related methods to work with the pivot table slicer.

The following code sample shows how to add a pivot table slicer using the SlicerCollection.add method. The SlicerType should be set to pivotTable.

$(document).ready(function () {
    // initializing Spread
    spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 2 });
    spread.options.allowDynamicArray = true;
    spread.suspendPaint();
    // get sheets
    pivotLayoutSheet = spread.getSheet(0);
    dataSourceSheet = spread.getSheet(1);

    // Hide gridlines
    pivotLayoutSheet.options.gridline = { showVerticalGridline: false, showHorizontalGridline: false };

    // set sheet name
    pivotLayoutSheet.name("PivotLayout");
    dataSourceSheet.name("DataSource");
    // set row count
    dataSourceSheet.setRowCount(245);
    // set datasource
    dataSourceSheet.setArray(0, 0, pivotDB_UseCase);
    // add table to dataSourceSheet
    dataSourceSheet.tables.add('tableSales', 0, 0, 245, 8);

    // initialize pivottable
    var pt = initPivotTable(pivotLayoutSheet);
    pivotLayoutSheet.setColumnCount(200);
    initSlicer(pivotLayoutSheet, pt);

    // auto fit columns in both the sheets
    autoFit(pivotLayoutSheet);
    autoFit(dataSourceSheet);
    spread.resumePaint();
});

function initPivotTable(sheet) {
    myPivotTable = sheet.pivotTables.add("pivotTable", "tableSales", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.dark3);
    myPivotTable.suspendLayout();
    myPivotTable.add("City", "City", GC.Spread.Pivot.PivotTableFieldType.rowField);
    myPivotTable.add("Category", "Category", GC.Spread.Pivot.PivotTableFieldType.rowField);

    myPivotTable.add("OrderDate", "OrderDate", GC.Spread.Pivot.PivotTableFieldType.columnField);
    let groupInfo = {
        originFieldName: "OrderDate",
        dateGroups: [
            {
                by: GC.Pivot.DateGroupType.quarters
            }
        ]
    };
    myPivotTable.group(groupInfo);
    myPivotTable.add("TotalPrice", "TotalPrice", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
    formatValueField(myPivotTable);
    myPivotTable.resumeLayout();
    myPivotTable.autoFitColumn();
    return myPivotTable;
}

function initSlicer(sheet, pt) {
    slicer_City = sheet.slicers.add("slicer_City", pt.name(), "City", GC.Spread.Sheets.Slicers.SlicerStyles.light4(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
    slicer_City.position(new GC.Spread.Sheets.Point(1020, 10));
    slicer_Category = sheet.slicers.add("slicer_Category", pt.name(), "Category", GC.Spread.Sheets.Slicers.SlicerStyles.light1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable)
    slicer_Category.position(new GC.Spread.Sheets.Point(1220, 10));
    slicer_Date = sheet.slicers.add("slicer_Date", pt.name(), "OrderDate", GC.Spread.Sheets.Slicers.SlicerStyles.other2(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
    slicer_Date.position(new GC.Spread.Sheets.Point(1020, 280));
}

Sort Items in Slicer

You can sort items in a pivot table slicer along with filtering. The following options are available in the Slicer settings dialog box:

1. Source Name: Specifies the name of the Slicer

2. Header:

   • Display Header CheckBox: Check if you want to display the name of the slicer on the header.

   • Caption: Name of the slicer you want to display on the header.

3. Sort State: Refers to the check bullets for Ascending/Descending order.

4. No Data Items Show: Refers to options below:

   • Hide items with no data: When true, it does not show the items with no data in the slicer.

   • Show items with no data last: If True, it shows the items with no data last. If false, items are sorted by the sort state.

     
     

     For example, Order dates like <1/1/2013 5:30:00 and >12/30/2014 5:30:00 are sorted by the sort state.

     
     

     
     

     
     

   • Visually indicate items with NoData: If true, it shows the items with no data as grayed out. When false, the items with no data are displayed in the same way as data items.

     
     

     For example: When order dates like <1/1/2013 5:30:00 and >12/30/2014 5:30:00 do not include any data, they are still shown in the list.

     
     

     
     

Default Sort values are "Hide items with no data" and "Show Items with no data last". The slicer items are divided into two parts and each part is sorted in ascending or descending order.

The following code sample defines different sorting or filtering functions.

function setMultiSelectFalseForSlicerCity() {
    // set MultiSelect to True
    slicer_City.multiSelect(true);
}
function setSortStateForSlicerCity() {
    // set sort state to descending
    slicer_City.sortState(GC.Spread.Sheets.SortState.descending);
}

function showNoDataItem() {
    myPivotTable.updateSource();
    // to check this delete City "Jersey"( or any one city from DataSource and click this button- on clicking this button, PT will be updated first)
    slicer_City.showNoDataItems(false);
}

function visuallyNoDataItems() {
    myPivotTable.updateSource();
    slicer_Date.visuallyNoDataItems(false);
}
function showNoDataItemsInLast() {
    slicer_Date.showNoDataItemsInLast(false);
}

Slicer and Pivot Table Connection

You can establish a connection between a slicer and the pivot table using the PivotTableItemSlicer.connectPivotTable method. The connection is controlled by either side as both the slicer and pivot table can connect or disconnect from one another. If the slicer disconnects from the pivot table, its filter action does not affect the pivot table and the same follows in the slicer.

The following code sample shows how to disconnect a connection.

function disconnectSlicerWithPivotTable() {
    // slicer_Date disconnect with PivotTable.
    slicer_Date.disconnectPivotTable(myPivotTable.name());
    slicer_Date.captionName("Disconnected");
}

Custom Item Slicer Styles

SpreadJS provides 8 light and 6 dark predefined themes which can be applied to a pivot table slicer. However, you can manage custom slicer themes by adding, removing, and modifying slicer styles, which can also be applied to your pivot table slicers. You need to just set a style name for the slicer to use these custom styles. To set slicer styles, use the customSlicerThemes property of the GC.Spread.Sheets.WorkBook class.

The CustomSlicerThemesManager class provides the following methods to manage custom item slicer themes.

Additionally, the defaultSlicerTheme method of GC.Spread.Sheets.WorkBook class enables you to manage default slicer styles effectively to ensure a consistent look and feel across all item slicers.

Note:

• Slicer theme names must be unique.

  For example, if you already have a custom item slicer style named "TEST", you cannot create another item slicer style with the same name.

• Slicer style names are case-insensitive.

• When a style name is added to a slicer, it finds the style by the name. If the style name matches an internal style, it will be applied directly. If not, it will search for the corresponding style in the customStyles collection. If found, that style will be applied. Otherwise, the default style will be used.

• The defaultSlicerTheme should be a string representing the style name of the slicer. If a slicer is created without explicitly specifying a style, and if, a style with this default name exists, then the style will be applied to that slicer.

• Custom slicer themes do not support older versions of Excel IO.

The following code sample demonstrates different methods necessary to manage custom slicer themes.

1. Add a new custom slicer theme.

   const spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
   
   // Add a new slicer theme named “custom0”.
   const slicerStyle = spread.customSlicerThemes.add("custom0");
   const styleInfo = new GC.Spread.Sheets.Slicers.SlicerStyleInfo();
   styleInfo.backColor("orange");
   styleInfo.borderBottom(new GC.Spread.Sheets.Slicers.SlicerBorder(2,"solid","green"));
   slicerStyle.wholeSlicerStyle(styleInfo);

2. Get the custom slicer theme.

   const slicerTheme = spread.customSlicerThemes.get("custom0");

3. Apply the custom slicer theme.

   slicer.style("custom0");

4. Modify the custom slicer theme.

   When you make changes to an existing slicer style, it will automatically update all slicers where that style was previously applied.

   // Modify custom slicer theme.
   const slicerStyle = spread.customSlicerThemes.get("custom0");
   slicerStyle.wholeSlicerStyle().backColor("red");
   
   // Update custom slicer theme.
   spread.customSlicerThemes.update("custom0", new GC.Spread.Sheets.Slicers.SlicerStyle());

5. Delete an existing custom slicer theme.

   When you delete a custom slicer style, all slicers that used the deleted style will switch to the default style.

   // Delete custom slicer theme.
   spread.customSlicerThemes.remove("custom0");

You can also add and format slicer styles using the SpreadJS Designer Component by following the steps below:

1. Click on the added slicer to open the SLICER tab to configure the slicer settings.

2. Click the Styles button available in the Slicer Styles group.

   
   

   
   

   
   

3. Click the dropdown arrow and select the New Slicer Style.. option.

   
   

   
   

   
   

4. In the New Slicer Style dialog, enter a new style name in the Name text field, select the element from the Slicer Element section to which you want to apply the styles, and then click Format to configure your desired styles for the custom slicer theme.

   
   

   
   

   
   

5. All the user-created slicer styles are displayed under the Custom section of the slicer styles collection list.

   
   

   
   

   
   

6. Right-click on the custom slicer style and perform actions such as apply, modify, duplicate, delete, and set as default actions, as per your requirement.

   
   

   
   

Note: You cannot modify or delete built-in pivot table slicer styles.