Configure Charts Using Records

This page explains how to configure a chart that uses a record type as its source.

Overview

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

  • Define a record type as the source and select fields to display on the chart.
  • Provide a list of data directly to the categories and series parameters in the chart.

If the source of your chart is a record type that uses a database as the source, or a record type that has sync enabled, use the record type in the configuration. Once you select the record type, it's easy to reference record fields or related record fields in the chart's labels or calculations.

If the source of your chart is not a record type, or you need to perform advanced calculations or manipulations on the data prior to displaying it on your chart (e.g. calculate a running sum after querying the results), use the categories and series parameters to construct the chart using an expression. See examples for configuring charts using categories and series directly in the component pages for Bar, Column, Line, and Pie charts.

To easily configure your charts, Appian recommends using a record type as the source. To configure a chart to use record data, you will:

Configure a chart to use record data

Appian makes it quick and easy to select a record type as the source of your chart. Simply choose Record Type as the source of your data and search for your record type. The selected record type determines which fields are available to display on the chart.

Once you select the source, there are two key configurations needed to display your record data: groupings and a measure. The combination of these configurations determine what data to query from the record type and how to display the results on the chart.

To configure a chart to use a record type as its source:

  1. On an interface, drag and drop one of the four chart types (Bar, Column, Line, or Pie) from the PALETTE.
  2. From Data Source, select RECORD TYPE and search for your record type.
  3. Click FILTER RECORDS to apply any filters if necessary.
  4. Use the Primary Grouping and Secondary Grouping fields to configure the chart's groupings.
  5. Use the Measure field to determines how to calculate the values of a field in a chart.
  6. Use the Sort and Data Limit fields to define how the chart sorts and displays the record data.

Depending on the chart you choose, you can configure additional chart properties like X-Axis and Y-Axis Titles, Reference Lines, Color Scheme, and more.

/chart design view

Once you select a source record type, record fields will auto-populate in design view for the primary grouping and the measure. Each of these parameters define a key aspect of the design for your chart.

Configure chart groupings

Groupings allow you to determine how the data is grouped together in the chart. You can group by any field in the record type, including 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.

Chart with one grouping

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 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 chart.

    Chart with one grouping

When there are multiple measures in your bar, column, or line chart, you cannot add a Secondary Grouping.

To configure a chart's groupings in Expression Mode, see the Grouping Component or the examples below.

Using intervals

When configuring the grouping of a chart, you can also use a date interval to group dates together and view trends at a summarized level.

For example, suppose you would like to display a line chart with the number of cases created over time. However, the only relevant field you have is createdOn, which is a Date and Time field. If you chose to 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.

In this example, you can add an interval that will group the data by the month. There are several intervals available, and each provides a different perspective on your data. See the Grouping Component for a list of available interval values.

To choose an interval in Design Mode, click the pencil icon next to your grouping. If you are grouping by a Date, Date and Time, or Time field, you can select an interval from the Time Interval dropdown. When you choose a time interval other than "AUTO", the Show time intervals with no data checkbox is also selected 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.

After you choose an 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:

Unit of Time Character Valid Combinations
Year y yy, yyyy
Month M M, MM, MMM, MMMM, MMMMM
Day d d, dd, ddd
Hour h h, hh
Minute m mm
Second s ss
Day of Week E EEE, EEEE
AM / PM a a
Timezone z z, zzzz

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.

Using format values

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:

/chart with statusId

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:

/chart with status labels

Configure chart measures

A measure determines how to calculate the values of a field in a chart. A measure can calculate on any field in the record type, including related record fields, 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.

Chart measure

To configure a chart's measure:

  1. In Design Mode, select the chart component.
  2. Locate the Measure field on the chart's Component Configuration.
  3. Select a record field or a related record field to perform a calculation on.
  4. Use the dropdown to choose the type of calculation to perform on the selected field.
  5. Click the edit icon next to the measure to add additional configurations:
    • 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.
    • 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.

If you use a bar, column, or line 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.

To configure a chart's measure in Expression Mode, see the Measure Component or the examples below.

Using filters in a measure

If your record type has data sync enabled, you also have the flexibility to add filters to your measures. By applying filters on a record field or a related record field, 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.

Column chart with number of total orders and number of critical orders

Now, let's say you want to create another report that shows the number of orders associated with each customer, and how many customer orders include at least one out-of-stock item.

In this example, 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 status field from the Order Item record type so you only count orders that have a status = Out-of-stock.

Column chart with number of customer orders that include out of stock items

In this example, the second measure counts all orders with at least one item marked as "Out-of-stock". This is because there is a one-to-many relationship between the Order record type and the Order Item record type.

When you filter on a field from the related record type (the "many" side of the relationship), the filter returns all records from the base record type (the "one" side of the relationship) that have at least one related record that meets the filter condition.

See Aggregating on related record fields for more examples using related record fields in your filtered aggregations. See Filters to learn more about configuring filters in Basic or Expression mode.

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 dynamic link and the variable fv!selection that contains the current selected values.

The fv!selection variable returns a map with all information about the current selected item, which allows you to dynamically pass chart data. To save the current selected values, you'll create a local variable to store the values.

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

/drilling_link

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:

/drilling_link2

Notice that the data that is saved depends on the alias defined for each field. If you add a drilling link, the alias field is required. To add an alias, click the pencil icon next to Primary Grouping or Measure and enter a value in the Alias field.

Additionally, 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.

Once you store the currently selected chart data to a local variable, it's easy to use the selection for filtering, drilling, or other interactions on the page. See the recipe on how to configure a chart drilldown to a grid for an example on how to use the selection.

Configure the data limit and sorting

In addition to the groupings and measures, charts that use a record type as a source contain options for setting a batch size and sorting.

Data limit

To limit the batch size, enter a value in the Data Limit field of the chart's Component Configuration. 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.

Sort

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

To configure a chart's sort behavior in Design Mode:

  1. Locate the Sort field on the chart's Component Configuration.
  2. Click ADD SORT to configure the sort behavior.

    sort options

  3. 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.

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

  4. For Order, choose to sort in Ascending or Descending order.
  5. 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 the chart to display in a variety of scenarios.

Column chart with cases by priority

In this example, let's say you have a Case record type that has a many-to-one relationship with the Priority record type, and you want to see how many open cases have a priority level of Low, Medium, or High.

An easy way to display this data is using a column chart, and you can use the Case record type as the source. Since the Case record type has a relationship with the Priority record type, you can easily reference related record fields from the Priority record type in the chart. To display the priority status in the correct order of Low, Medium, and High, you can also sort by the id field from the Priority record type.

Here's what the chart looks like:

/related-record-column-chart

To configure this chart in Design Mode:

  1. Open a new or empty interface object.
  2. From the PALETTE, drag a COLUMN CHART component into the interface.
  3. From Data Source, select RECORD TYPE and search for the Case record type.
  4. For Primary Grouping, remove the existing field. Then, use the dropdown to hover over the priority relationship and select the label field from the Priority record type. This will display each column as a different priority (Low, Medium, or High).

    /column-chart-design-view

  5. Click the pencil icon next to Measure to configure the aggregation:
    1. For Label, enter Cases.
    2. For Aggregation Function, select Count of.
    3. For Field, choose the Id field.
    4. For Filters, click + ADD FILTERS.
    5. Set the filter condition to recordType!Case.fields.status = "Open and click OK. Now, only cases with a status of "Open" will be counted.
  6. Return to the Column Chart Component Configuration.
  7. For Sort, click ADD SORT.
  8. For Sort By, select Record Field.
  9. From the dropdown, hover over the priority relationship and select the id field from the Priority record type. This will sort the columns by the priority Id so they display in order of Low, Medium, and High.

    If you were to sort by the alias from the primary grouping, it would sort the columns in alphabetical order (Medium, Low, High). If you were to sort by the alias from the measure, it would sort the columns by the number of cases, which will change day-to-day.

  10. For Order, select Descending.

    Design View config for the column chart

To configure this chart in Expression Mode:

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
{
  a!columnChartField(
    data: recordType!Case,
    config: a!columnChartConfig(
      primaryGrouping: a!grouping(
        field: recordType!Case.relationships.priority.fields.label,
        alias: "label_primaryGrouping"
      ),
      measures: {
        a!measure(
          label: "Cases",
          function: "COUNT",
          field: recordType!Case.fields.id,
          filters: a!queryLogicalExpression(
            operator: "AND",
            filters: {
              a!queryFilter(
                field: recordType!Case.fields.status,
                operator: "<>",
                value: "Closed"
              )
            },
            ignoreFiltersWithEmptyValues: true
          ),
          alias: "countId"
        )
      },
      sort: {
        a!sortInfo(
          field: recordType!Case.relationships.priority.fields.id
        )
      },
      dataLimit: 100
    ),
    label: "Support Cases by Priority",
    stacking: "NONE",
    showLegend: true,
    showTooltips: true,
    labelPosition: "ABOVE",
    colorScheme: "SUNSET",
    height: "MEDIUM",
    xAxisStyle: "STANDARD",
    yAxisStyle: "STANDARD",
    refreshAfter: "RECORD_ACTION"
  )
}

Pie chart with 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:

/pie chart design view2

To configure this chart in Design Mode:

  1. Open a new or empty interface object.
  2. From the PALETTE, drag a PIE CHART component into the interface.
  3. From Data Source, select RECORD TYPE and search for the Sales record type.
  4. For Primary Grouping, remove the existing field. Then, use the dropdown to select the productArea field. This will display each product as a slice of the pie.
  5. For Measure, select Sum of as the aggregation function, and choose the salesRevenue field. The sum of sales revenue will determine the size of each pie slice.

    /pie chart design view1

To configure this chart in Expression Mode:

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"
)

Stacked column chart of employees

In this example, you'd like to count the number of employees in the company grouped by their region and role. Since this can best be displayed as a stacked chart, you'll need to use multiple groupings. You should also only display active employees using a filter.

Here's what the chart looks like:

/stacked column chart2

To configure this chart in Design Mode:

  1. Open a new or empty interface object.
  2. From the PALETTE, drag a COLUMN CHART component into the interface.
  3. From Data Source, select RECORD TYPE and search for the Employee record type.
  4. For Primary Grouping, remove the existing field. Then, use the dropdown to select the region field. This will display each column as a different region.
  5. For Secondary Grouping, click ADD GROUPING and choose the role field. This will make the field labels display as stacked columns in the chart.
  6. For Measure, select Count of as the aggregation function, and choose the Id field.

    stacked column 1

  7. Click FILTER RECORDS to filter the record data to only display active employees.

To configure this chart in Expression Mode:

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
a!columnChartField(
  data: a!recordData(
    recordType: recordType!Employee,
    filters: a!queryFilter(
      field: recordType!Employee.fields.status,
      operator: "=",
      value: true
    )
  ),
  config: a!columnChartConfig(
    primaryGrouping: a!grouping(
      field: recordType!Employee.fields.region,
      alias: "region"
    ),
    secondaryGrouping: a!grouping(
      field: recordType!Employee.fields.role,
      alias: "role"
    ),
    measures: a!measure(
      function: "COUNT",
      field: recordType!Employee.fields.id,
      alias: "id_count"
    ),
    dataLimit: 5000
  ),
  label: "Column Chart",
  stacking: "NORMAL",
  showLegend: true,
  showTooltips: true,
  colorScheme: "MIDNIGHT"
)

Line chart with grouping by month per office

Suppose you'd like to create a chart that displays the total number of cases created within each office over time. If you'd like to see long term trends, it makes sense to use an interval to group the data by month.

Here's what the chart looks like:

/date grouping example2

To configure this chart in Design Mode:

  1. Open a new or empty interface object.
  2. From the PALETTE, drag a LINE CHART component into the interface.
  3. From Data Source, select RECORD TYPE and search for the Support Case record type.
  4. Click the pencil icon next to Primary Grouping to configure the grouping and formatting:
    1. For Field, select createdOn.
    2. For Time Interval, select Month.
    3. Select the Show time intervals with no data checkbox to display any months when there were no support cases submitted.
    4. For Format Value, use a pre-defined format to display the full month and year. For example, April 2021.

    /date grouping example

  5. For Measure, select Count of as the aggregation function, and choose the Id field.

To configure this chart in Expression Mode:

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",
)
Open in Github Built: Fri, Dec 03, 2021 (03:08:11 PM)

On This Page

FEEDBACK