Working with SQL Data Sources

After you connect to the SQL data source, you can create a new query or edit existing queries. To manage SQL queries, open the dashboard menu and go to the Data Sources page. Select the required LoadGen Cloud data source and perform any required action.
  • To add a new query, click the Add Query button. This action invokes the Dashboard Data Source Wizard, where you can create a query, select a stored procedure, or configure query parameters.
  • To edit the existing query, click the Edit button (the  wdd-icon-edit-query  icon) next to the required query. This action invokes the Dashboard Data Source Wizard where you can edit the current query.
  • To delete the existing query from the SQL data source, click the Delete button (the wdd-icon-delete-query icon) next to the query.

Query Builder

The Query Builder is a visual query editor. You can use it to add data tables and views to the data source and select which columns to include. Creating a new query can be done by:
  1. Enter a name for your query.
  2. Press the Run Query Builder or type the SQL string yourself (if you are a pro 😃 ).

Add Tables

Drag a specific table or view to the Query Builder design surface pane to include it in a query:
Then select the columns to include in the query:

The Query Builder provides a toolbar with the following commands:
Removes the selected table or view from the query.
Reverses the most recent action.
Restores the previously undone action.

Join Tables

The Query Builder allows you to join tables and/or views. Use drag and drop to connect corresponding columns (key fields). A relationship line is drawn between two connected tables/views. Note the connected columns should have identical data types. The Query Builder automatically joins a table or view that has a relationship at the database level with any recently added tables.
The Query Builder allows you to change the join type (if necessary). Click a relationship line to display the Relation Properties section. Properties in this section define the join type (Inner or Left Outer) and applied logical operator.
A left outer join returns all values from an inner join along with all values in the “left” table that do not match the “right” table. The result also includes rows with NULL (empty) values in the key field. If you select the left outer join, the relationship line displays an arrow that points to the “right” table of the join clause. The executed query returns a “flat” table that joins different tables within a single query.
Select the relationship line and click Delete (the wdd-query-builder-delete-icon icon) to delete an unnecessary relationship.


Edit Column Settings

Select a table or view, and click a data column to display the data column options.
The Column Properties section contains the following options:


Indicates the column name that the Query Builder obtains from the database.
Indicates the column’s data type. The Query Builder provides information about the maximum string length for string columns.
Specifies a custom column name (alias). Include a column into a query to enable this option. Aggregated columns should always have an alias.
Specifies whether to include a column in a query result.
Sort Type
Specifies whether to maintain the initial sort order for a column or sort data records in ascending or descending order.
Sort Order
Defines the sorting priority for multiple columns (the less this number is, the higher the priority).
Group By
Specifies whether to group a query result by this column.
Specifies whether to aggregate the column’s data records. You can use the following aggregate functions: CountMaxMinAvgSumCountDistinctAvgDistinctSumDistinct.
The Query Builder omits individual data records from the query result and only retains the aggregate function result when you apply any of these functions.

Filter Data

The Query Builder can be used to filter a query. To do this, deselect tables and click the ellipsis button in the invoked Filter field in the Properties pane. This invokes the Filter Editor dialog, which provides a visual interface for constructing a filter string.
You can filter SQL queries constructed in the Query Builder by including the WHERE clauses in the query. Filters can be applied to underlying or aggregated data. The applied filters affect all dashboard items that use the filtered query. Columns from this query are used to build filter criteria.
To filter a query, deselect added tables and click the ellipsis button next to the Filter field in the Query Builder.
The Filter Editor enables you to compare field values with the following objects:
  • Value - Allows you to compare field and static values.
  • Property - Compares different field values.
  • Parameter - Allows you to compare field and query parameter values. You can choose an existing query parameter or create a new one by selecting Create new parameter.

Preview Data

Click the Preview Results button to test a query on the actual data’s limited subset.
The invoked Data Preview dialog displays the first 100 data records of the query:
Was this article helpful?
0 out of 0 found this helpful