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.1 Script Basics and SuiteScript 2.1 Script Types.

          /**
 * @NApiVersion 2.1
 */
// The following script creates a simple workbook that contains a simple table
require(['N/workbook', 'N/dataset', 'N/datasetLink'], (nWorkbook, nDataset, datasetLink) => {
    const join = nDataset.createJoin({
        fieldId: 'budgetmachine'
    });

    const period = nDataset.createColumn({
        join,
        fieldId: 'period',
        alias: 'budgetmachineperiod',
        label: 'Accounting Period'
    });

    const department = nDataset.createColumn({
        fieldId: 'department',
        alias: 'department',
        label: 'Department'
    });

    const total = nDataset.createColumn({
        fieldId: 'total',
        alias: 'total',
        label: 'Amount (Total)'
    });

    const budget = nDataset.create({
        type: 'budgets',
        columns: [period, department, total]
    });

    const postingperiod = nDataset.createColumn({
        fieldId: 'postingperiod',
        alias: 'postingperiod',
        label: 'Posting Period'
    });

    const amount = nDataset.createColumn({
        fieldId: 'amount',
        alias: 'amount',
        label: 'Amount'
    });

    const sales = nDataset.create({
        type: 'salesinvoiced',
        columns: [postingperiod, department, amount],
    });


    const budgetmachineperiod = budget.getExpressionFromColumn({
        alias: 'budgetmachineperiod'
    });
    const postingperiodExpression = sales.getExpressionFromColumn({
        alias: 'postingperiod'
    });
    const link = datasetLink.create({
        datasets: [budget, sales],
        expressions: [[budgetmachineperiod, postingperiodExpression]],
        id: "link"
    });

    const postingPeriodItem = nWorkbook.createDataDimensionItem({
        expression: postingperiodExpression
    });
    const postingPeriodDimension = nWorkbook.createDataDimension({
        items: [postingPeriodItem]
    });
    const rowSection = nWorkbook.createSection({
        children: [postingPeriodDimension]
    });

    const departmentItem = nWorkbook.createDataDimensionItem({
        expression: budget.getExpressionFromColumn({
            alias: "department"
        })
    });
    const departmentDimension = nWorkbook.createDataDimension({
        items: [departmentItem]
    });

    const sumTotal = nWorkbook.createDataMeasure({
        label: 'Sum Total',
        expression: budget.getExpressionFromColumn({
            alias: 'total'
        }),
        aggregation: 'SUM'
    });

    const sumAmountNet = nWorkbook.createDataMeasure({
        label: 'Sum Amount',
        expression: sales.getExpressionFromColumn({
            alias: 'amount'
        }),
        aggregation: 'SUM'
    });

    const columnSection = nWorkbook.createSection({
        children: [departmentDimension, sumTotal, sumAmountNet]
    });

    const pivot = nWorkbook.createPivot({
        id: "pivot",
        rowAxis:  nWorkbook.createPivotAxis({
            root: rowSection
        }),
        columnAxis: nWorkbook.createPivotAxis({
            root: columnSection
        }),
        name: "Pivot",
        datasetLink: link
    });

    const wb = nWorkbook.create({
        pivots: [pivot]
    });

    wb.runPivot.promise('pivot').then((intersections) => {
        for (const intersection of intersections) {
            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);
            }
        }
    })
}); 

        

General Notices