Aggregate Data from a Data Store Entity 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 in a given department, to display in a pie 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 pie chart with 5 slices

This scenario demonstrates:

  • How to use the report builder to aggregate data from a data store entity and display in a pie chart.

Using the Report Builder

screenshot of the report builder form with fields and a pie 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 and lastName columns so that only the department and id columns remain.
  4. Select the Group records by common fields option and select Count for the id column.
  5. Select Pie Chart as the visualization.
  6. In the Chart Labels dropdown, select department.
  7. Click Generate. You should see the following expression in the design pane on the left-hand side:

Let's take a look at the expression to see how it relates to what we configured.

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: "department", isGrouping: true),
          a!queryAggregationColumn(field: "id", aggregationFunction: "COUNT")
        }
      ),
      pagingInfo: local!pagingInfo
    ),
    fetchTotalCount: false
  ),
  {
    a!pieChartField(
      series: {
        a!forEach(
          items: local!datasubset,
          expression: a!chartSeries(
            label: index(fv!item, "department", null),
            data: index(fv!item, "id", null)
          )
        )
      }, 
      colorScheme: "CLASSIC"
    )
  }
)

The field selected in the "Chart Labels" configuration determines the labels that are displayed with each pie slice. This is the same as the label configuration in a!chartSeries(). The field selected in the "Chart Data Series" configuration determines the data used to create the pie chart slices. This is the same as the data parameter in a!chartSeries().

Notable implementation details

Open in Github Built: Fri, Nov 04, 2022 (07:10:52 PM)
FEEDBACK