Parameter & ParameterUI

Open the demo in new window can open the designer

The Parameter interface is as follows:

interface IParameter {
    [parameterName: string]: string | number | boolean | Date;
}
class ReportSheet {
    function setParametersUI(host: HTMLElement | string, initParametersUI: GC.Spread.Report.InitParametersUIFunctionType, onChange: GC.Spread.Report.OnChangeFunctionType): void;
}

type InitParametersUIFunctionType = (sheet: Worksheet) => void;

type OnChangeFunctionType = (sheet: ReportSheet, changedArgs: IParameterChangedArgs) => void;

interface IParameterChangedArgs {
    tag: string; // changed cell bindingPath or tag.
    oldValue: string | number | boolean | Date;
    newValue: string | number | boolean | Date;
}

Right now, the parameter can only be used in a filter.

1. Define a parameter for a reportSheet:

    const spread = new GC.Spread.Sheets.Workbook('ss', { sheetCount: 1 });
    const ordersTable = spread.dataManager().addTable('Orders', {
        remote: {
            read: {
                url: baseApiUrl + '/Order'
            }
        }
    });
    ordersTable.fetch().then(() => {
        const reportSheet = spread.addSheetTab(0, 'report1', GC.Spread.Sheets.SheetType.reportSheet);
        reportSheet.parameter({
            customerId: "VINET",
            startDate: new Date("1996/7/1"),
            endDate: new Date("1998/7/1"),
        });
    });

2. Use a parameter in a template cell filter condition:

    const reportSheet = spread.getActiveSheetTab();
    const templateSheet = reportSheet.getTemplate();
    // set value and binding for the template
    templateSheet.setTemplateCell(1, 0, {
        type: 'List',
        binding: `Orders[orderId]`,
        filter: {
            condition: {
                and: [
                    {
                        column: "customerId",
                        operator: "Equal",
                        parameter: "customerId", // use parameter in filter condition.
                    },
                    {
                        column: "orderDate",
                        operator: "GreaterThanOrEqual",
                        parameter: "startDate",
                    },
                    {
                        column: "orderDate",
                        operator: "lessThanOrEqual",
                        parameter: "endDate"
                    }
                ]
            }
        }
    });
    // refresh the report
    reportSheet.refresh();
    // if users want to update the parameter.
    reportSheet.parameter({
        customerId: "TOMSP",
        startDate: new Date("1996/7/1"),
        endDate: new Date("1998/7/1"),
    });
    reportSheet.refresh();

3. Initialize the parameterUI (Optional):

    var reportSheet = spread.getActiveSheetTab();
    var parameterUIHost = document.getElementById("report-sheet-parameter-ui");
    reportSheet.setParametersUI(parameterUIHost, initParametersUI, parameterOnChanged);

    function initParametersUI (sheet) { // the parameterUI sheet init callback
        sheet.suspendPaint();
        sheet.suspendEvent();
        sheet.getCell(-1, -1).backColor("rgb(225, 225, 225)");
        sheet.setColumnWidth(0, 5);
        sheet.setColumnWidth(1, 220);
        sheet.setColumnWidth(2, 10);
        sheet.setColumnWidth(3, 220);
        sheet.setColumnWidth(4, 10);
        sheet.setColumnWidth(5, 220);
        sheet.setColumnWidth(6, 10);
        sheet.setColumnWidth(7, 105);
        sheet.setRowHeight(0, 10);
        sheet.setRowHeight(1, 30);
        sheet.setRowHeight(2, 30);
        sheet.setValue(1, 1, "CustomerID:");
        sheet.setValue(1, 3, "StartDate:");
        sheet.setValue(1, 5, "EndDate:");
        sheet.getCell(2, 1).bindingPath("customerId"); // bind parameter
        sheet.getCell(2, 3).bindingPath("startDate");
        sheet.getCell(2, 5).bindingPath("endDate");

        let submitButton = new GC.Spread.Sheets.CellTypes.Button(); // add submit button
        submitButton.text("Submit");
        submitButton.buttonBackColor("#00897B");
        submitButton.buttonClickColor("#004D40");
        sheet.getCell(2, 7).tag("submitButton").foreColor("white").cellType(submitButton);
        let style = sheet.getStyle(2, 7);
        style.hAlign = HorizontalAlign.center;
        style.vAlign = VerticalAlign.center;
        style.textIndent = 0;
        sheet.setStyle(2, 7, style);

        sheet.resumeEvent();
        sheet.resumePaint();
    };
    function parameterOnChanged (reportSheet, valueChangedArgs) { // the parameterUI changed callback
        if (valueChangedArgs.tag === "submitButton") { // judge the changed parameter.
            reportSheet.regenerateReport();
        }
    }