Set Filter Condition

In some conditions, users only want to see the data filtered by some conditions, or they want to combine multiple data tables by primary-foreign key in one report. In this scenario, SpreadJS lets you set filter conditions in template cells to filter data. To configure the data filtering conditions in the TemplateSheet, you need to define the table source in the DataManager, configure the tableName and columnName within the dataColumn option in the template cell, and finally add the desired filter conditions using the filter property in the template cell.

You can use any of the following methods to filter data in a ReportSheet.

Filter data by static value
Filter data by formula
Filter data by join tables

There are several condition rules of data filters.

Filter Condition Rule	Description
filterByCondition
ConditionRuleValueType	Static filter condition, the filter value data type is string \| number \| boolean \| date. Filters the records according to the ConditionRuleOperator and value.
ConditionRuleCellType	The cell name (like A1). When the cell value is a static value, the filter condition is the same as ConditionRuleValueType. When the cell has dataColumn data binding, the filter condition is the same as ConditionRuleDataColumnType.
ConditionRuleDataColumnType	Table join condition that joins the current table column with the target table column by ConditionRuleOperator.
ConditionRuleParameterType	Static filter condition that uses the existing parameter name.
ConditionRuleFormulaType	Static filter condition, users can set the formula here.
filterByFormula
FilterConditionFormulaRule	Static filter condition, used to filter the records matches the formula rule. Here, SpreadJS does not support cell reference in a formula, such as “=YEAR([@orderDate])=A1“.
ConditionRule	The ConditionRule column is the formula with a table field.

The following code samples show several usages of data filters.

// ConditionRule value is static value. 
// Filter by CustomerId Equals to "VINET".
templateSheet.setTemplateCell(2, 0, {
       type: "Group",
       binding: `Orders[customerId]`,
       filter: {
            condition: {
                 column: "customerId",
                 operator: "Equal",
                 value: "VINET"
            }
       }
});
templateSheet.setTemplateCell(2, 1, {
      type: "Group",
      binding: `Orders[employeeId]`,
});

// ConditionRule Value is CellName, cell value is static value.
templateSheet.setValue(1, 0, 'VINET');
templateSheet.setTemplateCell(1, 1, {
        type: "Group",
        binding: `Orders[customerId]`,
        filter: {
           condition: {
                  column: "customerId",
                  operator: "Equal",
                  cell: "A2"
            }
       }
});
templateSheet.setTemplateCell(1, 2, {
        type: "Group",
        binding: `Orders[employeeId]`,
});

// Join Table Condition.
// Add table relationship.
spread.dataManager().addRelationship(customerTable, "Id", "orderTable", ordersTable, "customerId", "customerTable");
templateSheet.setTemplateCell(2, 1, { // B1
         type: "List",
         binding: "Customers[CompanyName]",
         filter: {
               "condition": {
                        "column": "id",
                        "operator": "Equal",
                        "dataColumn": {
                            "tableName": "Orders",
                            "columnName": "customerId"
                        }
               }
       }
});

Filter Data Using Designer

You can also filter the data through the designer by using the Filter option available in the Report Cell properties panel.

RS-filterOption

You can add multiple static filter conditions, table join conditions, or formula filter conditions, and also change the rule order or delete rules in the Filter dialog.

RS-AddFilterRule

The following table shows the properties present in the Filter dialog along with their descriptions.

Property	Description
Relation	Display the relation between two conditions.
Column	Presents the current node table column list. You can also input a formula using the table column like =YEAR([@orderDate])
Operator	Display all filter condition operator list.
Value	Use to switch to the different data types and input the data value.
Brackets	Use to change the condition priority. The left and right brackets must always match.
Rule Type	Specify the type of filter rule. If the type is Condition, you can select a column, operator, or value. Whereas, if the rule type is Formula, only the formula value can be set.