Configure a Chart Drilldown to a Grid

SAIL Recipes give you an opportunity to explore different interface design patterns. To learn how to directly use SAIL recipes within your interfaces, see Adapt a SAIL Recipe to Work with My Applications.

Goal

This recipe uses an employee data structure 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.

Display a chart with aggregate data from a data store entity, specifically the total number of employees for each department. Then add links to the chart slices so that when a user clicks on a department, the chart is replaced by a grid that displays all employees for that department with a link to return to the chart.

This design pattern is not recommended for offline interfaces because reflecting immediate changes in an interface based on user interaction requires a connection to the server.

image:SAIL_Recipes_chart_drilldown_chart.png image:SAIL_Recipes_chart_drilldown_grid.png

This scenario demonstrates:

  • How to add links to each slice of the pie chart
  • How to display a grid when the user clicks on a slice of the pie chart

Expression

load(
  /* The piechart and paging grid need different paging info values. */
  local!chartPagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: -1,
    sort: a!sortInfo(
      field: "department",
      ascending: true
    )
  ),
  local!gridPagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 20,
    sort: a!sortInfo(
      field: "title",
      ascending: true
    )
  ),
  local!selectedDepartment,
  with(
    local!chartDatasubset: a!queryEntity(
      entity: cons!EMPLOYEE_ENTITY,
      query: a!query(
        /* Aggregates data by department for the chart */
        aggregation: a!queryAggregation(
          aggregationColumns: {
            a!queryAggregationColumn(field: "department", isGrouping: true),
            a!queryAggregationColumn(field: "id", aggregationFunction: "COUNT"),
          }
        ),
        pagingInfo: local!chartPagingInfo
      )
    ),
    local!gridDatasubset: if(
      isnull(local!selectedDepartment),
      {},
      /* Returns a set of employees, filtered by department */
      a!queryEntity(
        entity: cons!EMPLOYEE_ENTITY,
        query: a!query(
          selection: a!querySelection(
            columns: {
              a!queryColumn(field: "firstName"),
              a!queryColumn(field: "lastName"),
              a!queryColumn(field: "title")
            }
          ),
          filter: a!queryFilter(field: "department", operator: "=", value: local!selectedDepartment),
          pagingInfo: local!gridPagingInfo
        )
      )
    ),
    a!sectionLayout(
      contents:{
        a!pieChartField(
          series: a!forEach(
            items: local!chartDatasubset.data,
            expression: a!chartSeries(
              label: fv!item.department,
              data: fv!item.id,
              links: a!dynamicLink(value: fv!item.department, saveInto: local!selectedDepartment)
            )
          ),
          showWhen: isnull(local!selectedDepartment)
        ),
        a!linkField(
          labelPosition: "COLLAPSED",
          links: a!dynamicLink(
            label: "Back to Chart",
            value: null,
            saveInto: {
              local!selectedDepartment,
              /* 
               * Reset the startIndex back to the first page when the user
               * changes the filter. Otherwise, the grid could error out.           
               */
              a!save(local!gridPagingInfo.startIndex, 1)
            },
           showWhen:not( isnull(local!selectedDepartment) )
          )
        ),
        a!gridField(
          label: local!selectedDepartment & " Employees",
          totalCount: local!gridDatasubset.totalCount,
          columns: {
            a!gridTextColumn(
              label: "First Name", 
              field: "firstName", 
              data: index(local!gridDatasubset.data, "firstName", {})
            ),
            a!gridTextColumn(
              label: "Last Name", 
              field: "lastName", 
              data: index(local!gridDatasubset.data, "lastName", {})
            ),
            a!gridTextColumn(
              label: "Title", 
              field: "title", 
              data: index(local!gridDatasubset.data, "title", {})
            )
          },
          value: local!gridPagingInfo,
          saveInto: local!gridPagingInfo,
          showWhen: not( isnull( local!selectedDepartment) )
       )
      }
    )
  )
)

Test it out

  1. Click a slice of the chart. The chart will be replaced with a grid that displays all employees for that department.
  2. Click the "Back to Chart" link. The grid will be replaced with the original chart.

Notable implementation details

  • The grid uses a separate paging info from the chart so that it can be sorted and paged independently from the chart.
  • Notice that when the user goes back to the chart to select a new filter, we’re always resetting the value of local!gridPagingInfo, ensuring that the user will see the first page for the new filter. This is necessary regardless of what the user has selected, so we ignore the value returned by the component and instead insert our own value.
  • For this example, the value of the department field is the department name, so it can be used both for display and as the value of the filter. If using a lookup instead, you would need to use the name as a display but save the id instead.
17.2
FEEDBACK