Create a Comprehensive Workbook
The following sample creates a workbook that includes a chart, a table, and a pivot. This sample uses a dataset that is not included in your account, so you will need to change the dataset id to a valid value from your account. This dataset needs to include columns for 'id', 'name', 'date', and 'total'.
Note:
This sample script uses the require function so you can copy it into the SuiteScript Debugger and test it. You must use the define function in an entry point script (a script you attach to a script record and deploy). For more information, see SuiteScript 2.1 Script Basics and SuiteScript 2.1 Script Types.
/**
* @NApiVersion 2.1
*/
// The following script creates a workbook that includes a chart, a table, and a pivot. This sample uses a dataset that is not included in your account, so you will need to change the dataset id to a valid value from your account. This dataset needs to include columns for 'id', 'name', 'date', and 'total'.
/**
* @NApiVersion 2.x
*/
// The following script creates a workbook that includes a chart, a table, and a pivot. This sample uses a dataset that is not included in your account, so you will need to change the dataset id to a valid value from your account. This dataset needs to include columns for 'id', 'name', 'date', and 'total'.
require(['N/workbook', 'N/dataset', 'N/log'], (workbook, dataset, log) => {
const createWorkbook = (datasetId = 'dataset_7') => {
const myDataset = dataset.load({
id: datasetId
});
const theIDExpression = myDataset.getExpressionFromColumn({
alias: 'id'
});
const sort = workbook.createSort({
ascending: false
});
const columnID = workbook.createTableColumn({
datasetColumnAlias: 'id'
});
const columnName = workbook.createTableColumn({
datasetColumnAlias: 'name'
});
const columnDate = workbook.createTableColumn({
datasetColumnAlias: 'date'
});
const columnTotal = workbook.createTableColumn({
datasetColumnAlias: 'total'
});
const tableView = workbook.createTable({
id: 'view',
name: 'View',
dataset: myDataset,
columns: [columnID, columnName, columnDate, columnTotal]
});
const theDateExpression = myDataset.getExpressionFromColumn({
alias: 'date'
});
const rowItem = workbook.createDataDimensionItem({
label: 'A',
expression: theDateExpression
});
const rowDataDimension = workbook.createDataDimension({
items: [rowItem]
});
const rowSection = workbook.createSection({
children: [rowDataDimension]
});
const theTotalExpression = myDataset.getExpressionFromColumn({
alias: 'total'
});
const columnItem = workbook.createDataDimensionItem({
label: 'B',
expression: theTotalExpression
});
const columnDataDimension = workbook.createDataDimension({
items: [columnItem]
});
const columnMeasure = workbook.createDataMeasure({
label: 'M',
expression: theIDExpression,
aggregation: workbook.Aggregation.MAX
});
const columnSection = workbook.createSection({
children: [columnDataDimension, columnMeasure]
});
const constExpr = workbook.createConstant({
constant: 1
});
const anyOfExpr = workbook.createExpression({
functionId: workbook.ExpressionType.ANY_OF,
parameters: {
expression: theIDExpression,
set: [constExpr]
}
});
const notExpr = workbook.createExpression({
functionId: workbook.ExpressionType.NOT,
parameters: {
expression: anyOfExpr
}
});
const rowItemSelector = workbook.createDimensionSelector({
dimension: rowDataDimension
});
const columnItemSelector = workbook.createDimensionSelector({
dimension: columnDataDimension
});
const rowSelector = workbook.createPathSelector({
elements: [rowItemSelector]
});
const columnSelector = workbook.createPathSelector({
elements: [columnItemSelector]
});
const rowSort = workbook.createSortByDataDimensionItem({
item: rowItem,
sort: sort
});
const columnSort = workbook.createSortByMeasure({
measure: columnMeasure,
sort: sort,
selector: columnSelector,
otherAxisSelector: rowSelector
});
const rowSortDefinition = workbook.createSortDefinition({
sortBys: [rowSort],
selector: rowSelector
});
const columnSortDefinition = workbook.createSortDefinition({
sortBys: [columnSort],
selector: columnSelector
});
const rowAxis = workbook.createPivotAxis({
root: rowSection,
sortDefinitions: [rowSortDefinition]
});
const columnAxis = workbook.createPivotAxis({
root: columnSection,
sortDefinitions: [columnSortDefinition]
});
const limitingFilter = workbook.createLimitingFilter({
row: true,
filteredNodesSelector: rowSelector,
limit: 1,
sortBys: [rowSort]
});
const conditionalFilter = workbook.createConditionalFilter({
row: false,
filteredNodesSelector: columnSelector,
otherAxisSelector: rowSelector,
measure: columnMeasure,
predicate: notExpr
});
const pivot = workbook.createPivot({
id: 'pivot',
name: 'Pivot',
dataset: myDataset,
rowAxis: rowAxis,
columnAxis: columnAxis,
filterExpressions: [notExpr],
aggregationFilters: [limitingFilter, conditionalFilter]
});
const firstAxis = workbook.createChartAxis({
title: 'First axis'
});
const secondAxis = workbook.createChartAxis({
title: 'Second axis'
});
const category = workbook.createCategory({
axis: firstAxis,
root: rowSection
});
const legend = workbook.createLegend({
axes: [secondAxis],
root: columnSection
});
const aspect = workbook.createAspect({
measure: columnMeasure
});
const series = workbook.createSeries({
aspects: [aspect]
});
const chart = workbook.createChart({
id: 'chart',
name: 'Chart',
type: workbook.ChartType.AREA,
dataset: myDataset,
category: category,
legend: legend,
series: [series]
});
const myNewWorkbook = workbook.create({
description: 'My new updated workbook',
name: 'Workbook Updated',
tables: [tableView],
pivots: [pivot],
charts: [chart]
});
log.debug({
title: "MyNewWorkbook",
details: myNewWorkbook
});
return myNewWorkbook;
};
return { createWorkbook };
});