N/workbook Module Script Samples
The following script sample demonstrates how to use the features of the N/workbook module.
Create Datasets, Dataset Links, and a Workbook with a Pivot and Run the Workbook
The following sample creates two datasets, links them, and creates a workbook with a pivot based on the data in the datasets.
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 simple workbook that contains a simple table
require(['N/workbook', 'N/dataset', 'N/datasetLink'], function(nWorkbook, nDataset, datasetLink) {
var join = nDataset.createJoin({
fieldId: "budgetmachine"
});
var period = nDataset.createColumn({
join: join,
fieldId: "period",
alias: "budgetmachineperiod",
label: "Accounting Period"
});
var department = nDataset.createColumn({
fieldId: "department",
alias: "department",
label: "Department"
});
var total = nDataset.createColumn({
fieldId: "total",
alias: "total",
label: "Amount (Total)"
});
var budget = nDataset.create({
type: 'budgets',
columns: [period, department, total]
});
var postingperiod = nDataset.createColumn({
fieldId: "postingperiod",
alias: "postingperiod",
label: "Posting Period"
});
var amount = nDataset.createColumn({
fieldId: "amount",
alias: "amount",
label: "Amount"
});
var sales = nDataset.create({
type: 'salesinvoiced',
columns: [postingperiod, department, amount],
});
var budgetmachineperiod = budget.getExpressionFromColumn({
alias:"budgetmachineperiod"
});
var postingperiodExpression = sales.getExpressionFromColumn({
alias:"postingperiod"
});
var link = datasetLink.create({
datasets: [budget, sales],
expressions: [[budgetmachineperiod, postingperiodExpression]],
id: "link"
});
var postingPeriodItem = nWorkbook.createDataDimensionItem({
expression: postingperiodExpression
});
var postingPeriodDimension = nWorkbook.createDataDimension({
items: [postingPeriodItem]
});
var rowSection = nWorkbook.createSection({
children: [postingPeriodDimension]
});
var departmentItem = nWorkbook.createDataDimensionItem({
expression: budget.getExpressionFromColumn({
alias: "department"
})
});
var departmentDimension = nWorkbook.createDataDimension({
items: [departmentItem]
});
var sumTotal = nWorkbook.createDataMeasure({
label: 'Sum Total',
expression: budget.getExpressionFromColumn({
alias: 'total'
}),
aggregation: 'SUM'
});
var sumAmountNet = nWorkbook.createDataMeasure({
label: 'Sum Amount',
expression: sales.getExpressionFromColumn({
alias: 'amount'
}),
aggregation: 'SUM'
});
var columnSection = nWorkbook.createSection({
children: [departmentDimension, sumTotal, sumAmountNet]
});
var pivot = nWorkbook.createPivot({
id: "pivot",
rowAxis: nWorkbook.createPivotAxis({
root: rowSection
}),
columnAxis: nWorkbook.createPivotAxis({
root: columnSection
}),
name: "Pivot",
datasetLink: link
});
var wb = nWorkbook.create({
pivots: [pivot]
});
wb.runPivot.promise("pivot").then(function(intersections){
for (var i in intersections)
{
var intersection = intersections[i];
if (intersection.row.itemValues) //skip header
{
console.log("Period: " + intersection.row.itemValues[0].value.name);
console.log(intersection.column.section.children[1].label + ":");
console.log(intersection.measureValues[0] ? intersection.measureValues[0].value.amount : 0);
console.log(intersection.column.section.children[2].label + ":");
console.log(intersection.measureValues[1] ? intersection.measureValues[1].value.amount : 0);
}
}
})
});
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
});
});