Show Calculated Columns in a Grid

See SAIL Recipes for information about how to work through recipes and adapt them to your application.

Goal

Display and sort on an additional column for total price that is not a field in the CDT, but calculated based on the unit price and quantity fields.

To do this, we'll store the calculated data in a dictionary that contains the relevant CDT fields as well as the additional calculate field.

image:SAIL_Recipes_Show_Calculated_Columns_in_a_Grid_76.jpg

The main expression uses a supporting rule, so let's create it first.

  • ucGridData: Calculates additional data to be displayed in the grid

Create expression rule ucGridData with the following rule inputs:

  • data (Any Type)

Enter the following definition for the rule:

={
  /* Explicit casting allows us to sort using todatasubset(). This is only */
  /* necessary because the underlying data is a dictionary.                */
  summary: tostring(ri!data.summary),
  qty: tointeger(ri!data.qty),
  unitPrice: todecimal(ri!data.unitPrice),
  totalPrice: tointeger(ri!data.qty) * todecimal(ri!data.unitPrice)
}

Now that we've created the supporting rule, let's move on to the main expression.

Expression

=load(
  local!data: {
    {summary: "Item 1", qty: 5, unitPrice: 9.99},
    {summary: "Item 2", qty: 2, unitPrice: 19.99},
    {summary: "Item 3", qty: 10, unitPrice: 1.99},
    {summary: "Item 4", qty: 4, unitPrice: 14.99},
    {summary: "Item 5", qty: 7, unitPrice: 3.99}
  },
  local!gridData: apply(rule!ucGridData, local!data),
  /* batchSize is 3 to show more than 1 page of data in this recipe. Increase it as needed. */
  local!pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 3),
  with(
    local!datasubset: todatasubset(local!gridData, local!pagingInfo),
    a!gridField(
      label: "SAIL Example: Show Caluclated Columns in a Read-Only Paging Grid",
      totalCount: local!datasubset.totalCount,
      columns: {
        a!gridTextColumn(
          label: "Summary",
          field: "summary",
          data: index(local!datasubset.data, "summary", {})
        ),
        a!gridTextColumn(
          label: "Quantity",
          field: "qty",
          data: index(local!datasubset.data, "qty", {}),
          alignment: "RIGHT"
        ),
        a!gridTextColumn(
          label: "Unit Price",
          field: "unitPrice",
          data: dollar(index(local!datasubset.data, "unitPrice", {})),
          alignment: "RIGHT"
        ),
        a!gridTextColumn(
          label: "Total Price",
          field: "totalPrice",
          data: dollar(index(local!datasubset.data, "totalPrice", {})),
          alignment: "RIGHT"
        )
      },
      value: local!pagingInfo,
      saveInto: local!pagingInfo
    )
  )
)

Test it out

  1. Sort the grid by the "Total Price" column. Notice that the data sorts appropriately even across all pages of data.

Offline

This expression shows how to modify the above expression for offline use. The only difference is that all rows are displayed initially since grid paging is not available when offline.

=load(
  local!data: {
    {summary: "Item 1", qty: 5, unitPrice: 9.99},
    {summary: "Item 2", qty: 2, unitPrice: 19.99},
    {summary: "Item 3", qty: 10, unitPrice: 1.99},
    {summary: "Item 4", qty: 4, unitPrice: 14.99},
    {summary: "Item 5", qty: 7, unitPrice: 3.99}
  },
  local!gridData: apply(rule!ucGridData, local!data),
  local!pagingInfo: a!pagingInfo(startIndex: 1, batchSize: -1),
  with(
    local!datasubset: todatasubset(local!gridData, local!pagingInfo),
    a!gridField(
      label: "SAIL Example: Show Caluclated Columns in a Read-Only Paging Grid",
      totalCount: local!datasubset.totalCount,
      columns: {
        a!gridTextColumn(
          label: "Summary",
          field: "summary",
          data: index(local!datasubset.data, "summary", {})
        ),
        a!gridTextColumn(
          label: "Quantity",
          field: "qty",
          data: index(local!datasubset.data, "qty", {}),
          alignment: "RIGHT"
        ),
        a!gridTextColumn(
          label: "Unit Price",
          field: "unitPrice",
          data: dollar(index(local!datasubset.data, "unitPrice", {})),
          alignment: "RIGHT"
        ),
        a!gridTextColumn(
          label: "Total Price",
          field: "totalPrice",
          data: dollar(index(local!datasubset.data, "totalPrice", {})),
          alignment: "RIGHT"
        )
      },
      value: local!pagingInfo,
      saveInto: local!pagingInfo
    )
  )
)

Notable implementation details

  • In order to correctly sort on the calculated column, we queried the entire data set, calculated the new value for each row, then paged and sorted on the result. If you were to only calculate the column for the current page, sorting on the calculated column would not work correctly across pages.
  • Since you must query all data and then loop over each item to calculate the additional data, this technique should not be used for a large amount of data, as the query and calculation may become slow. To further optimize this grid, only perform the calculation on every row when the user sorts by the calculated column, otherwise, simply calculate the data for the current page.
FEEDBACK