--- metadata: - name: generator content: Diplodoc Platform v5.63.0 alternate: - en/operations/chart/create-sql-chart - ru/operations/chart/create-sql-chart - href: en/operations/chart/create-sql-chart.md type: text/markdown title: Markdown version csp: - script-src: - https://mc.yandex.ru img-src: - https://mc.yandex.ru connect-src: - https://mc.yandex.ru - wss://mc.yandex.ru child-src: - 'blob:' - https://mc.yandex.ru frame-src: - 'blob:' - https://mc.yandex.ru frame-ancestors: - 'blob:' - https://mc.yandex.ru canonical: en/operations/chart/create-sql-chart.html title: Creating a QL chart in DataLens On-premises description: Follow this guide to create a QL chart in DataLens On-premises. vcsPath: en/operations/chart/create-sql-chart.md --- # Creating a QL chart in DataLens On-premises QL charts have the same [general settings](../../concepts/chart/settings.md#common-settings) and [section settings](../../concepts/chart/settings.md#section-settings) as the dataset-based charts. Only certain [measure settings](../../concepts/chart/settings.md#indicator-settings) are supported for chart fields. At each step, you can [undo/redo](../../concepts/chart/settings.md#undo-redo) any change introduced within the current version. To create a QL chart: 1. Go to an existing database connection. 1. Make sure the **Raw SQL level** → **SQL to read** setting is active in the connection. 1. In the top-right corner, click **Create QL chart**. 1. In the **Query** tab, enter your query using the SQL dialect of the database you are querying. 1. In the bottom-left corner, click **Run**. After the query runs, your data will be visualized. {% cut "Example of a ClickHouse® database query" %} ```sql SELECT Category, Month, ROUND(SUM(Sales)) FROM samples.SampleLite WHERE Category in {{category}} -- Variable used in the selector GROUP BY Category, Month -- Grouping by category and month ORDER BY Category, Month -- Sorting by category and month ``` {% endcut %} ## Adding selector parameters {#selector-parameters} In [QL charts](../../concepts/chart/index.md#sql-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. 1. Click **Add parameter**. 1. Set the value type for the parameter, e.g., `date-interval`. 1. Name the parameter, e.g., `interval`. 1. Set the default values, e.g., `2017-01-01 — 2019-12-31`. ![image](../../_assets/datalens/parameters/date-interval.png =450x167) There are several ways to configure the parameters of the `date`, `datetime`, `date-interval`, and `datetime-interval` types: * **Exact date** to specify an exact value. * **Offset from the current date** to specify a relative value that will be updated automatically. Use presets to quickly fill in the values. To manage parameter values on the dashboard, [create a selector](../dashboard/add-selector.md) with manual input and specify a parameter name in the **Field or parameter name** field. ### Intervals {#params-interval} The `date-interval` and the `datetime-interval` type parameters can be used 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 interval (`2017-01-01`). * `interval_to` to get the end of the interval (`2019-12-31`). {% cut "Query example" %} ```sql SELECT toDate(Date) as datedate, count ('Oreder ID') FROM samples.SampleLite WHERE {{interval_from}} < datedate AND datedate < {{interval_to}} GROUP BY datedate ORDER BY datedate ``` {% endcut %} ### Substituting parameter values in a QL chart query {#params-in-select} A QL chart gets parameter values from a selector as: * Single value if one element is selected. * [Tuple](https://docs.python.org/3/library/stdtypes.html#tuples) if multiple values are selected. If a query for ClickHouse® or PostgreSQL connections has the `in` operator before a parameter, the substituted value is always converted into a tuple. In the case of other connections, there is no automatic conversion. A query with the `in` operator will run correctly if you select one or more values. {% cut "Example of a query with `in`" %} ```sql SELECT sum (Sales) as Sales, Category FROM samples.SampleLite WHERE Category in {{category}} GROUP BY Category ORDER BY Category ``` {% endcut %} If the query has `=` before a parameter, the query will only run correctly if a single value is selected. {% cut "Example of a query with `=`" %} ```sql SELECT sum (Sales) as Sales, Category FROM samples.SampleLite WHERE Category = {{category}} GROUP BY Category ORDER BY Category ``` {% endcut %} ### Null choice in a selector and parameters {#empty-selector} 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](../../concepts/chart/dataset-based-charts.md), 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: ```sql AND CASE WHEN LENGTH({{param}}::VARCHAR)=0 THEN TRUE ELSE column IN {{param}} END ``` ## Undoing and redoing changes in charts {#undo-redo} When editing a QL chart, you can now [undo/redo](../../concepts/chart/settings.md#undo-redo) any change introduced within the current version. #### Useful links {#see-also} * [DataLens On-premises chart](../../concepts/chart/index.md) * [Adding a chart to a dashboard in DataLens On-premises](../dashboard/add-chart.md) _ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._