Creating a QL chart

QL charts have the same general settings and section settings available as charts based on a dataset. Only certain measure settings are supported for chart fields.

To create a QL chart:

  1. Go to an existing database connection.
  2. Make sure SQL query access levelAllow subqueries in datasets and queries from charts is enabled.
  3. In the top-right corner, click Create QL chart.
  4. Use the Query tab to enter a query in the flavor of SQL native to the database you are accessing.
  5. In the bottom-left corner, click Start.

After the query runs, a visualization of your data will be displayed.

Query example for a ClickHouse database
SELECT Category, Month, ROUND(SUM(Sales))FROM samples.SampleLiteWHERE Category in {{category}} -- a variable used in the selectorGROUP BY Category, Month -- grouping by category and monthORDER BY Category, Month -- sorting by category and month

Adding selector parameters

In QL charts, you can control selector parameters from the Parameters tab in the chart editing area and use the Query tab to specify a variable in the query itself in {{variable}} format.

To add a parameter:

  1. Go to the Parameters tab when creating a chart.

  2. Click Add parameter.

  3. Set the value type for the parameter, e.g., date-interval.

  4. Name the parameter, e.g., interval.

  5. Reset the default values, e.g., 2017-01-01 — 2019-12-31.

    image

Intervals

You can use the date-interval and the datetime-interval type parameters in query code only with the _from and _to postfixes. For example, for the interval parameter set to 2017-01-01 — 2019-12-31, specify:

  • interval_from to get the start of the range (2017-01-01).
  • interval_to to get the end of the range (2019-12-31).
Sample query
SELECT toDate(Date) as datedate, count ('Order ID')FROM samples.SampleLiteWHERE {{interval_from}} < datedate AND datedate < {{interval_to}}GROUP BY datedateORDER BY datedate

Substituting parameter values in a QL chart query

Parameter values from a selector arrive to a QL chart as a:

  • Single value if one element is selected.
  • Tuple if multiple values are selected.

If the query has the IN operator specified before a parameter, the substituted value is always converted into a tuple. A query like this will run correctly if you select one or more values.

Sample query with the IN operator
SELECT sum (Sales) as Sales, CategoryFROM samples.SampleLiteWHERE Category in {{category}}GROUP BY CategoryORDER BY Category

If the query has = before a parameter, the query will only run correctly if a single value is selected.

Sample query with the = operator
SELECT sum (Sales) as Sales, CategoryFROM samples.SampleLiteWHERE Category = {{category}}GROUP BY CategoryORDER BY Category

Null choice in selector and parameters

If a selector has no value selected and no default value is set for a parameter, a null value is provided to a query. In this case, all values will be selected in dataset-based charts, and the filter for the relevant column will disappear when generating a query.

To enable a similar behavior in QL charts, you can use a statement like this in your query:

ANDCASE    WHEN LENGTH({{param}}::VARCHAR)=0 THEN TRUE    ELSE column IN {{param}}END