Examples of creating QL charts

In this tutorial, you will create charts using SQL queries. SQL queries enable you to configure data for visualization in a more flexible way than the standard dataset method does. For example, you can add parameters from dashboards to a SQL query.

We recommend using a QL chart if creating a regular chart using a dataset does not suit you 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 and follow the steps below:

1. Create a workbook.
2. Create a connection.
3. Create a QL chart.
4. Create a dashboard.
5. Add the QL chart to the dashboard.
6. Add selectors to the dashboard.

Getting startedGetting started

To get started with DataLens:

If you have any technical questions about the service, contact Yandex Cloud support. To ask for advice, discuss the solution to your problem or best practices of the service, write to the DataLens chat in Telegram.

Create a workbookCreate a workbook

1. Go to the DataLens home page.
2. In the left-hand panel, select Collections and workbooks.
3. In the top-right corner, click Create → Create workbook.
4. Enter a name for the workbook: Tutorials.
5. Click Create.

Create a connectionCreate a connection

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

1. In the top-right corner of the workbook, click Create → Connection.

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

   2. Enable Allow subqueries in datasets and queries from charts.

   3. Click Check connection and make sure you get a green check mark.

   4. Click Create connection.

      

   5. Enter the connection name:Sample ClickHouse.

   6. Click Create.

Wait for the connection to be saved.

Create a QL chartCreate a QL 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

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

2. In the top-right corner, click Create QL chart.

3. On the Query tab, enter the query code:

   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 {{interval_from}} < "Date" and "Date" < {{interval_to}} and t1.ClientID=t2.ClientID and t3.ClientID=t2.ClientID and t3.ClientStatus in {{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"
   

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

     

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

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

       

     • Click Apply.

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

     Note

     You can control the parameter values using dashboard selectors.

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

   

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

     

8. Save the chart:

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

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

Create a dashboardCreate a dashboard

Create a dashboard to add charts and other widgets to:

1. In the left-hand panel, select Collections and workbooks and go to the Tutorials workbook.
2. In the top-right corner, click Create → Dashboard.

Add the QL chart to the dashboardAdd the QL chart to the dashboard

1. In the panel at the bottom of the page, select Chart.

   

2. In the window that opens, click Select.

3. Select the New clients chart.

   

4. Click Add.

5. Adjust the size of the chart with your mouse and place it on the dashboard as you prefer.

   

Add selectors to the dashboardAdd selectors to the dashboard

Add selectors to filter charts by date and client statuses:

1. In the panel at the bottom of the page, choose Selector.

   

2. Add a selector for client status:

   1. In the Source list, select Manual input.

   2. In Field or parameter, enter status. Certain selector values will be passed into this SQL query variable.

   3. Choose List as your selector type.

   4. Enable Multiple choice.

   5. Next to the Possible values parameter, click Add. In the resulting window, add the following values:

      • Gold
      • Silver
      • Basic

      

      Click Apply.

   6. In the Default value list, specify Select all.

      

   7. In the Header field, enter Select client status.

   8. Click Save.

      

3. Add the calendar selector to filter by date range:

   1. In the Source list, select Manual input.

   2. In Field or parameter, enter interval. The start and end values of the interval from the selector will be provided to the {{interval_from}} and {{interval_to}} variables of the SQL query.

   3. Choose Calendar as your selector type.

   4. Enable Range.

   5. In the Header field, enter Order interval.

   6. Click Save.

      

4. Position the selectors on the dashboard however you like.

5. Save the dashboard:

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

Your dashboard is ready.

1. Your dashboard is ready. Now you can filter the chart by status using a selector.

   

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

   

See alsoSee also

• Adding a chart to a dashboard
• Adding a selector to a dashboard
• Creating a QL chart
• QL charts