With version 2021.2 SuiteAnalytics brought an option of using fields from two dataset in one Workbook table, pivot table or a chart. Because the connection (linking, joining..) needs to be defined via SuiteScript to appear in Workbook UI, I thought an example can be handy for your experiments.
Before you start coding, make sure to do the following:
- Enable the Client SuiteScript and Server SuiteScript features at Setup > Company > Enable Features > SuiteCloud.
- Register SuiteScript plug-ins for SuiteAnalytics to display defined datasets and workbooks in the UI. For more information, see:
- Take a look at the documentation for the three main SuiteScript modules:
- Use the SuiteScript Debugger at Customization > Scripting > Script Debugger.
- Use table views and pivots for further SuiteScript processing.
- Remember that you can't export existing datasets or workbooks from the UI to a SuiteScript definition.
Budget vs Actual report
1) Create an actual SuiteScript "Sales" dataset
/**
@NApiVersion 2.0
@NScriptType datasetbuilderplugin */
define(['N/dataset'], function(dataset) { return Object.freeze({ createDataset: function(context) {
var postingperiod = dataset.createColumn({
fieldId: "postingperiod",
alias: "postingperiod",
label: "Posting Period"
});
var department = dataset.createColumn({
fieldId: "department",
alias: "department",
label: "Department"
});
var amount = dataset.createColumn({
fieldId: "amount",
alias: "amount",
label: "Amount"
});
var ds = dataset.create({
type: 'salesinvoiced',
columns: [postingperiod, department, amount],
});
context.dataset = ds;
}
}); });
2) Create SuiteScript "budget" dataset
/**
@NApiVersion 2.0
@NScriptType datasetbuilderplugin */
define(['N/dataset'], function(dataset) { return Object.freeze({ createDataset: function(context) {
var join = dataset.createJoin({
fieldId: "budgetmachine"
});
var period = dataset.createColumn({
join: join,
fieldId: "period",
alias: "budgetmachineperiod",
label: "Accounting Period"
});
var department = dataset.createColumn({
fieldId: "department",
alias: "department",
label: "Department"
});
var total = dataset.createColumn({
fieldId: "total",
alias: "total",
label: "Amount (Total)"
});
var ds = dataset.create({
type: 'budgets',
columns: [period, department, total]
});
context.dataset = ds;
}
}); });
3) Create a suitescript file with workbook that joins both datasets and displays results in a pivot table
/**
@NApiVersion 2.0
@NScriptType workbookbuilderplugin */
define(['N/workbook', 'N/dataset', 'N/datasetLink'], function(nWorkbook, nDataset, datasetLink) { return Object.freeze({ createWorkbook: function(context) {
var budget = nDataset.load({
id: 'customscript_budget'
});
var sales = nDataset.load({
id: 'customscript_sales'
});
var budgetmachineperiod = budget.getExpressionFromColumn({alias:"budgetmachineperiod"});
var postingperiod = sales.getExpressionFromColumn({alias:"postingperiod"});
var link = datasetLink.create({
datasets: [budget, sales],
expressions: [[budgetmachineperiod, postingperiod]],
id: "link"
});
var postingPeriodItem = nWorkbook.createDataDimensionItem({expression: postingperiod});
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
});
context.workbook = nWorkbook.create({
pivots: [pivot]
});
}
}); });
4) Make the workbook visible and register the plug-ins
First, register Dataset plugins:
Customization -> Plug-ins -> Plug-in implementations -> New
budget.js -> Name: Budget, ID: _budget (will be renamed as customscript_budget)
actual.js -> Name: Sales, ID: _sales (will be renamed as customscript_sales)
Second, register Workbook plugin: WorkbookbWithDsLink.js -> Name: WbWithDsLink, id: _wbwithdslink
5) Then go to Workbook you've created, and you should see following indication of the dataset linking in the pivot table