Visualizing data with a QL chart

In this scenario, 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 from a dataset isn't suitable for your situation or if you wish 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, then follow the steps below:

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

Before you start

To get started with DataLens:

If you have a technical question about the service, please contact support. To ask for advice or discuss the solution to your problem or best service practices, write to the DataLens chat in Telegram.

Create a connection

1. Go to the connections page.

2. Click Create connection.

3. Select the ClickHouse connection.

   1. In the window that opens, specify the connection parameters:

      • Connection type — Specify manually.
      • Host name — rc1a-ckg8nrosr2lim5iz.mdb.yandexcloud.net.
      • HTTP interface port — 8443 (default).
      • Username — samples_ro.
      • Password — MsgfcjEhJk.

   2. Enable the HTTPS option and Allow subqueries in datasets and queries from charts.

   3. Check the connection and click Create connection.

   4. Enter Sample ClickHouse as your connection name.

   5. Click Create.

   

Wait for the connection to be saved.

Create a QL chart

1. Go to an existing database connection.

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

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

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

   SELECT
      toDate(t1.OrderDatetime) as "Date",
      COUNT(t1.ClientID) as "Number of new customers", 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 - variables associated with parameters affected by selectors
   GROUP BY "Status", "Date"
   ORDER BY "Date"
   

5. On the Params tab, click Add param 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 Золотой.

   • Click Add param value below and enter Серебряный.

   • Click Add param value below and enter Базовый.

     

   The added parameter will be associated with the {{status}} variable in the query.

6. Click Add param and complete the input fields:

   • In the drop-down list, select date-interval.

   • In the Name field, enter interval.

   • Click the field with the interval value and enter:

     • From: 2017-03-01.

     • To: 2017-03-31.

       

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

     Note

     You can control the parameter values using dashboard selectors.

7. Go back to the Query tab. In the lower left-hand corner, click Run. Running the query will produce a visualization in the right-hand window.

8. Configure the visualization:

   • Select Column chart as chart type.

     

   • Make sure that the Date field is in the X section.

   • Make sure that the Number of new clients field is in the Y section.

   • Make sure that the Status field is in the Colors section.

   • Make sure the Available section is empty. This is the section that contains fields not involved in calculations. Otherwise, the visualization will display incorrectly.

     

9. In the upper right-hand corner, click Save.

10. Enter New clients as your chart name and click Save.

You can place the chart created on the dashboard. You can also equip the dashboard with selectors to control the values of the QL chart status and interval parameters.

Create a dashboard

Create a dashboard to add these charts to.

1. Go to the DataLens homepage.

2. Click Create dashboard.

   

3. Enter Change in number of clients by year as the name for the dashboard and click Create.

   The dashboard created will open for editing.

Add the QL chart to the dashboard

1. At the top of the page, click Add and select Chart.

   

2. In the Chart field, click Select and select the previously created New clients chart from the chart list.

   

   Click Add.

3. The chart is displayed on the dashboard. Stretch it to improve visualization.

   

4. Save the dashboard.

Add selectors to the dashboard

1. Add a selector to be able to filter the chart by client status:

   1. At the top of the page, click Add.

   2. Choose Selector.

      

   3. Select Manual input as source type.

   4. In the Field or parameter name field, enter status. Certain selector values will be passed into this SQL query variable.

   5. Choose List as your selector type.

   6. Enable the Multiple choice option.

   7. Click the input field next to the Default value parameter. In the resulting window, add the following values:

      • Золотой
      • Серебряный
      • Базовый

      

      Click Apply.

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

      

   9. Enable the Title option and enter Select client status.

   10. Verify the specified selector parameters.

       

   11. Click Add.

   12. Place the selector on the dashboard under the chart.

2. Add another selector to be able to filter the chart based on a date range:

   1. At the top of the page, click Add.

   2. Choose Selector.

   3. Select Manual input as source type.

   4. Under Field or parameter name, enter interval. The interval start and end values from the selector will be passed into the SQL query {{interval_from}} and the {{interval_to}} variables.

   5. Choose Calendar as your selector type.

   6. Enable Range.

   7. Enable the Title option and enter Order interval.

   8. Verify the specified selector parameters.

      

   9. Click Add.

   10. Place the selector on the dashboard on top of the chart next to the first selector.

   11. Save the dashboard.

       

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

   

For details, see also

• Adding charts to dashboards
• Adding selectors to dashboards
• Creating a QL chart
• QL chart