Aggregate Data from a Data Store Entity by Multiple Fields 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 by multiple fields, specifically the total number of employees for each title in each department, to display in a stacked column 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 that recipes has been built first in order to see data in this recipe.

This implementation can be used more generally for any chart with multiple series.

This scenario demonstrates:

  • How to aggregate data from a data store entity by multiple fields and display in a column chart
  • How to modify the generated expression to group multiple data points together in one chart series

Expression

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
load(
  /* 
  * Paginginfo is bringing back all employee data in this case and sorting by
  * department.
  */
  local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: -1,
    sort: a!sortInfo(
      field: "department",
      ascending: true
    )
  ),
  with(
   /* 
    * local!datasubset is pulling data from the employee data store entity 
    * from the entity backed record tutorial The resulting data value looks 
    * like this data: { {department:Engineering, title:Director id: 1}...}
    *
    * To use your data, change the constant from cons!EMPLOYEE_ENTITY to one of
    * your own. Then replace the fields in the queryAggregationColumns to a
    * relevant datapoint from your data store entity
    */
    local!datasubset: a!queryEntity(
      entity: cons!EMPLOYEE_ENTITY,
      query: a!query(
        aggregation: a!queryAggregation(
         aggregationColumns: {
          a!queryAggregationColumn(field: "department", isGrouping: true),
          a!queryAggregationColumn(field: "title", isGrouping: true),
          a!queryAggregationColumn(field: "id", aggregationFunction: "COUNT")
         }
        ),
        pagingInfo: local!pagingInfo
      )
    ),
    /* 
    * local!categories is returning all department values. local!uniqueDepartments
    * is reducing the list down to only unique values. This populates the column
    * chart categories.
    *
    * To use your data, change the second parameter in the index function from
    * "department" to whatever data point is going to be your category
    */
    local!categories: index(local!datasubset.data, "department", {}),
    local!uniqueCategories: union(local!categories, cast(typeof(local!categories), {})), 
    /* 
    * local!labels and local!uniqueLabels provide the name of each piece in the
    * column.
    *
    * To use your data, change the second parameter in the index function from
    * "title" to whatever data point is going to be the label of each piece
    */
    local!labels: index(local!datasubset.data, "title", {}),
    local!uniqueLabels: union(local!labels, cast(typeof(local!labels), {})),
    /* 
    * local!series puts everything together for the column chart. It runs through
    * two a!forEach() loops. The first loop will lopp over a list of categories to
    * find matching data, while the second a!forEach() loop will find all matching
    * datapoints in that category.
    *
    * To use your data, change the second parameter in the index function at the
    * end of the second a!forEach() loop. Replace "id" to whatever data point is 
    * providing the numeric value to count on in your dataset.
    */
    local!series:a!forEach(
        items:local!uniquelabels,
        expression: with(
          local!label:fv!item,
           a!chartSeries(
            label: local!label,
            /* Loops over list of categories to find each datapoint that matches  
             * the series label and the category. This will ensure that the 
             * datapoints are in the correct order to display in the chart.                       
             */
            data: a!forEach(
              items:local!uniqueCategories,
              expression: with(
              /* Find all datapoints that match both the category and chart series label   */
                local!intersection: intersection(
                  where(local!categories=cast(typeof(local!categories), fv!item), 0),
                  where(local!labels=cast(typeof(local!labels), local!label), 0)
                ),
                if(
                length(local!intersection)=0,
                /* If there is no datapoint for this category-label pair, return 0 
                 * so that all subsequent points are in the correct order with the 
                 * categories.      
                 */
                0,
                index(index(local!datasubset.data, "id", {}), local!intersection, 0)
                )
              )
            )
          )
        )
     ),
    a!columnChartField(
      categories: local!uniqueCategories,
      series: local!series,
      xAxisTitle: "Departments",
      yAxisTitle: "Number of Employees",
      stacking: "NORMAL"
    )
  )
)

Test it out

  1. Hover over each of the stacked department data to see a breakdown of the title of employees in that department

Notable implementation details

  • Most of the expression before the column chart can be used more generally than this example. They can be used to generate series for bar, column, or line charts and with different data sets, though they would need to be modified to work with CDT fields.
  • The query that populates this chart will aggregate on the entire data set. To filter the data returned by the query before aggregating, see: Aggregate Data from a Data Store Entity using a Filter and Display in a Chart
FEEDBACK