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.
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]
In this case is used a Tableau Data Source for the Dataset but the Visual Editor will look the same for any other Plugin.
- Field: Displays a list of columns to be displayed in the Viewer
- Alias: To rename the column, click [Add] and enter the new title in the field
Type: Select the type of the data to be stored in this column.
- Four types of data are available: "decimal", "integer", "date", and "text".
- Override: Only "decimal", "integer", and "date" data types can be aggregated, change the type of data as required
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)
- [+ Count] to add a column that displays the number of values aggregated
- Click Refresh list to update the list of columns
It is possible to insert an additional field with a formula or date to the Dataset by clicking [+ Derived Field].
- Name: Enter a descriptive name for the derived field
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.
- Name: Enter a descriptive name
- Construct date based on: "Timestamp" option populates the Derived Field a date/timestamp
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
- Construct date based on: "Plugin data" extracts the date from one of the columns of the Dataset
- Date granularity: Select how precise the date is to be formatted: year, month and/or day
- Year/Month/Day: Choose the Dataset column from which the data is to be extracted
- Name: Enter a descriptive name
- Date: Choose the Dataset column from which the date is to be extracted
- Format: Enter format of the date according to Java Formatting Standards
On this tab, an additional filter or a sorting option can be added.
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
- Filter on: Select the column from the Dataset to be used as a Filter
- Condition: Choose how to compare the values
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.
A Sort defines the order in which the values in the column are to be ordered.
[+ Sort] to open the Add Sort screen
- Sort on: Choose the column from the Dataset
- 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.