Using parameters in calculated fields

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

You will learn how to manage visualizations 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 a dashboard using selectors with manual input.

You will use a direct connection to a demo ClickHouse® database as your 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 configurable grouping by date.
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 encounter a technical issue with the service, contact Yandex Cloud support. To ask for advice, discuss a solution to your issue, or explore current best practices, join 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_SalesFullTable table to the workspace.

   

3. Navigate 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 its color to blue.

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

7. Enter Moscow Sales dataset with parameters for the dataset name, 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 configurable grouping by dateCreate a chart with configurable grouping by date

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

2. Select 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 the following:

      • 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 new Date field to the X section.

4. Drag the Sales measure to the Y section.

5. Add grouping by product category to the chart. To do this, drag the ProductCategory field from Dimensions to the Colors section. The chart areas mapping to each product category will get different color fills.

6. Save the chart.

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

   2. Enter Sales by product category for the chart name, 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 the following:

      • 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 new 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 Chart with a configurable visualization dimension for the chart name, then click Save.

      

Create a dashboardCreate a dashboard

Create a dashboard for the charts.

1. In the left-hand panel, click Dashboards.
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. The Name field will be populated automatically.

5. Click Add.

   

6. Similarly, add Chart with a configurable visualization dimension.

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

8. Save the dashboard:

   1. In the top-right corner, click Save.
   2. Enter Visualizing data using 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. In Field or parameter name, enter scale. The selected selector value will be provided to this dataset parameter.

   5. Select List as your selector type.

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

      • day
      • week
      • month
      • year

      

      Click Apply.

   7. In the Default value list, specify month.

   8. Enable the Name option and enter Grouping.

   9. Check the specified selector parameters.

      

   10. Click Add.

   11. Place the selector on the dashboard above the Sales by product category chart. Now you can manage grouping by date in this chart.

       

       

3. Add a selector for Chart with a configurable visualization dimension:

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

   2. Choose Selector.

   3. Select Manual input as the source type.

   4. In Field or parameter name, enter dimension. The selected selector value will be provided to this dataset parameter.

   5. Select List as your selector type.

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

      • Status
      • Category
      • Payment type

      Click Apply.

   7. In the Default value list, specify Status.

   8. Enable the Name option and enter Dimension.

   9. Click Add.

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

   11. Unlink this selector from the Grouping selector:

       1. Click at the top of the screen. The settings window will open on the right.

       2. In the settings window, click Links .

       3. In the Links window, select Dimensions.

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

          

       5. Click Save. Now, you can use this selector to select a dimension for visualization.

          

          

See alsoSee also

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

ClickHouse® is a registered trademark of ClickHouse, Inc.