Create a pivot

Note:

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

In this step, you’ll create a pivot (also known as a pivot table) based on the data in the dataset. Pivots let you analyze different subsets of your data using more advanced features than are included for table views. In a pivot, you can create data dimensions for the data you want to analyze, add data measures and calculated measures, and configure the axes of the pivot to display the information you want. The following sections describe different aspects of creating a pivot.

Data dimensions

You can use data dimensions to define the data you want to display and analyze in the pivot. You can create data dimensions based on columns in the dataset, then specify the data dimensions when you define the axes in the pivot.

In this tutorial, we are interested in the data from two columns: Type and Posting Period. We use workbook.createDataDimensionItem(options) to create a data dimension item for each column. This method accepts an expression that represents the column from the dataset. To obtain this expression, use Dataset.getExpressionFromColumn(options) and provide the alias of the column.

After creating the data dimension items, we create data dimensions for each item. A data dimension can include multiple data dimension items, but in our case, we have only one item per dimension. Use workbook.createDataDimension(options) to create each data dimension.

            var typeItem = nWorkbook.createDataDimensionItem({
    label: 'Type',
    expression: dataset.getExpressionFromColumn({
        alias: 'Type'
    })
})

var postingPeriodItem = nWorkbook.createDataDimensionItem({
    label: 'Posting Period',
    expression: dataset.getExpressionFromColumn({
        alias: 'PostingPeriod'
    })
})

var typeDataDimension = nWorkbook.createDataDimension({
    items: [typeItem]
})

var postingPeriodDataDimension = nWorkbook.createDataDimension({
    items: [postingPeriodItem]
})

Data measures and calculated measures

Measures let you perform simple operations on the data in a column and display the results in the pivot. You can use two types of measures:

Data measure – A data measure represents an operation on the data in a column or set of columns. You can use the operations defined in the workbook.Aggregation enum to aggregate the column data in different ways. For example, you can use the workbook.Aggregation.COUNT_DISTINCT value to count the number of distinct values that appear in a column.
Calculated measure – A calculated measure represents a value that is calculated based on other column values or data measures. For example, you can calculate the difference between aggregated values in two columns.

In this tutorial, you will create three data measures (using workbook.createDataMeasure(options)) and one calculated measure (using workbook.createCalculatedMeasure(options)). The first data measure counts the number of distinct values in the Tranline ID column in the dataset. Similar to creating data dimension items, you must provide an expression that represents the dataset column to create the measure for.

            var countMeasure = nWorkbook.createDataMeasure({
    label: 'Count',
    expressions: [dataset.getExpressionFromColumn({
        alias: 'TranlineID'
    })],
    aggregation: 'COUNT_DISTINCT'
});

The second data measure sums the values in the Credit column in the dataset. Again, we need to provide an expression that represents the column, but we use workbook.createExpression(options) to create a custom expression (instead of obtaining one from a column using Dataset.getExpressionFromColumn(options)). When you use this method to create an expression, you can specify a function that represents the operation used in the expression using the values in workbook.ExpressionType. Each function accepts different parameters.

In our case, we use the SIMPLE_CONSOLIDATE function and provide an expression representing the column to consolidate. This function consolidates a currency expression based on the user’s subsidiary and current accounting period.

            var sumCredit = nWorkbook.createDataMeasure({
    label: 'Sum Credit',
    expression: nWorkbook.createExpression({
        functionId: workbook.ExpressionType.SIMPLE_CONSOLIDATE,
        parameters: {
            expression: dataset.getExpressionFromColumn({
                alias: 'Credit'
            })
        }
    }),
    aggregation: 'SUM'
});

The third data measure sums the values in the Debit column in the dataset. We use the same approach that we used for the Credit column.

            var sumDebit = nWorkbook.createDataMeasure({
    label: 'Sum Debit',
    expression: nWorkbook.createExpression({
        functionId: workbook.ExpressionType.SIMPLE_CONSOLIDATE,
        parameters: {
            expression: dataset.getExpressionFromColumn({
                alias: 'Debit'
            })
        }
    }),
    aggregation: 'SUM'
});

Now we can create our calculated measure. This measure calculates the difference between the Sum Credit and Sum Debit data measures that we previoulsy created. It uses the workbook.ExpressionType.MEASURE_VALUE function to obtain the values of each data measure, then it uses the workbook.ExpressionType.MINUS function to calculate the difference.

            var calculatedMeasure = nWorkbook.createCalculatedMeasure({
    label: 'Balance',
    expression: nWorkbook.createExpression({
        functionId: workbook.ExpressionType.MINUS,
        parameters: {
            operand1: nWorkbook.createExpression({
                functionId: workbook.ExpressionType.MEASURE_VALUE,
                parameters: {
                    measure: sumCredit
                }
            }),
            operand2: nWorkbook.createExpression({
                functionId: workbook.ExpressionType.MEASURE_VALUE,
                parameters: {
                    measure: sumDebit
                }
            })
        }
    })
});

Selectors

Selectors are objects that select elements in a pivot for different purposes, such as applying style rules and configuring the pivot axes. Pivots are stored as hierarchical tree structures, and selectors take care of selecting the correct row, column, and measure when needed.

In the next section, we will create a report style for the pivot. This report style changes the formatting of cells in the pivot, similar to the conditional formatting rules we created for the table view. A report style must be supported by a measure value selector, which selects the cells to apply the style to based on a measure. We create this selector using workbook.createMeasureValueSelector(options). This method accepts a row selector, column selector, and measure selector. The row selector and column selectors are workbook.DescendantorSelfNodesSelector objects, which are generic selectors that work in many situations. The measure selector must be created based on the measure to select (our calculated measure above) using workbook.createMeasureValueSelector(options).

We also create another workbook.DescendantorSelfNodesSelector, which we will use when we create the pivot axes in a later section.

            var conditionalRowSelector = nWorkbook.DescendantOrSelfNodesSelector;
var conditionalColumnSelector = nWorkbook.DescendantOrSelfNodesSelector;
var conditionalMeasureSelector = nWorkbook.createMeasureSelector({
    measures: [calculatedMeasure]
});

var measureValueSelector = nWorkbook.createMeasureValueSelector({
    rowSelector: conditionalRowSelector,
    columnSelector: conditionalColumnSelector,
    measureSelector: conditionalMeasureSelector
});

var allSubNodesSelector = nWorkbook.DescendantOrSelfNodesSelector;

Report style

Now that we have the required selectors, we can create the report style. This report style uses expressions to determine whether the calculated measure value (Sum Credit minus Sum Debit) in a cell is greater than or equal to 1 US dollar (USD). If this condition is true, the cell color is changed to pink.

Use workbook.createReportStyleRule(options) to create a report style rule, then use workbook.createReportStyle(options) to create the report style based on the rules you provide. The workbook.createReportStyle(options) method also accepts a selector, and we provide the measure value selector we created in the previous section.

            var rule = nWorkbook.createReportStyleRule({
    expression: nWorkbook.createExpression({
        functionId: workbook.ExpressionType.COMPARE,
        parameters: {
            comparisonType: "GREATER_OR_EQUAL",
            operand1: nWorkbook.createExpression({
                functionId: workbook.ExpressionType.MEASURE_VALUE,
                parameters: {
                    measure: calculatedMeasure
                }
            }),
            operand2: nWorkbook.createConstant(nWorkbook.createCurrency({
                amount: 1,
                id: "USD"})
            )
        }
    }),
    style: nWorkbook.createStyle({
        backgroundColor: nWorkbook.createColor({
            red: 255,
            green: 192,
            blue: 203
        })
    })
});

var reportStyle = nWorkbook.createReportStyle({
    selectors: [measureValueSelector],
    rules: [rule]
});

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

Final pivot

Finally, we use workbook.createPivot(options) to create the pivot, and we use workbook.create(options) to create the entire workbook, including the table view and pivot.

            var pivot = nWorkbook.createPivot({
    id: 'pivot',
    name: 'Pivot: Transaction types per posting period',
    dataset: dataset,
    rowAxis: rowAxis,
    columnAxis: columnAxis,
    reportStyles: [reportStyle]
})

context.workbook = nWorkbook.create({
    tables: [tableview],
    pivots: [pivot]
})

At this point, your workbook script file is complete and should look similar to the full script in the Workbook section of Full scripts.

You’re done! Now you can use the Dataset Builder Plug-in and Workbook Builder Plug-in to upload your script files to NetSuite, and you can view the complete dataset and workbook in the Analytics area.

Previous: Create a table view

Data dimensions

Data measures and calculated measures

Selectors

Report style

Pivot axes

Final pivot

Related Topics