V tree sales reports can be useful for tracking the performance of individual salespeople or product lines, identifying trends in consumer behavior, and determining overall growth or decline in revenue.
You can use the ‘Group' and 'Summary’ cell types, and custom left parent to generate the v tree sales report in the following steps.
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, 6],
['East', 'Mike', 'Fruits', 'Banana', 200, 21],
['East', 'Emma', 'Fruits', 'Banana', 210, 16],
['East', 'Sophia', 'Fruits', 'Banana', 316, 5],
['East', 'Jennifer', 'Fruits', 'Banana', 130, 11],
['North', 'Kristen', 'Fruits', 'Banana', 410.0, 21],
['North', 'Mia', 'Fruits', 'Banana', 290.0, 2],
['North', 'Bella', 'Fruits', 'Banana', 342.0, 45],
['North', 'Eva', 'Fruits', 'Banana', 214.0, 21],
['East', 'John', 'Fruits', 'Strawberry', 230.0, 15],
['East', 'Mike', 'Fruits', 'Strawberry', 641.0, 33],
['East', 'Emma', 'Fruits', 'Strawberry', 234.0, 15],
['East', 'Sophia', 'Fruits', 'Strawberry', 625.0, 53],
['East', 'Jennifer', 'Fruits', 'Strawberry', 241.0, 25],
['North', 'Kristen', 'Fruits', 'Strawberry', 195.0, 36],
['North', 'Mia', 'Fruits', 'Strawberry', 569.0, 23],
['North', 'Bella', 'Fruits', 'Strawberry', 698.0, 51],
['North', 'Eva', 'Fruits', 'Strawberry', 214.0, 67],
['East', 'John', 'Fruits', 'Watermelon', 147.0, 36],
['East', 'Mike', 'Fruits', 'Watermelon', 489.0, 26],
['East', 'Emma', 'Fruits', 'Watermelon', 347.0, 27],
['East', 'Sophia', 'Fruits', 'Watermelon', 652.0, 36],
['East', 'Jennifer', 'Fruits', 'Watermelon', 471.0, 31],
['North', 'Kristen', 'Fruits', 'Watermelon', 287.0, 43],
['North', 'Mia', 'Fruits', 'Watermelon', 349.0, 21],
['North', 'Bella', 'Fruits', 'Watermelon', 163.0, 35],
['North', 'Eva', 'Fruits', 'Watermelon', 841.0, 36],
['East', 'John', 'Snack', 'Chips', 292.0, 72],
['East', 'Mike', 'Snack', 'Chips', 514.0, 13],
['East', 'Emma', 'Snack', 'Chips', 256.0, 5],
['East', 'Sophia', 'Snack', 'Chips', 148.0, 31],
['East', 'Jennifer', 'Snack', 'Chips', 486.0, 53],
['North', 'Kristen', 'Snack', 'Chips', 285.0, 13],
['North', 'Mia', 'Snack', 'Chips', 741.0, 42],
['North', 'Bella', 'Snack', 'Chips', 249.0, 13],
['North', 'Eva', 'Snack', 'Chips', 105.0, 53],
['East', 'John', 'Snack', 'Cookie', 554.0, 7],
['East', 'Mike', 'Snack', 'Cookie', 311.0, 38],
['East', 'Emma', 'Snack', 'Cookie', 186.0, 35],
['East', 'Sophia', 'Snack', 'Cookie', 654.0, 28],
['East', 'Jennifer', 'Snack', 'Cookie', 247.0, 19],
['North', 'Kristen', 'Snack', 'Cookie', 143.0, 40],
['North', 'Mia', 'Snack', 'Cookie', 617.0, 23],
['North', 'Bella', 'Snack', 'Cookie', 214.0, 53],
['North', 'Eva', 'Snack', 'Cookie', 324.0, 37],
];
const columns = ['Region', 'Salesman', 'ProductType', 'Product', 'Sales', 'Return'];
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, 'V Tree Sales Report', GC.Spread.Sheets.SheetType.reportSheet);
const templateSheet = reportSheet.getTemplate();
// set style for the template
templateSheet.defaults.colWidth = 120;
templateSheet.defaults.rowHeight = 30;
templateSheet.setColumnWidth(0, 250);
const style = new GC.Spread.Sheets.Style();
style.backColor = '#81D4FA';
style.foreColor = '#424242';
style.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
style.vAlign = GC.Spread.Sheets.VerticalAlign.center;
style.font = '12px Maine';
templateSheet.getRange('A1:C6').setStyle(style);
for (let r = 2; r <= 5; r++) {
for (let c = 0; c <= 2; c++) {
const newStyle = templateSheet.getStyle(r, c).clone();
if (c === 0) {
newStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.left;
if (r === 2) {
newStyle.backColor = '#00BCD4';
}
if (r === 3) {
newStyle.backColor = '#26C6DA';
newStyle.textIndent = 2;
}
if (r === 4) {
newStyle.backColor = '#80DEEA';
newStyle.textIndent = 4;
}
if (r === 5) {
newStyle.backColor = '#B2EBF2';
newStyle.textIndent = 8;
}
}
else {
if (r === 2) {
newStyle.backColor = '#FFCC80';
}
if (r === 3) {
newStyle.backColor = '#FFE0B2';
}
if (r === 4) {
newStyle.backColor = '#FFF3E0';
}
if (r === 5) {
newStyle.backColor = undefined;
}
}
templateSheet.setStyle(r, c, newStyle);
}
}
[[0, 0, 2, 1], [0, 1, 1, 2]].forEach(x => templateSheet.addSpan(...x));
templateSheet.setValue(0, 0, 'Category');
templateSheet.setValue(0, 1, 'Summary');
templateSheet.setValue(1, 1, 'Sales');
templateSheet.setValue(1, 2, 'Return');
// set binding for the template
['Region', 'Salesman', 'ProductType', 'Product'].forEach((x, r) => {
templateSheet.setTemplateCell(r + 2, 0, {
type: 'Group',
binding: `Sales[${x}]`,
spillDirection: 'Vertical',
context: {
vertical: r > 0 ? `A${r + 2}` : 'Default',
},
showCollapseButton: r !== 3,
});
templateSheet.setTemplateCell(r + 2, 1, {
type: 'Summary',
aggregate: 'Sum',
binding: 'Sales[Sales]',
});
templateSheet.setTemplateCell(r + 2, 2, {
type: 'Summary',
aggregate: 'Sum',
binding: 'Sales[Return]',
});
});
reportSheet.refresh();
});
Submit and view feedback for