Documentation Visual SQL (beta) Start a Query

Start a Query in Visual Mode

Chartio makes it easy for users with minimal SQL knowledge to query their data sources. With Visual Mode, you can focus on simply adding the necessary columns and filters for your chart.

Toggle Visual Mode in Visual SQL

Columns

Add a column

To add a column, click +Add Column in the Columns section. To find and add a column from the list, you can do any of the following options:

  • Search for it using the search bar

  • Navigate through the schema browser

  • Click on a column from the table preview of the schema browser when you select a table

Keyboard shortcuts! To add multiple columns at a time without the schema browser closing between each selection, hold Command (for Mac) or Ctrl (for Windows) then click the columns you wish to add. This works when selecting columns from either the schema browser or the preview table.

Note: After adding a column, you might see some schemas, tables, or columns become disabled in the schema browser when you try to add another column. This means the currently selected columns aren’t connected to those disabled items. You may need to update your data source’s foreign keys to connect the tables or add another Query to query and merge additional columns.

Aggregation menu

Added columns appear in the Result Table, and each column has an Aggregation menu. The Aggregation menu is where you can choose to group by or aggregate that specific column, and the options displayed in the menu depend on the column’s data type. If you don’t want to group or aggregate a column, you can choose Unaggregated from the menu.

Need help understanding the different options in the aggregation menu? Check out our Group Operators and Aggregate Operators sections in our Operators page.

Each column in the Result Table has an Aggregation menu

Remove a column

To remove a column from your Query, hover over the column’s block and click the X that appears on the right-side of the block.

Filters

Only want to show data matching certain conditions? This is where filters will come in handy. For filters in a Query, you can filter by any available columns, even if they aren’t added to the Columns section.

Add a filter

You won’t be able to add a filter until you first add a column. Once you’ve added a column, you’ll see the Filters section appear to the right of the Columns section.

To add a filter, click +Add Filter in the Filters section.

  1. Select a column using the same process as described in Add a column.
  2. After choosing a column, you’re prompted to choose a filter operator. The operator options vary depending on the aggregation type (e.g., group or aggregate) and data type of your selected column.
  3. After choosing the operator, you’ll need to provide values within the selected column to filter your data. Type in values manually or select your Dashboard Controls, if any, from the list. Values can be literal strings, Dashboard Controls, Variables, etc.

All added filters are connected by an implicit AND operator. In other words, the data returned must match all the filter conditions. If you want to connect your filters with an OR operator (i.e., you only need to match some filter conditions), you’ll need to use a Filter Rows Action.

Need help understanding the different options for filter operators? Check out the Filter Operators section of our Operators page.

Remove a filter

To remove a filter, click the X on the right-side of the filter’s block.

Date filters and Relative Date Variables

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

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

To apply dynamic date filters, try using Chartio’s Relative Date Variables!

Table preview

If you want to see the kinds of data stored in a particular table, you can get a convenient table preview with the first ten rows of data. When adding a column or a filter, an interactive table preview will appear in the schema browser when you select a table from the list. Hovering over any of the table’s columns in the dropdown will highlight the column in the table preview and the dropdown navigation.

Column data types

Each column in the schema browser has an icon corresponding to the column’s data type.

Data Type Icon
Boolean Icon for Boolean data type
Date or Datetime Icon for Date and Datetime data types
Number Icon for Number data type
Text Icon for Text data type

Advanced Drawer

The Advanced Drawer is the collapsible section at the bottom of the Query editor. By default, the Drawer is collapsed. To expand the Drawer, click the down caret. To hide it again, click the up caret.

There are a few notable items in the Drawer:

Query sort

Your query is automatically sorted by the order you’ve added your columns. You can change the query sort in the Advanced Drawer.

To add a sort block, click +Add Sort, select the column to sort by, then choose the sort direction (Asc for Ascending, Desc for Descending).

To delete a sort block, click the X at the right-side of the block.

To edit a sort block:

  • Click the column name to choose a different column
  • Click Asc/Desc to change the sort direction

Note: You can only sort by columns you’ve added to the Columns section.

Limit rows

Please note that Chartio queries all rows in your data source to calculate your result set, but the number of rows returned for that result set is limited to the number you enter in this field. By default, to improve performance while creating and testing your chart, the row limit for the returned results of your query is set to 1,000. When you’re finished testing, you may want to increase this limit to ensure that your chart results are not truncated by this limit. Most Chartio subscriptions allow for up to 100,000 rows to be returned from original queries.

Refer to Chart Data Limits for more details on row limits.

SQL preview

When you’re using Visual Mode, Chartio auto-generates the SQL query to be executed in the background. If you’re curious, you can see a read-only view of the auto-generated SQL query from the Advanced Drawer.

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

Check the SQL preview in Visual Mode's Advanced Drawer