Documentation Visual SQL 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.

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.

When you add a column to the Query, you’ll notice each column has an Aggregation menu to left of the column’s name. 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.

Select a column using the same process as described in Add a column.
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.
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.

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

OR filters

By default, filter conditions in Visual Mode each have an AND between them, which means the data returned must match all the filter conditions. If you only need your results to match some filter conditions, you can change the operator to an OR by clicking the & button that connects the conditions. You can change it back to AND by clicking the button again.

But maybe you need more complex combinations of filter conditions with nested conditions and a mix of ANDs and ORs. To create a new subgroup of conditions, hover over the condition you’d like to include in the subgroup and click the conjunction that appears to the right of it. You’ll then see the option to add more conditions to your new subgroup.

For example, let’s say you wanted to set up the following filter conditions:

(Status = Signed AND Signature Date is null) OR (Status = Prepared AND Signature Date is not null)

This is how you’d do it in a Visual Mode Query:

Did you accidentally add a condition to the wrong subgroup? You can drag and drop the condition to the correct position (or use the Undo feature to quickly undo it)!

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
Date or Datetime	/
Number
Text

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
Limit rows
SQL preview

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. The auto-generated SQL query uses the appropriate syntax for the underlying data source type. 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 Visual Mode chart and watch how the generated query changes.

Check the SQL preview in Visual Mode's Advanced Drawer