Pivot axes

Now it is time to assemble all of the elements and create the pivot. A pivot consists of a row axis and a column axis, and you create these axes using workbook.createPivotAxis(options). You must provide a root element for the axis, and this element contains a hierarchical set of data dimensions and measures for that axis. You must also provide a sort definition for the axis, which defines how items are sorted on the axis.

The row axis of our pivot contains a data dimension that applies to the Date column, meaning that the top level of this axis in the pivot will be organized by date. As a child of this dimension, we include the Posting Period data dimension that we created earlier. We also include the countMeasure and calculatedMeasure objects that we created, and everything is wrapped up in a workbook.Section object. For the sort definition, we specify an ascending sort based on the Posting Period data dimension item, and we use workbook.createPathSelector(options) and workbook.createDimensionSelector(options) to select the correct data dimension.

          var rowAxis = nWorkbook.createPivotAxis({
    root: nWorkbook.createSection({
        children: [
            nWorkbook.createDataDimension({
                items: [
                    nWorkbook.createDataDimensionItem({
                        label: 'Date (Year)',
                        expression: nWorkbook.createExpression({
                            functionId: workbook.ExpressionType.DATE_TIME_PROPERTY,
                            parameters: {
                                dateTime: dataset.getExpressionFromColumn({
                                    alias: 'Date'
                                }),
                                property: 'YEAR',
                                hierarchy: 'MONTH_BASED'
                            }
                        })
                    })
                ],
                children: [postingPeriodDataDimension]
            }),
            countMeasure,
            calculatedMeasure
        ]
    }),
    sortDefinitions: [
        nWorkbook.createSortDefinition({
            sortBys: [
                nWorkbook.createSortByDataDimensionItem({
                    item: postingPeriodItem,
                    sort: SORT.ASCENDING
                })
            ],
            selector: nWorkbook.createPathSelector({
                elements: [
                    allSubNodesSelector,
                    nWorkbook.createDimensionSelector({
                        dimension: postingPeriodDataDimension
                    })
                ]
            })
        })
    ]
}) 

        

We use a similar approach to create the column axis of the pivot. This axis contains the Type data dimension item, and we specify that we want to include a total line as the first line on the axis. For the sort definition, we specify an ascending sort based on the Type data dimension item.

          var columnAxis = nWorkbook.createPivotAxis({
    root: nWorkbook.createSection({
        children: [typeDataDimension],
        totalLine: nWorkbook.TotalLine.FIRST_LINE
    }),
    sortDefinitions: [
        nWorkbook.createSortDefinition({
            sortBys: [
                nWorkbook.createSortByDataDimensionItem({
                    item: typeItem,
                    sort: SORT.ASCENDING
                })
            ],
            selector: nWorkbook.createPathSelector({
                elements: [
                    allSubNodesSelector,
                    nWorkbook.createDimensionSelector({
                        dimension: typeDataDimension
                    })
                ]
            })
        })
    ]
}) 

        

Related Topics

• Tutorial: Creating a Workbook Using the Workbook API
• Final pivot creation

General Notices