createWorkbook(context)

Example

            /**
 * @NApiVersion 2.x
 * @NScriptType workbookbuilderplugin
 */
define(['N/workbook', 'N/dataset', 'N/query'], function(wb, ds, query) {
    return Object.freeze({
        createWorkbook: function(context) {
            var dataset = ds.load({
                id: "%s"
            });

            var idExpr = dataset.getExpressionFromColumn({
                alias: 'id'
            });

            var totalExpr = dataset.getExpressionFromColumn({
                alias: 'total'
            });

            var constExpr = wb.createConstant({
                constant: 1,
                type: wb.ConstantType.DECIMAL

            });
            var anyOfExpr = wb.createExpression({
                functionId: wb.ExpressionType.ANY_OF,
                parameters: {
                    expression: idExpr,
                    set: [constExpr]
                }
            });
            var notExpr = wb.createExpression({
                functionId: wb.ExpressionType.NOT,
                parameters: {
                    operand1: anyOfExpr
                }
            });

            var sort = wb.createSort({
                ascending: false,
                locale: query.SortLocale.EN_AU,
                nullsLast: false,
                caseSensitive: true
            });

            var tableViewFilter = wb.createTableColumnFilter({
                operator: query.Operator.START_WITH_NOT,
                values: ["test"]
            });

            var fieldContext = wb.createFieldContext({
                name: 'RAW'
            });

            var fieldContext1 = wb.createFieldContext({
                name: 'HIERARCHY'
            });

            var col1 = wb.createTableColumn({
                datasetColumnAlias: 'id',
                fieldContext: fieldContext
            });

            var col2 = wb.createTableColumn({
                condition: wb.createTableColumnCondition({operator: "AND", filters: [tableViewFilter]}),
                width: 10,
                datasetColumnAlias: 'name',
                label: "Name",
                sort: sort
            });

            var col3 = wb.createTableColumn({
                datasetColumnAlias: 'date'
            });
            var col4 = wb.createTableColumn({
                datasetColumnAlias: 'total'
            });
            var col5 = wb.createTableColumn({
                datasetColumnAlias: 'subsidiary',
                fieldContext: fieldContext1
            });
            var tableview = wb.createTable({
                name: "View",
                id: "view",
                dataset: dataset,
                columns: [col1, col2, col3, col4, col5]
            });

            var rowItem = wb.createDataDimensionItem({
                label: "A",
                expression: dataset.getExpressionFromColumn({
                    alias: "date"
                })
            });

            var rowDataDimension = wb.createDataDimension({
                items: [rowItem]
            });

            var rowSection = wb.createSection({
                totalLine: wb.TotalLine.FIRST_LINE,
                children: [rowDataDimension]
            });


            var columnItem = wb.createDataDimensionItem({
                label: "B",
                expression: dataset.getExpressionFromColumn({
                    alias: "total"
                })
            });

            // var colDataDimensionChild = wb.createDataDimension({
            //     items: [columnItem]
            // });

            var columnDataDimension = wb.createDataDimension({
                totalLine: wb.TotalLine.HIDDEN,
                items: [columnItem],
                // children: [colDataDimensionChild]
            });

            var columnMeasure = wb.createDataMeasure({
                label: "M",
                expressions: [idExpr, totalExpr],
                aggregation: wb.Aggregation.COUNT_DISTINCT
            });

            var columnSection = wb.createSection({
                children: [columnDataDimension, columnMeasure]
            });

            var allSubNodesSelector = wb.DescendantOrSelfNodesSelector;

            var rowItemSelector = wb.createDimensionSelector({
                dimension: rowDataDimension
            });
            var columnItemSelector = wb.createDimensionSelector({
                dimension: columnDataDimension
            });

            var pathSelector = wb.createPathSelector({
                elements: [allSubNodesSelector, columnItemSelector]
            });

            var rowSelector = wb.createPathSelector({
                elements: [allSubNodesSelector, rowItemSelector]
            });
            var columnSelector = wb.createPathSelector({
                elements: [allSubNodesSelector, columnItemSelector]
            });

            var rowSort = wb.createSortByDataDimensionItem({
                item: rowItem,
                sort: sort
            });

            var columnSort = wb.createSortByMeasure({
                measure: columnMeasure,
                sort: sort,
                otherAxisSelector: allSubNodesSelector
            });

            var rowSortDefinition = wb.createSortDefinition({
                row: false,
                sortBys: [rowSort],
                selector: rowSelector
            });

            var columnSortDefinition = wb.createSortDefinition({
                sortBys: [columnSort],
                selector: columnSelector
            });

            var rowAxis = wb.createPivotAxis({
                root: rowSection,
                sortDefinitions: [rowSortDefinition]
            });

            var columnAxis = wb.createPivotAxis({
                root: columnSection,
                sortDefinitions: [columnSortDefinition]
            });


            var pivot = wb.createPivot({
                name: "Pivot",
                id: "pivot",
                rowAxis: rowAxis,
                columnAxis: columnAxis,
                dataset: dataset
            });

            var firstAxis = wb.createChartAxis({
                title: "First axis"
            });
            var secondAxis = wb.createChartAxis({
                title: "Second axis"
            });

            var category = wb.createCategory({
                axis: firstAxis,
                root: rowSection,
                sortDefinitions: [rowSortDefinition]
            });
            var legend = wb.createLegend({
                axes: [secondAxis],
                root: columnSection,
                sortDefinitions: [columnSortDefinition]
            });
            var aspect = wb.createAspect({
                type: wb.AspectType.VALUE,
                measure: columnMeasure
            });
            var series = wb.createSeries({
                aspects: [aspect]
            });

            var chart = wb.createChart({
                name: "Chart",
                title: "First Chart",
                subTitle: "sub first chart",
                id: "chart",
                type: wb.ChartType.BAR,
                stacking: wb.Stacking.NORMAL,
                category: category,
                legend: legend,
                dataset: dataset,
                series: [series]
            });

            var workbook = wb.create({
                name: "Name Workbook",
                description: "Description Workbook",
                tables: [tableview],
                pivots: [pivot],
                charts: [chart],
                id: "custworkbook_workbook"
            });
            context.workbook = workbook;
        }
    });
}); 

          

Related Topics