Pivot Axes

Pivot axes are the X and Y axes in a pivot, where you combine elements before creating the pivot. Create pivot axes with the data dimensions and measures you want in your pivot. You can also add sections (as workbook.Section objects) that wrap sets of data dimensions or measures. Pivot axes can have a sort definition to customize how elements are sorted.

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

You can create a pivot axis using workbook.createPivotAxis(options). This method creates a workbook.PivotAxis object. To create a pivot axis, you need to provide the root parameter with the data dimensions and measures. You have two options for providing the root parameter value:

For a single data dimension, use a workbook.DataDimension object directly:

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

For 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
        ]
    })
});

You can also add a sort definition using the sortDefinitions parameter of workbook.createPivotAxis(options). The sort definition can only reference data dimension items added to the pivot axis by the root parameter.

Create a sort definition using 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'll also need a selector for 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