Pivot Axes

Note:

The content in this help topic applies to SuiteScript 2.x.

Pivot axes represent the X and Y axes in a pivot and are where you combine other pivot elements before creating the pivot itself. You create pivot axes that include the data dimensions and measures that you want to include in the pivot. You can also include sections (as workbook.Section objects) that wrap sets of data dimensions or measures. Pivot axes can include a sort definition, which specifies how the elements on the axes are sorted.

For more information about pivots in SuiteAnalytics Workbook, see Workbook Pivot Tables.

To create a pivot axis, use workbook.createPivotAxis(options). This method creates a workbook.PivotAxis object. When you use this method, you must provide a value for the root parameter, which includes the data dimensions and measures to use for the pivot axis. You can provide this value in two ways:

If the pivot axis includes a single data dimension, use a workbook.DataDimension object directly:

              var myPivotAxis = workbook.createPivotAxis({
    root: myDataDimension
});

If the pivot axis includes multiple data dimensions or measures, create a section using workbook.createSection(options) and add the data dimensions and measures to the section. You can use the children parameter of workbook.createSection(options) to create a hierarchy of data dimensions or measures to display on the axis.

              var myPivotAxis = workbook.createPivotAxis({
    root: workbook.createSection({
        children: [
            workbook.createDataDimension({
                items: [
                    workbook.createDataDimensionItem({
                        label: 'My Child DD Item',
                        expression: workbook.createExpression({
                            functionId: 'DATE_TIME_PROPERTY',
                            parameters: {
                                dateTime: myDataset.getExpressionFromColumn({
                                    alias: 'Date'
                                }),
                                property: 'YEAR',
                                hierarchy: 'MONTH_BASED'
                            }
                        })
                    })
                ],
                children: [myDataSubDimension]
            }),
            myCountMeasure,
            myCalculatedMeasure
        ]
    })
});

Optionally, you can provide a sort definition using the sortDefinitions parameter of workbook.createPivotAxis(options). The sort definition must reference only the data dimension items that have been added to the pivot axis using its root parameter.

To create a sort definition, use workbook.createSortDefinition(options). You can use workbook.createSortByDataDimensionItem(options) to create a sort that applies to a specific data dimension, then add that sort to the sort definition using the sortBys parameter. You must also provide a selector that applies to the data dimension, which you can create using workbook.createPathSelector(options).

          var allSubNodesSelector = workbook.DescendantOrSelfNodesSelector;

var myPivotAxis = workbook.createPivotAxis({
    root: workbook.createSection({
        children: [myDataDimension],
        totalLine: workbook.TotalLine.FIRST_LINE
    }),
    sortDefinitions: [
        workbook.createSortDefinition({
            sortBys: [
                workbook.createSortByDataDimensionItem({
                    item: myDataDimensionItem,
                    sort: workbook.createSort({
                        ascending: true
                    })
                })
            ],
            selector: workbook.createPathSelector({
                elements: [
                    allSubNodesSelector,
                    workbook.createDimensionSelector({
                        dimension: myDataDimension          // Note that this is the same data dimension
                                                            // used in the root parameter of createPivotAxis()
                    })
                ]
            })
        })
    ]
});

You can also sort by measures using workbook.createSortByMeasure(options).

Related Topics