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 scenario demonstrates:

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

Create this pattern

The report builder allows you to create basic grids and charts to display data from a record type object or data store entity using an intuitive interface.

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

• The query that populates this chart will aggregate on the entire data set. To filter the data returned by the query before aggregating, see also: Aggregate Data from a Data Store Entity using a Filter and Display in a Chart