[Yandex Cloud documentation](../../../index.md) > [Yandex DataLens](../../index.md) > Charts > QL charts > Creating a QL chart

# Creating a QL chart in Yandex DataLens



You can create a QL chart using one of the following methods:

* [From the home page](#main-page).
* [From a connection](#connection).

{% note warning %}

Access permissions for QL charts based on Monitoring and Prometheus data are managed both on the DataLens side and in Monitoring and Prometheus.

To view a chart in DataLens, the user needs chart access and connection permissions, as well as permissions to read data from a specific Prometheus API or project in Monitoring.

{% endnote %}

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.

## Creating a QL chart from the home page {#main-page}


{% note warning %}

If you use the new DataLens object model with [workbooks and collections](../../workbooks-collections/index.md):

1. Go to the DataLens [home page](https://datalens.ru/?skipPromo=true). In the left-hand panel, select ![collections](../../../_assets/console-icons/rectangles-4.svg) **Collections and workbooks**.
1. Open the workbook, click **Create** in the top-right corner, and select the object you need.

Proceed with this guide from Step 3.

{% endnote %}



1. Go to the DataLens [home page](https://datalens.ru/?skipPromo=true).


1. In the left-hand panel, select ![image](../../../_assets/console-icons/chart-column.svg) **Charts**.
1. Click **Create chart** → **QL chart**.
1. In the window that opens, select the **Chart type**:

   {% list tabs %}

   - SQL

     1. Click **Select connection**.
     1. In the list, select a database connection you have access to. Make sure **Raw SQL level** → **Allow subqueries in datasets and queries from charts** is enabled.
     1. Click **Create**.
     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 **Start**.

     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 not_var{{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 %}

   - Prometheus

     1. Click **Select connection**.
     1. In the list, select a Prometheus connection you have access to.
     1. Click **Create**.
     1. Click **Add query** and specify a query in the Prometheus language.
     1. In the bottom-left corner, click **Start**.

     After the query runs, your data will be visualized.

     {% cut "Example of a query for Prometheus" %}
     
     ```sql
     sum by (name) (rate(container_cpu_user_seconds_total{container_label_org_label_schema_group="monitoring"}[1m]) * 100  / scalar(count(node_cpu_seconds_total{mode="user"})))
     ```
     
     {% endcut %}

     QL charts built on the Prometheus data can be parameterized similarly to SQL charts. This type of charts also has required global parameters created automatically when building a QL chart. You can edit default values of these parameters.

     | Parameter | Data type | Description |
     |---|---|---|
     | from | datetime | Lower time limit |
     | to | datetime | Upper time limit |
     | step | number | Step on the time scale in seconds |

     {% note info %}

     On the dashboard, you can use a single selector for the date with the special `interval` name instead of the two selectors, `from` and `to`.

     {% endnote %}

   - Monitoring

     
     1. Click **Select connection**.
     1. In the list, select a Monitoring connection you have access to.
     1. Click **Create**.
     1. Click **Add query** and specify a query in the Monitoring language.
     1. In the bottom-left corner, click **Start**.


     After the query runs, your data will be visualized.

     {% cut "Example of a query for Monitoring" %}
     
     ```json
     {project="monitoring", sensor="nodejs.heap_space.size.bytes"}
     ```
     
     {% endcut %}

     QL charts built on the Monitoring data can be parameterized similarly to SQL charts. This type of charts also has required global parameters created automatically when building a QL chart. You can edit default values of these parameters.

     | Parameter | Data type | Description |
     |---|---|---|
     | from | datetime | Lower time limit |
     | to | datetime | Upper time limit |

     {% note info %}

     On the dashboard, you can use a single selector for the date with the special `interval` name instead of the two selectors, `from` and `to`.

     {% endnote %}

   {% endlist %}

## Creating a QL chart from a connection {#connection}

1. Go to an existing database connection.
1. Make sure **Raw SQL level** → **Allow subqueries in datasets and queries from charts** is enabled.
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 **Start**.

After the query runs, your data will be visualized.


## 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 `not_var{{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 not_var{{interval_from}} < datedate AND datedate < not_var{{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 not_var{{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 = not_var{{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(not_var{{param}}::VARCHAR)=0 THEN TRUE
    ELSE column IN not_var{{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.

#### See also {#see-also}

* [Yandex DataLens chart](../../concepts/chart/index.md)
* [Adding a chart to a dashboard in Yandex DataLens](../dashboard/add-chart.md)

_ClickHouse® is a registered trademark of [ClickHouse, Inc](https://clickhouse.com)._