Versions Compared

Old Version 7

changes.mady.by.user user-05e19

Saved on

compared with

New Version Current

changes.mady.by.user Support

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Overview

The Dataset module 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 Tag embedding. Mapping options enable data population from query results, and users can specify time references (UTC or Local) for DateTime Date Time fields.

AdditionallyIn addition, the Dataset module Module allows users to display query results using DataGrids Datagrids 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:

Table of Contents
maxLevel3
stylenone

Tip
titleSolution Examples

Datasets Examples


Creating Queries

Queries configuration properties in the Dataset module enable users to create and manage data retrieval queries efficiently. Key fields include:

 To create and configure a Dataset Query, follow the steps below:

  1. Access Datasets / Queries.
  2. Click the plus icon.

  3. Enter a Name and Description for the Query

  4. Click Ok.

  5. 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)

    Info

    The use of spaces is not allowed in the Name. Instead, use underscores. For example My_Query.

    Image Added


Tip
titleUse Meaningful Names

By Default, the system will name the create the Dataset.Query with Query1, Query2,... We recommend giving a more meaningful name and using the description field, all in order to properly identify each query.


Info
titleEnhance 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.

Dataset Query Configuration Properties  

Column

Description

Name

Enter a name for the queryThe Query's name. The system allows will warn you to know if the name is not validif you choose an invalid name.

DB

Select Defines the database configurationwhere the Query will be executed.

SqlStatement

Enter Specify the query Query using SQL syntax. You can embed tags Tags directly on the SQL statement, embracing the tag with curtly curly brackets. E.g. "For example, ColumnName = {tag.tagInt}".

Alternatively, you can also update the SQLStatement SQL Statement with the Script Module module accessing the property Dataset.Query.QueryName.SqlStatement. When doing so, if If you access that property from multiple tasks using this approach, be aware of potential concurrency issues, for advanced information on that refer to the page . For more information, access Advanced Dataset Guide.

Mapping

Click "the three dots button (..." ) to select the tags that you want to populate with data from specific columns returned by the queryQuery returns.

MappingDateTime

Select Specify how the time reference in Database will be interpreted (UTC or Local), when mapping to DateTimeOffset .NET variables.

Description

Enter a description for the table configuration.

Showing DataGrids Tables on Displays

One of the key features of the Dataset module is the ability to display query results on screens and dashboards using visualization tools like DataGrids. Here are some steps for using DataGrids to display query results:

  1. Create a query in the Dataset module to retrieve the desired data.
  2. In the Displays Module, add a DataGrid control to the screen or dashboard.
  3. Configure the DataGrid to display the query results by selecting the data source and column mappings.
  4. Save and preview the screen or dashboard to display the query results on the DataGrid.

Using Query Results on Scripts and Tags

Users can use query results to trigger actions in custom scripts and tags. Here are some steps for using query results in scripts and tags:

  1. Create a query in the Dataset module to retrieve the desired data.
  2. In the Scripting Module, create a custom script that references the query results.
  3. Use the query results to trigger specific actions within the script, such as sending notifications or updating tags.
  4. Save and execute the script to perform the desired actions.

Query's description.


Editing Queries

The queries are ending using the Built-In SQL Query Editor, which assists doing the hight of the SQL Syntax. 

Image Added

→ Learn more at the Query Editor page.

There is also the tool VisualQueryBuilder which allows to explore databases, and drag-drop tables and visually connect fields to build the Query. 

The VisualQueryBuilder is started navigating to Datasets → DBs, and clicking on the Visual Query Builder button. 

→ See Visual SQL Query Builder for more information on that tool.

Testing Queries

The best place to test your queries is the DataExplorer SQL Queries tool.

Image Added

Any query used in the solution, or even new queries you are modifying in that environment can be executed. 

The results are presented in a DataGrid object in which you can customize the columns.

→ Learn more at Data Explorer SQL QueriesCheck the Stored Procedures page for additional information.

In this section...

Page Tree
root@parent
spacesV10