Full scripts
Note:
The content in this help topic applies to SuiteScript 2.x.
This tutorial is designed to help you create a workbook using a step-by-step approach. You may want to see the full scripts that you will create, so they are listed here.
Dataset
This dataset includes fields related to transactions, transaction lines, and accounting impact. The dataset uses the transaction record as the base record type. It joins this record type with the transaction line record type using the transactionlines field. It also joins the transaction line record type with the transaction accounting line record type using the accountingimpact field. The dataset includes columns from all of these record types, including transaction line ID, transaction name, type, date, and so on.
This dataset is created using the Dataset Builder Plug-in. For more information, see Dataset Builder Plug-in.
/**
* @NApiVersion 2.0
* @NScriptType datasetbuilderplugin
*/
define(['N/dataset'], function (nDataset) {
return Object.freeze({
createDataset: function (context) {
// Create joins with other record types
var transactionlines = nDataset.createJoin({
fieldId: 'transactionlines'
})
var accountingimpact = nDataset.createJoin({
fieldId: 'accountingimpact',
join: transactionlines
})
// Create the dataset and its columns
context.dataset = nDataset.create({
type: 'transaction',
columns: [
nDataset.createColumn({
fieldId: 'id',
join: transactionlines,
alias: 'TranlineID',
label: 'Tranline ID'
}),
nDataset.createColumn({
fieldId: 'trandisplayname',
alias: 'Transaction',
label: 'Transaction'
}),
nDataset.createColumn({
fieldId: 'type',
alias: 'Type',
label: 'Type'
}),
nDataset.createColumn({
fieldId: 'trandate',
alias: 'Date',
label: 'Date'
}),
nDataset.createColumn({
fieldId: 'entity',
alias: 'Entity',
label: 'Entity'
}),
nDataset.createColumn({
fieldId: 'postingperiod',
alias: 'PostingPeriod',
label: 'Posting Period'
}),
nDataset.createColumn({
fieldId: 'posting',
join: accountingimpact,
alias: 'Posting',
label: 'Posting'
}),
nDataset.createColumn({
fieldId: 'account',
join: accountingimpact,
alias: 'Account',
label: 'Account'
}),
nDataset.createColumn({
fieldId: 'credit',
join: accountingimpact,
alias: 'Credit',
label: 'Credit'
}),
nDataset.createColumn({
fieldId: 'debit',
join: accountingimpact,
alias: 'Debit',
label: 'Debit'
})
],
})
}
});
});
Workbook
This workbook loads the dataset defined in the previous section, and it includes a table view and pivot based on the data in the dataset. The workbook demonstrates features such as conditional formatting rules, data dimensions, data measures, and calculated measures.
This workbook is created using the Workbook Builder Plug-in. For more information, see Workbook Builder Plug-in.
/**
* @NApiVersion 2.0
* @NScriptType workbookbuilderplugin
*/
define(['N/workbook', 'N/dataset', 'N/runtime'], function(nWorkbook, nDataset, nRuntime){
return {
createWorkbook: function (context){
// Load the dataset for the workbook
var dataset = nDataset.load({
id: 'customscript_transaction'
})
// Create a helper object for sort directions
var SORT = {
ASCENDING: nWorkbook.createSort({
ascending: true
}),
DESCENDING: nWorkbook.createSort({
ascending: false
})
}
// Create two conditional formatting rules
var yellowRule = nWorkbook.createConditionalFormatRule({
filter: nWorkbook.createTableColumnFilter({
operator: "LESS",
values: [10]
}),
style: nWorkbook.createStyle({
backgroundColor: nWorkbook.createColor({
red: 255,
green: 255,
blue: 0,
})
})
});
var pinkRule = nWorkbook.createConditionalFormatRule({
filter: nWorkbook.createTableColumnFilter({
operator: "GREATER_OR_EQUAL",
values: [0]
}),
style: nWorkbook.createStyle({
backgroundColor: nWorkbook.createColor({
red: 255,
green: 192,
blue: 203
})
})
});
// The color is set on first matching rule and is not overwritten by
// subsequent rules
var conditionalFormat = nWorkbook.createConditionalFormat({rules: [yellowRule, pinkRule]});
// Create a table view
var credit = nWorkbook.createTableColumn({datasetColumnAlias: 'Credit', conditionalFormats: [conditionalFormat]});
var debit = nWorkbook.createTableColumn({datasetColumnAlias: 'Debit', conditionalFormats: [conditionalFormat]});
var tableview = nWorkbook.createTable({id: 'view', name: 'Table', dataset: dataset, columns: [credit, debit]});
// Create data dimensions to be used in a pivot
var allSubNodesSelector = nWorkbook.DescendantOrSelfNodesSelector;
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]
})
// Create data measure definitions to be used in a pivot
var countMeasure = nWorkbook.createDataMeasure({
label: 'Count',
expressions: [dataset.getExpressionFromColumn({
alias: 'TranlineID'
})],
aggregation: 'COUNT_DISTINCT'
});
var sumCredit = nWorkbook.createDataMeasure({
label: 'Sum Credit',
expression: nWorkbook.createExpression({
functionId: workbook.ExpressionType.SIMPLE_CONSOLIDATE,
parameters: {
expression: dataset.getExpressionFromColumn({
alias: 'Credit'
})
}
}),
aggregation: 'SUM'
});
var sumDebit = nWorkbook.createDataMeasure({
label: 'Sum Debit',
expression: nWorkbook.createExpression({
functionId: workbook.ExpressionType.SIMPLE_CONSOLIDATE,
parameters: {
expression: dataset.getExpressionFromColumn({
alias: 'Debit'
})
}
}),
aggregation: 'SUM'
});
// Create a calculated measure to be used in a pivot
var calculatedMeasure = nWorkbook.createCalculatedMeasure({
label: 'Bilance',
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
}
})
}
})
});
var conditionalRowSelector = nWorkbook.DescendantOrSelfNodesSelector;
var conditionalColumnSelector = nWorkbook.DescendantOrSelfNodesSelector;
var conditionalMeasureSelector = nWorkbook.createMeasureSelector({measures: [calculatedMeasure]});
var measureValueSelector = nWorkbook.createMeasureValueSelector({
rowSelector: conditionalRowSelector,
columnSelector: conditionalColumnSelector,
measureSelector: conditionalMeasureSelector
});
// Create a report style
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]
});
// Create pivot axes for the pivot
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
})
]
})
})
]
})
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
})
]
})
})
]
})
// Create the pivot
var pivot = nWorkbook.createPivot({
id: 'pivot',
name: 'Pivot: Transaction types per posting period',
dataset: dataset,
rowAxis: rowAxis,
columnAxis: columnAxis,
reportStyles: [reportStyle]
})
// Create the workbook with the specified table view and pivot
context.workbook = nWorkbook.create({
tables: [tableview],
pivots: [pivot]
})
}
}
})
|
Previous: Prerequisites |
Next: Create the dataset |