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.x Script Basics and SuiteScript 2.x Script Types.
/**
* @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'], function(workbook, dataset){
var myDataset = dataset.load({
id: 'dataset_7'
});
var theIDExpression = myDataset.getExpressionFromColumn({
alias: 'id'
});
var sort = workbook.createSort({
ascending: false
});
var columnID = workbook.createTableColumn({
datasetColumnAlias: 'id'
});
var columnName = workbook.createTableColumn({
datasetColumnAlias: 'name'
});
var columnDate = workbook.createTableColumn({
datasetColumnAlias: 'date'
});
var columnTotal = myBasicWorkbook.createTableColumn({
datasetColumnAlias: 'total'
});
var tableView = workbook.createTableDefinition({
id: 'view',
name: 'View',
dataset: myDataset,
columns: [columnID, columnName, columnDate, columnTotal]
});
var theDateExpression = dataset.getExpressionFromColumn({
alias: 'date'
});
var rowItem = workbook.createDataDimensionitem({
label: 'A',
expression: theDateExpression
});
var rowDataDimension = workbook.createDataDimension({
items: [rowItem]
});
var rowSection = workbook.createSection({
children: [rowDataDimension]
});
var theTotalExpression = dataset.getExpressionFromColumn({
alias: 'total'
});
var columnItem = workbook.createDataDimensionItem({
label: 'B',
expression: theTotalExpression
}); var columnDataDimension = workbook.createDataDimension({
items: [columnItem]
});
var columnMeasure = workbook.createMeasure({
label: 'M',
expression: theIDExpression,
aggregation: workbook.Aggregation.MAX
});
var columnSection = workbook.createSection({
children: [columnDataDimension, columnMeasure]
});
var constExpr = workbook.createConstant({
constant: 1
});
var anyOfExpr = workbook.createExpression({
functionId: workbook.ExpressionType.AND,
parameters: {
expression: theIDExpression,
set: [constExpr]
}
});
var notExpr = workbook.createExpression({
functionId: workbook.ExpressionType.NOT,
parameters: {
a: anyOfExpr
}
});
var allSubNodesSelector = workbook.createAllSubNodesSelector();
var rowItemSelector = workbook.createDimensionSelector({
dimension: rowDataDimension
});
var columnItemSelector = workbook.createDimensionSelector({
dimension: columnDataDimension
});
var rowSelector = workbook.createPathSelector({
elements: [allSubNodesSelector, rowItemSelector]
});
var columnSelector = workbook.createPathSelector({
elements: [allSubNodesSelector, columnItemSelector]
});
var rowSort = workbook.createDimensionSort({
item:rowItem,
sort:sort
});
var columnSort = workbook.createMeasureSort({
measure: columnMeasure,
sort: sort,
otherAxisSelector: allSubNodesSelector
});
var rowSortDefinition = workbook.createSortDefinition({
sortBys: [rowSort],
selector: rowSelector
});
var columnSortDefinition = workbook.createSortDefinition({
sortBys: [columnSort],
selector: columnSelector
});
var rowAxis = workbook.createPivotAxis({
root: rowSection,
sortDefinitions: [rowSortDefinition]
});
var columnAxis = workbook.createPivotAxis({
root: columnSection,
sortDefinitions: [columnSortDefinition]
});
var limitingFilter = workbook.createLimitingFilter({
row: true,
filteredNodesSelector: rowSelector,
limit: 1,
sortBys: [rowSort]
});
var conditionalFilter = workbook.createConditionalFilter({
row: false,
filteredNodesSelector: rowSelector,
otherAxisSelector: columnSelector,
measure: columnMeasure,
predicate: notExpr
});
var pivot = workbook.createPivotDefinition({
id: 'pivot',
name: 'Pivot',
dataset: myDataset,
rowAxis: rowAxis,
columnAxis: columnAxis,
filterExpressions: [notExpr],
aggregationFilters: [limitingFilter, conditionalFilter]
});
var firstAxis = workbook.createChartAxis({
title: 'First axis'
});
var secondAxis = workbook.createChartAxis({
title: 'Second axis'
});
var category = workbook.createCategory({
axis: firstAxis,
root: rowSection
});
var legend = workbook.createLegend({
axes: [secondAxis],
root: columnSection
});
var aspect = workbook.createAspect({
measure: columnMeasure
});
var series = workbook.createSeries({
aspects: [aspect]
});
var chart = workbook.createChartDefinition({
id: 'chart',
name: 'Chart',
type: workbook.ChartType.AREA,
dataset: myDataset,
category: category,
legend: legend,
series: [series]
});
var myNewWorkbook = workbook.create({
description: 'My new updated workbook',
name: 'Workbook Updated',
tableDefinitions: [tableView],
pivotDefinitions: [pivot],
chartDefinitions: [chart]
});
var workbookList = workbook.list();
log.debug({
title: "MyNewWorkbook",
details: myNewWorkbook
});
});