Sort Cell Data

Using the sort options in the template cell, you can sort the data in the desired order like ascending, descending, or in their customized order. To configure the sorting options in the TemplateSheet, you must first define the table source in the DataManager. Then, in the template cell, configure the tableName and columnName in the dataColumn option, and then specify the desired sortOptions.

Notes:
The sort is working while generating report data before extending, so it cannot get the formula calculated results after generating report data.
The sort value must start with "=" to use sortByCell and sortByFormula methods as the sort value is always a string.
The sortByCell method only supports the format '=A1' and does not support R1C1, absolute references, or custom names.

You can sort data in various ways. Let's discuss them one by one.

Sort Data by Column Value

Users can specify which data column the current cellNode is sorted according to, then the currentNode and child node will reorder within currentNode parent groups. The column must be in the current node binding table.

The following code sample shows how to sort within the group/list, with multiple rules, and with no rules.

// Sort within the group.
// Sort by column "shipCounty" ascending.
templateSheet.setTemplateCell(2, 0, {
        type: "Group",
        binding: `Orders[shipCountry]`,
        sortOptions: [
            {
               asc: "shipCountry"
            }
        ]
});
// Sort within the list.
// Sort by column "customerId" descending.
templateSheet.setTemplateCell(2, 1, {
        type: "List",
        binding: `Orders[customerId]`,
        sortOptions: [
           {
              dsc: "customerId"
           }
       ]
});

// Sort by multiple rules.
// Sort by column "shipCounty" then by "sales".
templateSheet.setTemplateCell(0, 0, {
    type: "List",
    binding: `Orders[shipCountry]`,
    sortOptions: [
      {
          asc: "shipCountry"
      },
      {
          asc: "sales"
      }
    ]
});

// Default (no sorting applied).
templateSheet.setTemplateCell(2, 2, {
       type: "List",
       binding: `Orders[freight]`
});

Sort Data by Cell Value

Users can specify which cellNode value the current cellNode is sorted according to, the cellNode value must be the current cellNode child.

Notes:
If there are multiple values in the referred cellNode, the first value will be used.
If the referred cellNode is the current cellNode itself, it will sort by the current cell value.

The following code sample shows how to sort data by cell value.

// Sort by the B3 cell value.
templateSheet.setTemplateCell(2, 0, {
      type: "Group",
      binding: `Products[ProductName]`,
      sortOptions: [
          {
              asc: "=B3"
          }
     ]
});

templateSheet.setTemplateCell(2, 1, {
      type: "List",
      binding: `Products[UnitsInStock]`,
});

Sort Data by Formula Value

Users can use the formula as a sort option to sort data by the formula calculate result. The formula must start with the '=' sign.

The following code sample shows how to sort data by the formula value.

// Sort by the "=YEAR([@OrderDate])&\"/\"&MONTH([@OrderDate])" formula.
templateSheet.setTemplateCell(3, 0, {
           type: "Group",
           binding: `Orders[orderDate]`,
           "formula": "=YEAR([@OrderDate])&\"/\"&MONTH([@OrderDate])",  // Formula
           "sortOptions": [
                    {
                        "desc": "=YEAR([@OrderDate])"
                    },
                    {
                        "asc": "=MONTH([@OrderDate])",
                        // Custom list.
                        "list": [
                            4,
                            5,
                            6,
                            7,
                            8,
                            9,
                            10,
                            11,
                            12,
                            1,
                            2,
                            3
                        ]
                    }
           ]
});

templateSheet.setTemplateCell(3, 1, {
         type: "List",
         binding: `Orders[freight]`,
});

Sort Data by List Value

Users can use a specified value array in each sort option, then the sort result will order by the list order and then by ascending/descending.

The following code sample shows how to sort data as per the list value.

// Sort by the "Product" in the list ["Tofu", "Thin Bread", "Ikura Caviar"].
// The rest of the products are in the ascending order. 
templateSheet.setTemplateCell(3, 0, {
       type: "List",
       binding: `Products[Id]`,
       sortOptions: [
             {
                 asc: "=B4",
                 list: ["Tofu", "Thin Bread", "Ikura Caviar"]
             },
       ]
});
templateSheet.setTemplateCell(3, 1, {
        type: "Group",
        binding: `Products[ProductName]`,
});
templateSheet.setTemplateCell(3, 2, {
        type: "List",
        binding: `Products[UnitPrice]`,
});

Sort Cell Data Using Designer

You can also sort the data through the designer by using the Sort option available in the Report Cell properties panel. Click the Add button to create a sorting rule. You can change the order to ascending or descending, sort data by column or cell, and even set up custom sorting lists using the Custom List option. You can also click the Up/Down/Remove buttons to rearrange or remove the sort rules.

RS-Sort data