Aggregate Data from a Data Store Entity using a Filter and Display in a Chart

Interface patterns give you an opportunity to explore different interface designs. Be sure to check out How to Adapt a Pattern for Your Application.

Goal

Aggregate data from a data store entity, specifically the total number of employees for each title in the Engineering department, to display in a bar chart.

This recipe uses example data and objects created through the Use the Write to Data Store Entity Smart Service Function on an Interface recipe. Make sure you've completed that recipe in your environment first.

image of a bar chart with three bars

This scenario demonstrates:

  • How to use the report builder to aggregate data from a data store entity and display in a bar chart
  • How to modify the generated expression to add a default filter to the query

Using The Report Builder

screenshot of the report builder form with fields and a bar chart preview

  1. Open a new or empty interface object and select Report Builder from the list of templates.
  2. In the Source Constant field, select the EMPLOYEE_ENTITYconstant.
  3. Delete the firstName, lastName, and department columns so that only the id column remains.
  4. In the Add a field… dropdown, select title and click Add Field.
  5. Select the Group records by common fields option and select Count for the id column.
  6. Set the display name for the id column as Total.
  7. Select Bar Chart as the visualization.
  8. In the Chart Labels dropdown, select title.
  9. Click Generate.

You should see a chart that looks similar to this: image of a bar chart with 6 bars

Expression

To add a filter, enter the following expression after the aggregation parameter of a!query():

filter: a!queryFilter(field: "department", operator: "=", value: "Engineering"),

The comma at the end of this expression is included because pagingInfo is defined later.

Your final expression should look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
a!localVariables(
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: - 1,
    sort: {}
  ),
  local!datasubset: a!queryEntity(
    entity: cons!EMPLOYEE_ENTITY,
    query: a!query(
      aggregation: a!queryAggregation(
        aggregationColumns: {
          a!queryAggregationColumn(field: "id", aggregationFunction: "COUNT"),
          a!queryAggregationColumn(field: "title", isGrouping: true)
        }
      ),
      filter: a!queryFilter(field: "department", operator: "=", value: "Engineering"),
      pagingInfo: local!pagingInfo
    ),
    fetchTotalCount: false
  ),
  {
    a!barChartField(
      categories: {
        index(local!datasubset.data, "title", null)
      },
      series: {
        a!chartSeries(
          label: "Total",
          data: index(local!datasubset.data, "id", null)
        )
      },
      colorScheme: "OCEAN"
    )
  }
)

Notable implementation details

  • The expression for the filter is being passed into the filter parameter of a!query(). If you would like to filter using multiple constraints, use a!queryLogicalExpression() in the logicalExpression parameter instead.
  • In the final expression, the chart color scheme "OCEAN" has been added. You can change the color scheme or create your own custom color scheme.
Open in Github Built: Fri, Nov 04, 2022 (07:10:52 PM)
FEEDBACK