Table formats for Bar, Line, and Area charts

Bar, Area, and Line charts all accept a similar table format. Here’s a brief explanation of the types of table formats these charts accept, along with chart examples.

In the Visual SQL interface

One aggregated column and one grouped column

One aggregated column and one grouped column:

  • The first column (the grouped column) maps to the x-axis
  • The second column (the aggregated column) maps to the y-axis

Example Bar chart with two columns

One aggregated column and two grouped columns

To make this result set compatible with Bar charts, you need to use a Pivot Action on the second column (the second grouped column). This turns the second column’s values into column headers.

  • The first (grouped) column maps to the x-axis
  • After the Pivot, subsequent columns map to the y-axis and are grouped and labeled by their column name.

Example chart with two groups and one aggregate

Pivot with two aggregated columns

Multiple aggregated columns and one grouped column

  • The first column maps to the x-axis
  • Subsequent columns map to the y-axis and are grouped and labeled by their column name
  • Tooltip displays second through last column’s values when hovering on the chart preview

Example chart with one group and two aggregates

Number of lines in a Bar Line chart

For an example of Bar Line chart with multiple lines, check out our Dual axis Line charts article.


In the Data Explorer interface

Two columns

One measure grouped by one dimension

Chart, one measure & two dimensions

  • The first column maps to x-axis.
  • The second column maps to y-axis.

Sample table

Table, one measure & two dimensions

One measure grouped by two dimensions

Chart, one measure & two dimensions

  • Add a Pivot step, which allows the second column’s values to each become a new column. View an example of a Pivot here.
  • The first column maps to x-axis.
  • After the Pivot, subsequent columns map to the y-axis and are grouped and labeled by their column name.

Sample table (before Pivot step)

Table (before Pivot), one measure & two dimensions

Sample table (after Pivot step)

Table (after Pivot), one measure & two dimensions

Pivot with two dimensions

First, let’s take a look at an example chart before and after a pivot. Our example chart is grouped by two dimensions: Date and Customer. There are two aggregate columns: Amount and Count of Orders.

Before the Pivot step

After the Pivot step

Here’s what our chart looks like in the Data Explorer:

Chart in Data Explorer

The second dimension, Customer, is pivoted so the values are displayed/grouped with our column headers for our measures (Amount and Count of Orders). This type of pivot requires the following criteria:

  • Grouped by exactly two dimensions
  • Two or more measures (aggregates)
  • Second (pivoted) dimension must have a limited number of values, otherwise, it will produce a large number of columns. (The limit in Chartio is 200 columns per table chart, though you would likely want far fewer than that to produce for legibility.)

Multiple measures grouped by one dimension

Chart, multiple measures & one dimension

  • First column maps to x-axis.
  • Subsequent columns are mapped to the y-axis, and are grouped and labeled by their column name.
  • Tooltip displays second through last column on hover.

Sample table

Table, multiple measures & one dimension

Number of lines in a Bar Line Chart

When you create a bar line chart, it will default to using the last column as a line. This image demonstrates how Chartio defaults to choosing the last column as a line when you change from a bar chart to a bar line chart.

Bar vs. bar line chart

This doesn’t always make sense given the data you are trying to display. If for example, you want the last 2 columns of data to be lines, you can set this in your Chart Settings under the Series tab.

By changing the “Last X columns as lines” from 1 to 2, now you have two lines in your bar line chart.

Final result

You can update additional settings such as choosing custom colors for your lines and bar in the Chart Setting options.