Sales Summary Report

Open the demo in new window can open the designer

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

• Define the Order, OrderDetail(contains calculated column), Employee tables in the DataManager.
• Set the style for the template.
• Bind the data column to the template and use the 'Group'(formula group) and 'Summary' cell types, and use both 'Horizontal' and 'Vertical' spill directions.
• Set the sortOptions and filter for the template cell.
• Add the formula for the summary cell.

window.onload = async () => {
    const spread = new GC.Spread.Sheets.Workbook('demo-host', { sheetCount: 1 });
    await addTables(spread);
    addSalesSummaryReport(spread);
}

async function addTables(spread) {
    const baseUrl = window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api';
    const tables = ['Order', 'OrderDetail', 'Employee'].map(x => spread.dataManager().addTable(x, { remote: { read: { url: `${baseUrl}/${x}` } } }));
    await Promise.all(tables.map(x => x.fetch()));
    tables[1].columns['Sales'] = {
        value: '=[@UnitPrice]*[@Quantity]*(1-[@Discount])',
        caption: 'Sales',
    };
}
function addSalesSummaryReport(spread) {
    const reportSheet = spread.addSheetTab(0, 'Sales Summary Report', GC.Spread.Sheets.SheetType.reportSheet);
    const templateSheet = reportSheet.getTemplate();

    templateSheet.options.gridline.showHorizontalGridline = false;
    templateSheet.options.gridline.showVerticalGridline = false;
    templateSheet.defaults.colWidth = 160;
    templateSheet.defaults.rowHeight = 30;
    const headerStyle = new GC.Spread.Sheets.Style();
    headerStyle.backColor = '#BBDEFB';
    headerStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    headerStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    headerStyle.foreColor = '#424242';
    headerStyle.font = '12px "Open Sans"';
    const dataStyle = new GC.Spread.Sheets.Style();
    dataStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    dataStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
    dataStyle.foreColor = '#424242';
    dataStyle.font = '12px "Open Sans"';
    const border = new GC.Spread.Sheets.LineBorder('#e0e0e0', 1);
    dataStyle.borderBottom = border;
    templateSheet.setValue(0, 0, 'Employee');
    templateSheet.setValue(0, 2, 'Trend');
    templateSheet.getRange(0, 0, 1, 3).setStyle(headerStyle);
    templateSheet.getRange(1, 0, 1, 3).setStyle(dataStyle);
    templateSheet.setFormatter(1, 1, '0.00');
    templateSheet.setFormula(1, 2, '=LINESPARKLINE(B2,0)');
    const summaryStyle = dataStyle.clone();
    summaryStyle.backColor = '#FFECB3';
    templateSheet.getRange(2, 0, 1, 3).setStyle(summaryStyle);
    templateSheet.setValue(2, 0, 'Total');
    templateSheet.setFormatter(2, 1, '0.00');
    templateSheet.setFormula(2, 1, 'SUM(B2)');
    templateSheet.setFormula(2, 2, '=LINESPARKLINE(B3,0)');

    templateSheet.setTemplateCell(0, 1, {
        type: 'Group',
        formula: '=Year([@OrderDate])',
        binding: 'Order[OrderDate]',
        spillDirection: 'Horizontal',
    });
    templateSheet.setTemplateCell(1, 0, {
        type: 'Group',
        binding: 'Employee[FirstName]',
        filter: {
            condition: {
                column: 'Id',
                operator: 'Equal',
                dataColumn: {
                    tableName: 'Order',
                    columnName: 'EmployeeId',
                }
            },
        },
        sortOptions: [{ asc: 'FirstName' }]
    });
    templateSheet.setTemplateCell(1, 1, {
        type: 'Summary',
        aggregate: 'Sum',
        binding: 'OrderDetail[Sales]',
        filter: {
            condition: {
                column: 'OrderId',
                operator: 'Equal',
                dataColumn: {
                    tableName: 'Order',
                    columnName: 'Id',
                }
            },
        },
    });

    reportSheet.refresh();
}