Sales V Sliced Cross Report

Open the demo in new window can open the designer

You can use the ‘Group' and 'Summary’ cell types to generate the sales v sliced report in the following steps.

• Define the sales table in the DataManager.
• Set the style for the template.
• Bind the data column to the template and use the ‘Group' and ‘Summary’ cell types, and use both ‘Horizontal’ and 'Vertical’ spill directions.

const spread = new GC.Spread.Sheets.Workbook('ss', { sheetCount: 1 });
spread.options.scrollByPixel = true;
spread.options.scrollbarMaxAlign = true;
const records = [
    ['East', 'John', 'Fruits', 'Banana', 180],
    ['East', 'Mike', 'Fruits', 'Banana', 200],
    ['East', 'Emma', 'Fruits', 'Banana', 210],
    ['East', 'Sophia', 'Fruits', 'Banana', 316],
    ['East', 'Jennifer', 'Fruits', 'Banana', 130],
    ['North', 'Kristen', 'Fruits', 'Banana', 410.0],
    ['North', 'Mia', 'Fruits', 'Banana', 290.0],
    ['North', 'Bella', 'Fruits', 'Banana', 342.0],
    ['North', 'Eva', 'Fruits', 'Banana', 214.0],
    ['East', 'John', 'Fruits', 'Strawberry', 230.0],
    ['East', 'Mike', 'Fruits', 'Strawberry', 641.0],
    ['East', 'Emma', 'Fruits', 'Strawberry', 234.0],
    ['East', 'Sophia', 'Fruits', 'Strawberry', 625.0],
    ['East', 'Jennifer', 'Fruits', 'Strawberry', 241.0],
    ['North', 'Kristen', 'Fruits', 'Strawberry', 195.0],
    ['North', 'Mia', 'Fruits', 'Strawberry', 569.0],
    ['North', 'Bella', 'Fruits', 'Strawberry', 698.0],
    ['North', 'Eva', 'Fruits', 'Strawberry', 214.0],
    ['East', 'John', 'Fruits', 'Watermelon', 147.0],
    ['East', 'Mike', 'Fruits', 'Watermelon', 489.0],
    ['East', 'Emma', 'Fruits', 'Watermelon', 347.0],
    ['East', 'Sophia', 'Fruits', 'Watermelon', 652.0],
    ['East', 'Jennifer', 'Fruits', 'Watermelon', 471.0],
    ['North', 'Kristen', 'Fruits', 'Watermelon', 287.0],
    ['North', 'Mia', 'Fruits', 'Watermelon', 349.0],
    ['North', 'Bella', 'Fruits', 'Watermelon', 163.0],
    ['North', 'Eva', 'Fruits', 'Watermelon', 841.0],
    ['East', 'John', 'Snack', 'Chips', 292.0],
    ['East', 'Mike', 'Snack', 'Chips', 514.0],
    ['East', 'Emma', 'Snack', 'Chips', 256.0],
    ['East', 'Sophia', 'Snack', 'Chips', 148.0],
    ['East', 'Jennifer', 'Snack', 'Chips', 486.0],
    ['North', 'Kristen', 'Snack', 'Chips', 285.0],
    ['North', 'Mia', 'Snack', 'Chips', 741.0],
    ['North', 'Bella', 'Snack', 'Chips', 249.0],
    ['North', 'Eva', 'Snack', 'Chips', 105.0],
    ['East', 'John', 'Snack', 'Cookie', 554.0],
    ['East', 'Mike', 'Snack', 'Cookie', 311.0],
    ['East', 'Emma', 'Snack', 'Cookie', 186.0],
    ['East', 'Sophia', 'Snack', 'Cookie', 654.0],
    ['East', 'Jennifer', 'Snack', 'Cookie', 247.0],
    ['North', 'Kristen', 'Snack', 'Cookie', 143.0],
    ['North', 'Mia', 'Snack', 'Cookie', 617.0],
    ['North', 'Bella', 'Snack', 'Cookie', 214.0],
    ['North', 'Eva', 'Snack', 'Cookie', 324.0],
];
const columns = ['Region', 'Salesman', 'ProductType', 'Product', 'Sales'];
const salesTable = spread.dataManager().addTable('Sales', {
    data: records.map((x) => {
        const record = {};
        columns.forEach((c, i) => record[c] = x[i]);
        return record;
    })
});

salesTable.fetch().then(() => {
    const reportSheet = spread.addSheetTab(0, 'report1', GC.Spread.Sheets.SheetType.reportSheet);
    const templateSheet = reportSheet.getTemplate();

    // set style for the template
    templateSheet.defaults.colWidth = 100;
    templateSheet.defaults.rowHeight = 30;
    templateSheet.setColumnWidth(0, 200);
    const headerStyle = new GC.Spread.Sheets.Style();
    headerStyle.backColor = '#90CAF9';
    headerStyle.foreColor = '#424242';
    headerStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    headerStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    headerStyle.font = '12px Maine';
    templateSheet.getRange('A1:D8').setStyle(headerStyle);
    [2, 4, 6].forEach(r => {
        const newStyle = templateSheet.getStyle(r, 0).clone();
        newStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
        newStyle.textIndent = 1;
        templateSheet.setStyle(r, 0, newStyle);
    });
    [1, 2].forEach(c => {
        const newStyle = templateSheet.getStyle(1, c).clone();
        newStyle.backColor = '#B3E5FC';
        templateSheet.setStyle(1, c, newStyle);
    });
    const newStyle = templateSheet.getStyle(0, 1).clone();
    newStyle.backColor = '#B3E5FC';
    templateSheet.setStyle(0, 1, newStyle);
    const border = new GC.Spread.Sheets.LineBorder('#E0E0E0', 1);
    ['#C8E6C9', '#A5D6A7', '#81C784'].forEach((bg, r) => {
        r = (r * 2) + 3;
        const newStyle = templateSheet.getStyle(r, 0).clone();
        newStyle.backColor = bg;
        templateSheet.setStyle(r, 0, newStyle);

        ['#FFCC80', undefined, '#FFA726'].forEach((x, c) => {
            const newStyle1 = templateSheet.getStyle(r, c + 1).clone();
            newStyle1.backColor = x;
            if (!x) {
                newStyle1.borderBottom = border;
                newStyle1.borderTop = border;
                newStyle1.borderLeft = border;
                newStyle1.borderRight = border;
            }
            templateSheet.setStyle(r, c + 1, newStyle1);
        })
    });

    [[0, 0, 2, 1], [0, 1, 2, 1], [0, 3, 2, 1], [2, 0, 1, 4], [4, 0, 1, 4], [6, 0, 1, 4]].forEach(x => templateSheet.addSpan(...x));
    templateSheet.setValue(0, 0, 'Category');
    templateSheet.setValue(0, 2, 'Include');
    templateSheet.setValue(0, 3, 'Total');
    templateSheet.setValue(2, 0, 'Product Sales');
    templateSheet.setValue(4, 0, 'Product Type Sales');
    templateSheet.setValue(6, 0, 'Total Sales');
    templateSheet.setValue(7, 0, 'Total');

    // binding for the template
    templateSheet.setTemplateCell(0, 1, {
        type: 'Group',
        binding: 'Sales[Region]',
        spillDirection: 'Horizontal',
        showCollapseButton: true,
    });
    templateSheet.setTemplateCell(1, 2, {
        type: 'Group',
        binding: 'Sales[Salesman]',
        spillDirection: 'Horizontal',
        context: {
            horizontal: 'B1',
        },
    });
    ['Product', 'ProductType', ''].forEach((x, r) => {
        r = (r * 2) + 3;
        if (x) {
            templateSheet.setTemplateCell(r, 0, {
                type: 'Group',
                binding: `Sales[${x}]`,
            });
        }

        [1, 2, 3].forEach(c => {
            templateSheet.setTemplateCell(r, c, {
                type: 'Summary',
                aggregate: 'Sum',
                binding: 'Sales[Sales]',
                context: {
                    horizontal: c === 1
                        ? 'B1'
                        : c === 2 ? 'C2' : 'Default',
                },
            });
        });
    });
    [2, 4, 6].forEach(r => {
        templateSheet.setTemplateCell(r, 0, {
            type: 'Static',
            context: {
                horizontal: 'None',
            },
        });
    });

    reportSheet.refresh();
});