Using parameters

A parameter is a variable that can substitute constant values in calculated fields.

You will learn how to manage visulizations using parameters:

• Create parameters at the dataset level and chart level.
• Work with formulas and visualizations in charts using parameters.
• Edit a parameter value on the dashboard using selectors with manual input.

You will use a direct connection to a demo database as your ClickHouse® data source.

To visualize and explore data, set up DataLens and follow the steps below:

1. Create a connection.
2. Create a dataset.
3. Add parameters to the dataset.
4. Create a chart with a configurable date dimension.
5. Create a chart with a configurable visualization dimension.
6. Create a dashboard.
7. Add charts to the dashboard.
8. 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 connectionCreate 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 Allow subqueries in datasets and queries from charts.

   3. Check the connection and click Create connection.

   4. Enter the connection name: Sample ClickHouse.

   5. Click Create.

   

Wait for the connection to be saved.

Create a datasetCreate a dataset

1. In the top-right corner, click Create dataset.

2. Drag the MS_SalesMiniTable table to the workspace.

   

3. Go to the Fields tab.

   

4. Create an order date field named OrderDate.

   1. Duplicate the OrderDatetime field.

      

   2. Rename the OrderDatetime (1) duplicate field to OrderDate: click the row name, delete the current name, and enter the new one.

   3. Change the data type from Date and time to Date.

      

5. In the Aggregation column, select Sum for the Sales field.

   

   The aggregation field will become a measure and change color to blue.

6. In the top-right corner, click Save.

7. Name the dataset: Moscow Sales dataset with parameters, then click Create.

Add parameters to the datasetAdd parameters to the dataset

1. Go to the Parameters tab.

   

2. Add the scale and dimension parameters:

   1. Click Add. Then, in the window that opens, enter:

      • Name: scale.
      • Type: String.
      • Default value: month.

      

      Click Add.

   2. Click Add. Then, in the window that opens, enter:

      • Name: dimension.
      • Type: String.
      • Default value: Status.

      Click Add.

3. Click Save.

Create a chart with a configurable date dimensionCreate a chart with a configurable date dimension

1. In the top-right corner of the dataset, click Create chart.

2. Select Stacked area chart as the visualization type.

   

3. Add a calculated field to the chart:

   1. On the left of the screen, click under the dataset and select Field.

   2. In the Field settings window, enter:

      • In the Name field, Date.

      • In the formula field, DATETRUNC([OrderDate], [scale]). The order date will be rounded depending on the value of the scale dataset parameter.

        

   3. Click Create.

   4. Drag the created Date field to the X section.

4. Drag the Sales measure to the Y section.

5. Add a product category dimension to the chart. To do this, drag the ProductCategory field from Dimensions to the Colors section. The diagram areas corresponding to each product category will get different color fills.

6. Save the chart.

   1. In the top-right corner, click Save.

   2. Enter the chart name Sales by product category, then click Save.

      

Create a chart with a configurable visualization dimensionCreate a chart with a configurable visualization dimension

1. In the top-right corner of the dataset, click Create chart.

2. For the visualization type, select Line chart.

3. Add a calculated field for selecting a dimension to the chart:

   1. On the left of the screen, click under the dataset and select Field.

   2. In the Field settings window, enter:

      • In the Name field, Field.

      • In the formula field:

        if(
             [dimension] = 'Status', [ClientStatus],
             [dimension] = 'Category', [ProductCategory],
             [dimension] = 'Payment type', [PaymentType],
             null
           )
        

        The visualized dimensions will change depending on the value of the dimension dataset parameter.

   3. Click Create.

   4. Drag the created Field field to the Y section.

4. Drag the Sales measure to the X section.

5. Save the chart.

   1. In the top-right corner, click Save.

   2. Enter the chart name Chart with a configurable visualization dimension, then click Save.

      

Create a dashboardCreate a dashboard

Create a dashboard to add these charts to.

1. Go to the DataLens home page.

2. Click Create dashboard.

   

Add charts to the dashboardAdd charts to the dashboard

1. The first time you open the dashboard after saving, it opens in edit mode. If you open it later, click Edit in the top-right corner.

2. Click Add and select Chart.

   

3. In the Chart field, click Select.

4. Select the Sales by product category chart. This will automatically fill in the Name field.

5. Click Add.

   

6. In a similar manner, add the Chart with a configurable visualization dimension.

7. Position the charts on the dashboard however you like.

8. Save the dashboard:

   1. In the top-right corner, click Save.
   2. Enter Visualizing data using the parameters for the dashboard name and click Create.

Add selectors to the dashboardAdd selectors to the dashboard

Add selectors with manual input to edit values in the charts.

1. To switch to dashboard editing mode, click Edit in the top-right corner.

2. Add a selector for the Sales by product category chart:

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

   2. Choose Selector.

      

   3. Select Manual input as the source type.

   4. Under Field or parameter name, enter scale. The value from the selector will be passed to this dataset parameter.

   5. Choose List as your selector type.

   6. Click the input field next to the Default value parameter and add the values:

      • day
      • week
      • month
      • year

      

      Click Apply.

   7. In the Default value list, specify month.

   8. Enable the Name option and enter Grouping.

   9. Verify the specified selector parameters.

      

   10. Click Add.

   11. Place the selector on the dashboard on top of the Sales by product category type. Now you can manage data grouping in this chart.

       

       

3. Add a selector for the Chart with a visualization dimension change:

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

   2. Choose Selector.

   3. Select Manual input as the source type.

   4. Under Field or parameter name, enter dimension. The value from the selector will be passed to this dataset parameter.

   5. Choose List as your selector type.

   6. Click the input field next to the Default value parameter and add the values:

      • Status
      • Category
      • Payment type

      Click Apply.

   7. In the Default value list, specify Status.

   8. Enable the Name option and enter Grouping.

   9. Click Add.

   10. Place the selector on the dashboard above the Chart with a configurable visualization dimension chart.

   11. Unlink this selector with the Grouping selector:

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

       2. In the Links window, select Dimensions.

       3. Make sure that you selected the Outgoing link link type for the Chart with a configurable visualization selection. For the Grouping selector, specify the link type Ignore.

          

       4. Click Save. Now, you can use this selector to select the visualized dimension.

          

          

See alsoSee also

• Parameters in Yandex DataLens
• Adding parameters to a dashboard
• Adding parameters to a chart
• Adding parameters

ClickHouse® is a registered trademark of ClickHouse, Inc.