[Yandex Cloud documentation](../../index.md) > [Tutorials](../index.md) > [Data analysis and visualization](index.md) > DataLens > Tutorials for features > Examples of creating QL charts

# Examples of creating QL charts



{% note info %}

In this tutorial, all objects will be created and stored in a workbook. If using legacy folder navigation, create an individual folder to work in. 

{% cut "Create a folder" %}

1. Go to the DataLens [home page](https://datalens.ru/?skipPromo=true).
1. In the left-hand panel, select ![](../../_assets/console-icons/folders.svg) **All objects** or ![](../../_assets/console-icons/folder-house.svg) **Personal folder**.
1. In the top-right corner, click **Create** → **Folder**.
1. Enter a name for the folder.
1. Click **Create**.

{% endcut %}

{% endnote %}


In this tutorial, you will create charts using SQL queries. SQL queries provide more options for configuring data for visualization than the standard dataset method. For example, you can add parameters from dashboards to an SQL query.

We recommend using a [QL chart](../../datalens/concepts/chart/ql-charts.md) if creating a regular [chart](../../datalens/concepts/chart/dataset-based-charts.md) using a dataset does not suit your needs or if you want to experiment with the data.

A direct connection to a demo database will be used as your data source. 


To visualize and explore data, [set up DataLens](#before-you-begin) and follow the steps below:


1. [Create a workbook](#create-workbook).
1. [Create a connection](#create-connection).
1. [Create a QL chart](#create-sql-chart).
1. [Create a dashboard](#create-dashboard).
1. [Add the QL chart to the dashboard](#add-sql-chart-to-dashboard).
1. [Add selectors to the dashboard](#add-selectors-to-dashboard).

{% note warning %}

SQL queries only work with databases. File, GoogleSheets, Yandex Metrica, and other service connections do not support SQL queries.

{% endnote %}


## Getting started {#before-you-begin}

To get started with DataLens:


{% list tabs group=datalens_user %}

- New user {#new}

  1. [Log in](https://passport.yandex.com/auth) to your Yandex account. If you do not have an account, [create](https://yandex.ru/support/passport/authorization/registration.html) one.
  1. Open the DataLens [home page](https://datalens.ru/promo).
  1. Click **Start in cloud**.
  1. Confirm that you have read the [Terms of use](https://yandex.com/legal/cloud_termsofuse/?lang=en) and click **Log in**.

- I am already using Yandex Cloud {#already}

  1. [Log in](https://passport.yandex.com/auth) to your Yandex account.
  1. Open the DataLens [home page](https://datalens.ru/promo).
  1. Click **Start in cloud**.
  1. Select one of these options:

     * If you already have an organization, select it from the drop-down menu in the **Organizations** tab and click **DataLens**.

       {% note info %}

       To activate a DataLens instance, the user must have the `admin` or `owner` role. For more information about roles, see [Access management in Yandex Identity Hub](../../organization/security/index.md).

       {% endnote %}

     * If you have a cloud but no organization, click **Add new DataLens**. In the window that opens, enter a name and description for your organization and click **Create organization and DataLens**. For more information about working with organizations, see [Getting started with organizations](../../organization/quickstart.md).

{% endlist %}

If you encounter a technical issue with the service, contact Yandex Cloud [support](https://center.yandex.cloud/support). To ask for advice, discuss a solution to your issue, or explore current best practices, join the [DataLens](https://t.me/YandexDataLens) chat in Telegram.

## Create a workbook {#create-workbook}

1. Go to the DataLens [home page](https://datalens.ru/?skipPromo=true).
1. In the left-hand panel, select ![collections](../../_assets/console-icons/rectangles-4.svg) **Collections and workbooks**.
1. In the top-right corner, click **Create** → **Create workbook**.
1. Enter `Tutorials` for the [workbook](../../datalens/workbooks-collections/index.md) name.
1. Click **Create**.


## Create a connection {#create-connection}

A connection named **Sample ClickHouse** will be created for database access.

1. In the top-right corner of the workbook, click **Create** → ![image](../../_assets/console-icons/thunderbolt.svg) **Connection**.



1. Under **Databases**, select the **ClickHouse®** connection.

   1. In the window that opens, select `Specify manually` for the connection type and provide the following connection parameters:

   
      * Host name: `rc1a-ckg8nrosr2lim5iz.mdb.yandexcloud.net`
      * HTTP interface port: `8443` (default).
      * Username: `samples_ro`.
      * Password: `MsgfcjEhJk`.

   1. Enable **Allow subqueries in datasets, source parameterization, and QL charts**.
   1. Click **Check connection** and make sure you get a green check mark.
   1. Click **Create connection**.

      ![create-connection](../../_assets/datalens/operations/connection/create-sample-connection.png)

   1. Enter the connection name: `Sample ClickHouse`.
   1. Click **Create**.





Wait for the connection to be saved.

## Create a QL chart {#create-sql-chart}

Create a QL chart based on the `Sample ClickHouse` connection:

1. In the top-right corner of the connection page, click **Create dataset**.

   {% note info %}

   Make sure **Raw SQL level** → **Allow subqueries in datasets and queries from charts** is enabled.

   {% endnote %}

1. In the top-right corner, click **Create QL chart**.
1. On the **Query** tab, enter the query code:

   ```sql
   SELECT 
      toDate(t1.OrderDatetime) as "Date",
      COUNT(t1.ClientID) as "Number of new clients", t3.ClientStatus as "Status"
   FROM
      samples.MS_SalesFacts t1,
      (SELECT 
	      ClientID, 
         MIN(OrderDatetime) as FirstDate
      FROM samples.MS_SalesFacts
      GROUP BY ClientID) as t2,
         samples.MS_Clients t3
   WHERE not_var{{interval_from}} < "Date" and "Date" < not_var{{interval_to}} and t1.ClientID=t2.ClientID and t3.ClientID=t2.ClientID and t3.ClientStatus in not_var{{status}} -- status, interval_from and interval_to are variables linked to the parameters affected by the following selectors:
   GROUP BY "Status", "Date"
   ORDER BY "Date"
   ```

1. On the **Parameters** tab, click **Add parameter** and complete the input fields:

   * From the drop-down list, select `string` (default).
   * In the **Name** field, enter `status`.
   * In the **Default value** field, enter `Gold`.
   * Click **Add value** below and enter `Silver`.
   * Click **Add value** below and enter `Basic`.

     ![sql-chart-parameter](../../_assets/datalens/sql-chart/sql-chart-parameter.png)

   The added parameter will be linked to the `not_var{{status}}` variable in the query.

1. Click **Add parameter** and complete the input fields:

   * From the drop-down list, select `date-interval`.
   * In the **Name** field, enter `interval`.
   * Click the field with the interval value and in the window that opens, specify:

     * **From:** `01.03.2017`
     * **To:** `31.03.2017`

       ![sql-chart-parameter-data2](../../_assets/datalens/sql-chart/sql-chart-parameter-data2.png)

     * Click **Apply**.

     The **From:** and **To:** values of the added parameter will be linked to the `not_var{{interval_from}}` and `not_var{{interval_to}}` variables in the SQL query, respectively.

     {% note info %}
   
     You can control the parameter values using dashboard selectors.

     {% endnote %}

1. Go back to the **Query** tab. In the bottom-left corner, click **Start**. Running the query will produce a visualization in the right-hand window.

   ![sql-chart-diagram](../../_assets/datalens/sql-chart/sql-chart-diagram.png)

1. Configure the visualization:

   * Make sure the **X** section contains the `Date` field; otherwise, drag it from the **Available** section.
   * Make sure the **Y** section contains the `Number of new clients` field; otherwise, drag it from the **Available** section.
   * Add color division of clients by status to the chart. To do this, drag the `Status` field from the **Available** section to the **Colors** section.

     ![sql-chart-rezult](../../_assets/datalens/sql-chart/sql-chart-rezult.png)

1. Save the chart:

   1. In the top-right corner, click **Save**.
   1. In the window that opens, enter `New clients` for the chart name and click **Save**.

You can add the chart to the dashboard. You can also add selectors to the dashboard to manage the values of the `status` and `interval` QL chart parameters.

## Create a dashboard {#create-dashboard}

Create a [dashboard](../../datalens/concepts/dashboard.md) to add charts and other widgets to:


1. In the left-hand panel, select ![collections](../../_assets/console-icons/rectangles-4.svg) **Collections and workbooks** and go to the `Tutorials` workbook.
1. In the top-right corner, click **Create** → ![image](../../_assets/console-icons/layout-cells-large.svg) **Dashboard**.



## Add the QL chart to the dashboard {#add-sql-chart-to-dashboard}

1. In the panel at the bottom of the page, click ![image](../../_assets/console-icons/chart-column.svg) **Chart** and drag it where you want it to be.

   ![add-chart](../../_assets/datalens/sql-chart/add-chart.png)

1. In the window that opens, click **Select**.
1. Select the `New clients` chart.

   ![image](../../_assets/datalens/sql-chart/select-chart.png)

1. Click **Add**.
1. Adjust the size of the chart with your mouse and place it on the dashboard as you prefer.

   ![image](../../_assets/datalens/sql-chart/add-chart-on-dashboard.png)

## Add selectors to the dashboard {#add-selectors-to-dashboard}

Add [selectors](../../datalens/dashboard/selector.md) to filter charts by date and client statuses:

1. In the panel at the bottom of the page, click ![image](../../_assets/console-icons/sliders.svg) **Selector** and drag it where you want it to be.

   ![image](../../_assets/datalens/sql-chart/add-selector.png)

1. Add a selector for client status:

   1. In the **Source** list, select `Manual input`.
   1. In **Field or parameter**, enter `status`. This SQL query variable will get the selected values from the selector.
   1. Select `List` as your selector type.   
   1. Enable **Multiple choice**.
   1. Next to the **Possible values** parameter, click **Add**. In the window that opens, add the following values:

      * Gold
      * Silver
      * Basic

      ![image](../../_assets/datalens/sql-chart/add-selector-values.png)

      Click **Apply**.

   1. In the **Default value** list, specify **Select all**.

      ![image](../../_assets/datalens/sql-chart/add-selector-select-all.png)

   1. In the **Header** field, enter `Select client status`.
   1. Click **Save**.

      ![image](../../_assets/datalens/sql-chart/add-selector-parameters.png)

1. Add the calendar selector to filter by date interval:

   1. In the **Source** list, select `Manual input`.
   1. In **Field or parameter**, enter `interval`. The start and end values of the interval from the selector will be provided to the `not_var{{interval_from}}` and `not_var{{interval_to}}` variables of the SQL query.
   1. Select `Calendar` as the selector type.
   1. Enable **Range**.
   1. In the **Header** field, enter `Order interval`.
   1. Click **Save**.

      ![image](../../_assets/datalens/sql-chart/add-selector-data-parameters.png)

1. Position the selectors on the dashboard however you prefer.
1. Save the dashboard:

   1. In the top-right corner of the dashboard, click **Save**.
   1. Enter `Change in number of clients by year` as the dashboard name and click **Create**.

Your dashboard is ready for use.

   ![image](../../_assets/datalens/sql-chart/add-selector-on-dashboard.png)

1. Your dashboard is ready for use. Now you can filter the chart by status using the selector.
   
   ![image](../../_assets/datalens/sql-chart/selector-2-values.png)

   You can also filter the chart by date interval using the second selector.

   ![image](../../_assets/datalens/sql-chart/selector-data-2-values.png)

#### Useful links {#see-also}

- [Adding a chart to a dashboard in Yandex DataLens](../../datalens/operations/dashboard/add-chart.md)
- [Adding a selector to a dashboard in Yandex DataLens](../../datalens/operations/dashboard/add-selector.md)
- [Creating a QL chart in Yandex DataLens](../../datalens/operations/chart/create-sql-chart.md)
- [QL charts](../../datalens/concepts/chart/index.md#sql-charts)