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 level → Allow subqueries in datasets and queries from charts is enabled.
3. In the top-right corner, click Create QL chart.
4. In the Query tab, enter your query using the SQL dialect of the database you are querying.
5. In the bottom-left corner, click Start.

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

SELECT Category, Month, ROUND(SUM(Sales))
FROM samples.SampleLite
WHERE Category in {{category}}: A variable used in the selector
GROUP BY Category, Month: Grouping by category and month
ORDER BY Category, Month: Sorting by category and month

Adding selector parametersAdding 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. Set the default values, e.g., 2017-01-01 — 2019-12-31.

   

   Parameter values of the date, datetime, date-interval, and datetime-interval types can be specified in one of the following ways:

   • Exact date: Specified as an exact value.
   • Offset from the current date: Specified as a relative value that gets updated automatically.

   Use presets to quickly fill in the values.

To manage parameter values on the dashboard, create a selector with manual input and specify a parameter name in the Field or parameter name field.

IntervalsIntervals

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).

SELECT toDate(Date) as datedate, count ('Order ID')
FROM samples.SampleLite
WHERE {{interval_from}} < datedate AND datedate < {{interval_to}}
GROUP BY datedate
ORDER BY datedate

Substituting parameter values in a QL chart querySubstituting 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.

SELECT sum (Sales) as Sales, Category
FROM samples.SampleLite
WHERE Category in {{category}}
GROUP BY Category
ORDER BY Category

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

SELECT sum (Sales) as Sales, Category
FROM samples.SampleLite
WHERE Category = {{category}}
GROUP BY Category
ORDER BY Category

Null choice in selector and parametersNull 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:

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

ClickHouse® is a registered trademark of ClickHouse, Inc.