Configure Charts Using Records

Charts in Appian can be configured using one of two methods:

• Set a record type as the data source and define fields to display on the chart.
• Perform your own query and define lists of data within the categories and series.

The easiest way to configure a chart is using a record type as the source, also known as a records-powered chart.

Configure a records-powered chart

To configure a records-powered chart:

1. On an interface, drag and drop a chart from the PALETTE:
   • Area Chart
   • Bar chart
   • Column Chart
   • Line Chart
   • Pie Chart
   • Scatter Chart
2. In the Component Configuration pane, go to the Data tab.
3. From Data Source, select Record Type.

4. Search and select your record type. The chart will automatically populate with data from the record type.

   

   Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

5. Customize the data in your chart by editing the chart:
   • Groupings
   • Measures
   • Links
   • Data limit and sorts

Configure chart groupings

Groupings allow you to determine how the data is grouped together in the chart. You can group by any record fields or related record fields.

For example, say you have a record type of all open support cases and you want to see how many open cases are associated with each employee. You can use grouping to group open cases by employee.

Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

Depending on the type of chart you use, the way you group your data and the complexity of your groupings will vary:

• Pie charts contain only one grouping: Primary Grouping. This grouping determines the labels for each slice of the pie chart.
• Bar and column charts allow adding multiple groupings. If only the Primary Grouping is used, the chart displays a single series of data and the axis shows the labels of the grouping results. When a Secondary Grouping is added, the secondary grouping shows as stacked or grouped bars.
• Line and area charts also support multiple groupings. Like bar and column charts, the Primary Grouping displays on the x-axis. We recommend using time series data for this grouping. A Secondary Grouping will show as multiple lines in a line and as stacked layers in an area chart.
• Scatter charts also support multiple groupings. The Primary Grouping is the main concept you want to examine in the chart. To refine the data, you can add a Secondary Grouping like a sales region or store identifier.

Add an alias

An alias is a short name that is used to reference the grouping field in other places in the chart, like in the sort or in a link.

By default, an alias is provided for you. You can modify the alias name as needed.

To edit the alias:

1. Click the pencil icon next to Primary Grouping.
2. Edit the value in the Alias field.

Apply time intervals

When configuring the grouping of a chart, you can apply a time interval on any Date, Date and Time, or Time field to group dates together and view trends at a summarized level.

For example, suppose you want to create a line chart with the number of cases created over time. If you group by the createdOn field without an interval, it will only group together data that occurred at the exact same time. Instead of grouping by the full date and time, it makes sense instead to group at a higher level, for instance by showing cases created by month.

To apply a time interval to a grouping:

1. Click the pencil icon next to the grouping.
2. In the Time Interval, select an interval. By default, "AUTO" is selected.

3. Select the Show time intervals with no data checkbox to display all date values between the first and last date, even when there are no values returned for some dates.

   For example, the chart below shows support cases created each month; however, there were no support cases created in August. Since the Show time intervals with no data checkbox is selected, that month still appears on the axis of the chart.

   

   Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

Format date fields

If you apply a time interval, you can also define how the data is formatted using a Format Value.

Some intervals have pre-defined formats that you can choose. If you select one of these formats, it will automatically localize the data based on language for the user viewing the chart. For example, if you select the long text version of date, the value in US English will show as April 13, 2021. However, a user with a French locale will see the same chart with the value 13 avril 2021.

You also have the flexibility to customize your date format. With the Use custom format option, you can use the following characters to format your date values:

You can combine these characters to create a variety of formats, and use special characters like / or - to separate your values. For example, d-m-yy will return 17-9-21, whereas dd mmmm yyyy will return 17 September 2021.

Format text and number fields

In addition to using Format Value to format your dates, you can also use this option to format text and integer fields used in your groupings.

For example, suppose the grouping field displayed on the chart is the status ID. The status labels exist in a constant, so the labels aren't available directly from the data source. Using the status ID as the grouping field would display on the chart like this:

To display the status labels instead, we can add an expression to the formatValue parameter of a!grouping():

1
2
3
4
5
6
7
8
9
a!grouping(
  field: recordType!Cases.field.statusId,
  alias: "status",
  formatValue: index(
    cons!MY_APP_STATUS_LABELS /* Status labels available in a constant*/
    fv!value,
    "Unknown Status"
  )
)

Using the expression above, the chart will instead display like this:

Configure chart measures

Measures determine how to calculate the values of a field in a chart. A measure can be calculated on any record field or related record field, but it should usually be a numeric field.

For example, say you have a record type that lists all customer requests and you want to see how many requests have been marked as "Success", "Fail", or "Exception". You can use grouping to display requests by status and use measure to count the number of requests per status.

Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

To configure a chart's measure:

1. In design mode, select the chart component.
2. In the Component Configuration pane, go to the Data tab.
3. For Data Source, select Record Type and search for the record type.
4. For Primary Grouping, select a field to group by.
5. For Measure, select a record field or a related record field to perform a calculation on.
6. Use the dropdown to choose the type of calculation to perform on the selected field.
7. Next to Measure, click the edit icon to add additional configurations:
   • In the Content tab
     • Under Label, enter the text to display in the legend or tooltip on the chart. If no label is defined, the label displays with the function name and field name.
   • In the Data tab:
     • Under Alias, create an alias for the results of the measure field. An alias can be used as a field to sort by and as a field in a chart link.
     • Click ADD FILTERS to determine which values are returned in the Measure field based on the specified conditions. Learn more about using filters in a measure.
     • For Format Value, you can choose a pre-defined format (Automatic, Integer, Decimal, Dollar, or Euro), or you can choose a custom format to style the measure value. Learn more about using format values.

If you use a bar, column, line, or area chart, you can display multiple measures if no Secondary Grouping field is provided.

When multiple measures display on a chart, each measure displays in chronological order on the chart and legend, and is assigned a different color based on the color scheme.

Add an alias

An alias is a short name that is used to reference the grouping field in other places in the chart, like in the sort or in a link.

By default, an alias is provided for you. You can modify the alias name as needed.

To edit the alias:

1. Click the pencil icon next to Measure.
2. Go to the Data tab and edit the value in the Alias field.

Using filters in a measure

By applying filters on a measure, you can determine which values are included in the measure's calculation.

For example, let's say you want to create a column chart that shows the number of orders associated with each customer, and you also want to see how many of those orders have a priority level of "Critical".

In your chart, you'd use the Customer record type to group by the name field and use the relationship with the Order record type to create two measures:

• The first measure will calculate the count of order Ids.
• The second measure will also calculate the count of order Ids and add a filter on the label field from the Priority record type so you only count orders that have a priorityLabel = Critical.

Using format values

Formatted numbers make it easier for users to scan and understand the data shown in a chart. You can use the Format Value option to format the numbers used in your measures. You can choose from several predefined options or use a custom format.

For example, a dashboard may contain a chart showing the total sales for each country. If you build the chart using the default options, the result might be similar to the following sample.

Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

While the data is accurate, it is hard to read and may not match what the user expects to see in a sales report.

By changing the format value to Dollar, the data labels will show the same data with a dollar sign before the number and rounded to the nearest cent.

Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

Configure chart links

A link in a chart can be used to drill down into a grid, or filter other components on an interface.

Chart links consist of a two elements:

• A Dynamic Link component.
• The variable fv!selection that contains a map with all the information about the current selected item. This variable allows you to dynamically pass chart data and save the current selected value to a local variable.

For example, suppose you have the following stacked column chart that displays a count of restaurants by region and type:

Scroll or drag to pan · ⌘+/⌘− to zoom · Esc to close

In expression mode, you'll see that the chart's link parameter is pre-populated with the a!dynamicLink() function, and the value parameter contains fv!selection.

To dynamically save the current selected values, you'll add a local variable, like local!selectedItem, and use it in the saveInto parameter:

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
a!localVariables(
  local!selectedItem,
  {
    a!columnChartField(
      data: recordType!Restaurants,
      config: a!columnChartConfig(
        primaryGrouping: a!grouping(
          field: recordType!Restaurants.fields.region,
          alias: "region"
        ),
        secondaryGrouping: a!grouping(
          field: recordType!Restaurants.fields.type,
          alias: "type"
        ),
        measures: a!measure(
          field: recordType!Restaurants.fields.id,
          function: "COUNT",
          alias: "id_count"
        ),
        link: a!dynamicLink(
          value: fv!selection,
          saveInto: local!selectedItem
        )
      )
    )
  }
)

Now, when you select the bar chart shown above, the following data is saved to the local variable:

Link considerations

• The data that is saved depends on the alias defined for each field. If you add a drilling link, the alias field is required.

• The saved value is always the unformatted result.

  For instance, suppose the Primary Grouping field is a boolean type, but the chart displays as Yes or No using the formatValue parameter. When using a link, the data in fv!selection for that field will be a boolean instead of a text.

  This applies when using some intervals that provide localized formatting too. For example, the DATE_TEXT interval returns the date as a text, like November 16, 2021. However, when you add a link for this field, the resulting value is the original date value 11/16/2021, not the localized text.

Configure the data limit and sorting

In addition to the groupings and measures, records-powered charts contain options for setting a batch size and sorting.

Data limit

The data limit determines how many unique groupings are on the chart. The data limit is most useful when using a single grouping.

For example, if you want a chart to display the top 10 products by their sales, the limit of 10 will return the correct results. The data limit also determines the combination of groupings. So, if both a primary and secondary grouping are used, a data limit of 100 would return a total of 100 values (10 primary grouping values x 10 secondary grouping values). For this reason, we recommend using the maximum limit of 5,000 when using multiple groupings.

To limit the batch size:

1. In the chart's Component Configuration pane, go to the Data tab.
2. For Data Source, select Record Type and search for the record type.
3. Enter a value in the Data Limit field.

Sort

To sort the record data in the chart, use the Sort field of the chart's Component Configuration pane. Sort is used to determine which values display first on the chart.

To configure a chart's sort behavior in design mode:

1. In the chart's Component Configuration pane, go to the Data tab.
2. For Data Source, select Record Type and search for the record type.

3. Locate the Sort field and click ADD SORT to configure the sort behavior.

   

4. For Sort By, choose the field to sort by:
   • Select Alias to sort by the alias of any groupings or measures in the chart. Then, select an alias from the dropdown.
   • Select Record Field to sort by a record field or related record field that is not referenced in the chart. Then, select a field from the dropdown.

   Note:  You can only select fields of type Number (Integer), Number (Decimal), Date, Date and Time, Time, or Boolean.

5. For Order, choose to sort in Ascending or Descending order.
6. If you're sorting on a line, bar, or column chart, click ADD SORT to add multiple sort fields.

To configure the sort behavior in expression mode, use the a!sortInfo function.

Examples

Let's look at a few examples to understand how to configure a chart to use a record type as the source.

Stacked column chart of cases by status and priority

In this example, let's say you have a Case record type that has a many-to-one relationship with the Status record type, and you want to see how many cases have a status of Open, In Progress, or Closed. Within this chart, you also want to see a breakdown of case priority.

An easy way to display this data is using a stacked column chart, and you can use the Case record type as the source. Since the Case record type has a relationship with the Status record type, you can easily reference related record fields from the Status record type in the chart. To display the case status in the correct order of Open, In Progress, and Closed, you can also sort by the id field from the Status record type.

Here's what the chart looks like:

To experiment with examples, copy and paste the expression into an interface object.

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
{
  a!columnChartField(
    data: recordType!Case,
    config: a!columnChartConfig(
      primaryGrouping: a!grouping(
        field: recordType!Case.relationships.status.fields.label,
        alias: "status"
      ),
      secondaryGrouping: a!grouping(
        field: recordType!Case.fields.priority,
        alias: "priority"
      ),
      measures: {
        a!measure(
          function: "COUNT",
          field: recordType!Case.fields.id,
          alias: "id_count"
        )
      },
      sort: {
        a!sortInfo(
          field: recordType!Case.relationships.status.fields.id
        )
      },
      dataLimit: 100
    ),
    label: "Column Chart",
    stacking: "NORMAL",
    showLegend: true,
    showTooltips: true,
    labelPosition: "ABOVE",
    colorScheme: "SUNSET",
    height: "MEDIUM",
    xAxisStyle: "STANDARD",
    yAxisStyle: "STANDARD",
    refreshAfter: "RECORD_ACTION"
  )
}

Bar chart of employees per department

In this example, you'd like to count the number of employees in the company grouped by their department. To easily compare the number of employees in each department, you can use a bar chart.

Here's what the chart looks like:

To experiment with examples, copy and paste the expression into an interface object.

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
{
  a!barChartField(
    data: recordType!Employee,
    config: a!barChartConfig(
      primaryGrouping: a!grouping(
        field: recordType!Employee.fields.department,
        alias: "department_primaryGrouping"
      ),
      measures: {
        a!measure(
          function: "COUNT",
          field: recordType!Employee.fields.id,
          alias: "id_count_measure1"
        )
      },
      sort: {
        a!sortInfo(
          field: "id_count_measure1"
        )
      },
      dataLimit: 100
    ),
    label: "Employees per Department",
    labelPosition: "ABOVE",
    stacking: "NONE",
    showLegend: true,
    showTooltips: true,
    colorScheme: "OCEAN",
    height: "MEDIUM",
    xAxisStyle: "STANDARD",
    yAxisStyle: "STANDARD",
    refreshAfter: "RECORD_ACTION"
  )
}

Pie chart of sales by product area

Now suppose you have a record type that contains a list of sales for each product. A pie chart is a great way to view the contributions made by each product area.

Here's what the chart looks like:

To experiment with examples, copy and paste the expression into an interface object.

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
a!pieChartField(
  label: "Sales by Product Area",
  data: recordType!Sales,
  config: a!pieChartConfig(
    primaryGrouping: a!grouping(
      field: recordType!Sales.fields.productArea,
      alias: "productArea"
    ),
    measures: a!measure(
      function: "SUM",
      field: recordType!Sales.fields.salesRevenue,
      alias: "sum_salesRevenue"
    ),
    dataLimit: 100,
    sort: a!sortInfo(
      field: "sum_salesRevenue",
      ascending: false
    )
  ),
  labelPosition: "ABOVE",
  height: "SHORT",
  showDataLabels: true,
  showAsPercentage: true,
  seriesLabelStyle: "LEGEND",
  colorScheme: "RAINFOREST",
  style: "DONUT"
)

Line chart of cases per month for each office

Suppose you'd like to create a chart that displays the total number of cases created within each office over time. When displaying trends over time, a line chart is typically a good choice.

Here's what the chart looks like:

To experiment with examples, copy and paste the expression into an interface object.

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
a!lineChartField(
  data: recordType!Case,
  config: a!lineChartConfig(
    primaryGrouping: a!grouping(
      field: recordType!Case.fields.createdOn,
      alias: "createdOn_month_primaryGrouping",
      interval: "MONTH_TEXT"
    ),
    secondaryGrouping: a!grouping(
      field: recordType!Case.fields.office,
      alias: "office"
    ),
    measures: {
      a!measure(
        function: "COUNT",
        field: recordType!Case.fields.id,
        alias: "id_count"
      )
    },
    dataLimit: 5000,
    showIntervalsWithNoData: true
  ),
  label: "Cases by Month per Office",
  showTooltips: true,
  colorScheme: "RAINFOREST",
)

Area chart of sales per month for each region

Let's say you want to create a chart that shows which sales regions generated the most sales revenue this past year.

Line charts and area charts are both great for showing trends over time; however, an area chart would be a better fit for this use case since you want to see a part-to-whole relationship (which region's sales makes up most of the total sales). Since the total number of sales generated this past year is not the key performance indicator, you may choose to create an area chart that shows the percent to total ratio of each region's sales.

Here's what the chart looks like:

To experiment with examples, copy and paste the expression into an interface object.

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!areaChartField(
    data: recordType!Order,
    config: a!areaChartConfig(
      primaryGrouping: a!grouping(
        field: recordType!Order.fields.orderDate,
        alias: "orderDate_month_primaryGrouping",
        interval: "MONTH_DATE"
      ),
      secondaryGrouping: a!grouping(
        field: recordType!Order.relationships.salesRegion.fields.name,
        alias: "name_secondaryGrouping"
      ),
      measures: {
        a!measure(
          function: "COUNT",
          field: recordType!Order.fields.orderId,
          alias: "orderId_count_measure1"
        )
      },
      dataLimit: 100,
      showIntervalsWithNoData: true
    ),
    label: "Area Chart",
    labelPosition: "ABOVE",
    stacking: "PERCENT_TO_TOTAL",
    showLegend: true,
    showTooltips: true,
    colorScheme: "CLASSIC",
    height: "MEDIUM",
    xAxisStyle: "STANDARD",
    yAxisStyle: "STANDARD",
    refreshAfter: "RECORD_ACTION"
  )
}

Scatter chart of items per order compared to order cost

Scatter charts show relationships in a dataset, so you need to define two measures: one for the horizontal (x) axis and one for the vertical (y) axis. For example, if you are reporting on retail sales, you can look for a relationship between the number of items being purchased and the total cost of an order.

The scatter chart for these measures may look like this example, showing a positive correlation between the number of items and order total.

Here's what the chart looks like:

To experiment with examples, copy and paste the expression into an interface object.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
a!scatterChartField(
  data: recordType!Order,
  primaryGrouping: a!grouping(
    field: recordType!Order.fields.orderNumber,
    alias: "orderNumber_primaryGrouping"
  ),
  xAxisMeasure: a!measure(
    function: "AVG",
    field: recordType!Order.fields.itemQty,
    label: "Quantity ordered",
    alias: "itemQty_avg_measure1"
  ),
  yAxisMeasure: a!measure(
    function: "AVG",
    field: recordType!Sales Order.fields.totalDue,
    label: "Order total",
    alias: "totalDue_avg_measure1"
  ),
  label: "Total cost by number of items in order",
)

Feedback