Dataset Visual Editor Overview

Dataset Visual Editor or Query Builder is a feature that helps the User define the data to be displayed in the Viewer. It helps to build a query without requiring an understanding of SQL. In Query Builder, a command that defines the data to be extracted into MI is built. It is available for Datasets created from a Plugin and simplifies writing of the Plugin command code.

Open the Query Builder

Access Content > Datasets and click the Dataset Active Name link

Open the Data tab and scroll to the Plugin command section. To access the Query Builder:

  • From the Visual tab, [Select Columns]
  • From the Command tab, [Visual Editor]

Fields Tab

In this case is used a Tableau Data Source for the Dataset but the Visual Editor will look the same for any other Plugin.

  1. Field: Displays a list of columns to be displayed in the Viewer
  2. Alias: To rename the column, click [Add] and enter the new title in the field
  3. Type: Select the type of the data to be stored in this column.
    • Four types of data are available: "decimal", "integer", "date", and "text".
  4. Override: Only "decimal", "integer", and "date" data types can be aggregated, change the type of data as required
  5. Aggregation: Specify how the data in the column is to be displayed:
    • min: Minimum value
    • max: Maximum value
    • sum: Aggregated total of all values
    • avg: Calculated average of all data (total of all values divided by the number of values)
  6. [+ Count] to add a column that displays the number of values aggregated
  7. Click Refresh list to update the list of columns

Add a Derived Field

It is possible to insert an additional field with a formula or date to the Dataset by clicking [+ Derived Field].

1. Derived Field with Formula

  1. Name: Enter a descriptive name for the derived field
  2. Expression: Type the formula using "+", "-", "*", "/" symbols for mathematical operations
    • The values of the formula are placed into square brackets
    • After typing "[", the autosuggestion drop-down opens under the field.

2. Derived Field with Date

  1. Name: Enter a descriptive name
  2. Construct date based on: "Timestamp" option populates the Derived Field a date/timestamp
  3. Set date to be: Choose what kind of date/timestamp to add to the column
    • Current Time: Current date and time
    • Today: Only the current date without time
    • Yesterday: The date of the day before without time
    • Current Month: Only the current month without a day
    • Last Month: The previous month without a specific day
  4. Construct date based on: "Plugin data" extracts the date from one of the columns of the Dataset
  5. Date granularity: Select how precise the date is to be formatted: year, month and/or day
  6. Year/Month/Day: Choose the Dataset column from which the data is to be extracted

3. Derived Field with  Formatted Date

  1. Name: Enter a descriptive name
  2. Date: Choose the Dataset column from which the date is to be extracted
  3. Format: Enter format of the date according to Java Formatting Standards

Advanced Tab

On this tab, an additional filter or a sorting option can be added.

Adding Advanced Filter

Advanced Filter allows to specify the data shown in the Viewer. It is possible to display only the values that meet the defined conditions.

[+ Advanced Filter] to open the Add Advanced Filter window

  1. Filter on: Select the column from the Dataset to be used as a Filter
  2. Condition: Choose how to compare the values
  3. Value: The value entered in this setting is compared with the value from column chosen in the Filter on field.
    • Only the values that match the Condition are displayed in the Dataset

Separately, add a Filter for each column.

Adding Sort

A Sort defines the order in which the values in the column are to be ordered.

[+ Sort] to open the Add Sort screen

  1. Sort on: Choose the column from the Dataset
  2. Direction: Select how the data in the selected column is to be sorted, in ascending or descending order

Separately, add a Sorting option for every column.