Dataset Interface

The Dataset Interface is where you build your queries. Add a dataset for each separate query you want to include in your chart. If you’d like to join data from multiple data sources, you can do so by querying each source in a separate dataset.

Dataset Features

Preview Table Data

Not sure which table you need to use? Click the table icon next to a table name to quickly preview its first 100 rows.

For compatible data sources; excludes Google Analytics.

Foreign Key Creator

To query columns from two different tables in the same dataset, you’ll need a Foreign Key to connect them. Add a new foreign key without leaving the Data Explorer by hovering over the table and clicking the key icon. To learn more about foreign keys, see our documentation.

Hover over a table to open the Foreign Key icon

Building a query to chart total payments per month.

Measures and Dimensions

Chartio automatically sorts your columns into two groups: Measures and Dimensions. Generally, you’ll want to drag Measures columns to the Measures field, and likewise for Dimensions. To use a Measure as a Dimension, see our documentation on Bucketing Measures.

Measures

Typically refer to quantitative data, such as number of units sold, number of unique visits, profit and so on. In the context of data visualization, measures map to the Y axis of a chart.

Drag a Measure column to the Measures field and click the column to select an aggregation.

Dimensions

Refer to categorical data, such as state, gender, product name or units of time (e.g., day, week, month). Generally, dimensions are used to group quantitative data into useful categories (e.g., number of units sold by state) and typically map to the X axis.

For information about date formatting, see here.

Filters

Dragging a column to the Filters field in the Data Explorer generates a list of filtering customization options. These options vary depending on whether the column is a Measure, Dimension (Non-Date), or Dimension (Date).

OR filters

By default, filters in Interactive Mode each have an AND between them. This means that the each row included in your result set must match every filter condition.

However, there are certain situations where your query results only need to match some of the filter conditions. In those cases, you can use an OR filter.

Once there’s at least one filter in the Filters section, an OR button will appear below the Filter box. Click the OR button and drag your column to the new OR field.

Every condition in the same filter field will have an AND between it. You may add as many new OR filter fields as needed.

View examples

OR filter

Date filters

Chartio’s Interactive Mode date filters don’t necessarily follow SQL behavior. We have created standard date filters to ensure date filtering behavior is consistent for both dates and datetimes across all of our supported data sources.

  • between is exclusive, which means the end date is not included
  • between and including is inclusive, which means the end date is included

Date filters

Relative date filters

To apply custom date filters, you can use Chartio’s relative date variables. For more information, see our documentation.

Relative date filters

Edit Variable Values

Use the Edit Variable Values feature to customize the dashboard variable values that you’re using in chart filters or in the Data Pipeline. This allows you to preview your chart’s data with non-default variable values.

Note: the edited variable values are only applied to the current Data Explorer session. Values revert to defaults when navigating away from the Data Explorer.

Interactive vs. SQL Mode

Building queries in Interactive Mode and then switching to SQL Mode is a great way to generate the basic structure of return values and even joins, before editing the query with more specific needs.

Interactive Mode

Any columns dropped into the pane while in Interactive Mode automatically generates an underlying SQL statement. To view the generated SQL, click the Preview SQL button.

SQL Mode

SQL mode allows you to write custom SQL against your database. SQL Mode can be useful for complex queries that aren’t feasible in Interactive Mode due to complex joins, subqueries, etc.

Autocomplete

Autocomplete column and table names using the keyboard shortcut Control+Space.

Autocomlete column and table names using keyboard shortcuts

Query History

Chartio saves the last 100 saved queries in each SQL Mode dataset. To access, click the History button in the top bar of your SQL Mode dataset.

Query History

Preview SQL

To view the SQL being generated for your Interactive Mode query, click the Preview SQL button. The SQL is updated in realtime as changes are made to your Interactive Mode query.

This is also a great way to learn some basic SQL—simply edit your Interactive Mode chart and watch how the generated query changes.

Multiple datasets

See our documentation on Merging Datasets.