Overview
The Dataset module offers powerful configuration options for managing queries efficiently. Users can define queries with custom names, select database configurations, and input SQL statements, including Tag embedding. Mapping options enable data population from query results, and users can specify time references (UTC or Local) for Date Time fields.
In addition, the Dataset module allows users to display query results using Data Grids in screens and dashboards, simplifying data visualization. Query results can also trigger actions in custom scripts and tags, enhancing automation capabilities for notifications and tag updates. For more advanced functionalities, users can explore Stored Procedures.
On this page:
Queries
To create and configure a Dataset Query, follow the steps below:
- Access Datasets → Queries.
- Click the plus icon.
Enter a Name and Description for the Query
Click Ok.
Define the DB (Database Connection), and Go To SQL Query editor to define the Query (you can navigate using the tree or double-click the SqlStatement column)
The use of spaces is not allowed in the Name. Instead, use underscores. For example My_Query.
Use Meaningful Names
By Default, the system will name the create the Dataset.Query with Query1, Query2,... We recommend giving a more meaning name, and using the description field, to properly identify each query.
Enhance Query Flexibility with Real-Time Parameters
Adding real-time parameters to your queries offers flexibility and adaptability, enabling dynamic data retrieval based on changing conditions. Instead of hardcoding specific values, you can use dynamically populated placeholders at runtime.
Instead of using Queries like the following:
SELECT * FROM your_table WHERE col1 = 5
Use Queries with real-time parameters:
SELECT * FROM your_table WHERE col2 = {{tag.Test}}
In this example, {{tag.Test}} serves as a placeholder for a real-time parameter.
You can modify the Query settings after its creation. Double-click the property you wish to edit on the row corresponding to the Query you want to modify. The following table lists all possible configuration properties.
Column | Description |
|---|---|
Name | The Query's name. The system will warn you if you choose an invalid name. |
DB | Defines the database where the Query will be executed. |
SqlStatement | Specify the Query using SQL syntax. You can embed Tags directly on the SQL statement, embracing the tag with curly brackets. For example, ColumnName = {tag.tagInt}. Alternatively, you can update the SQL Statement with the Script module accessing the property Dataset.Query.QueryName.SqlStatement. If you access that property from multiple tasks using this approach, be aware of potential concurrency issues. For more information, access Advanced Dataset Guide. |
Mapping | Click the three dots button (...) to select the tags you want to populate with data from specific columns the Query returns. |
MappingDateTime | Specify how the time in Database will be interpreted (UTC or Local), when mapping to DateTimeOffset .NET variables. |
Description | Query's description. |
Showing Data Grids Tables On Displays
One of the critical features of the Dataset module is the ability to display Query results on screens and dashboards using visualization tools like Data Grids. To use Data Grids to display query results, follow these steps:
- Create a Query in the Dataset module to retrieve the desired data.
- In the Displays module, add a Data Grid control to the screen or dashboard.
- Configure the Data Grid to display the Query results by selecting the data source and column mappings.
- Save and preview the screen or dashboard to display the Query results on the Data Grid.
You can access the Displays (Desktop and Web) page for more information on configuring Displays.
Using Query Results On Scripts And Tags
You can use Query results to trigger actions in custom scripts and tags. To use this kind of automation, you need to follow the steps below:
- Create a Query in the Dataset module to retrieve the desired data.
- In the Scripting module, create a custom script that references the Query results.
- Use the Query results to trigger specific actions within the script, such as sending notifications or updating tags.
- Save and execute the script to perform the desired actions.
Check the Stored Procedures page for additional information.
In this section...