SELECT
queries that extract Entities or other data form the Database. Depending on the configuration, the extracted Entities can be returned directly or inserted into a template to return a file.
Each Data Query can return either the extracted Entities directly or a file. If you require both variants, two different Data Queries need to be used.
-
A Data Query consists of at least one SQL
SELECT
query, but can have multiple SQLSELECT
query in it. Each SQLSELECT
query in a Data Query produces a so called Result Set. Hence, a Data Query returns as may Result Set as it has SQLSELECT
queries. You can think of a Result Set as a table. -
The SQL
SELECT
queries are executed one after another in the same transaction and are allowed to run a combined maximum of 120 seconds. After that, the transaction and therefore the execution of the SQLSELECT
queries is canceled and the Data Query fails. -
The SQL
SELECT
query are executed with the User triggering the Data Query- If the Data Query is executed by an Activity then the Data Query is executed by same User as the Activity is executed. Hence, the Data Query might be executed as the Novunex Process Engine user as described here.
You need to keep this fact in mind, since the Novunex Process Engine user can access all Entities without any restrictions. Hence, your Data Query might leak information.
Data Query Overview
Menu
Search and create new Data Queries
- You can enter a full name or part of a name of a Data Query into the search box and press Search so that the Data Query List only shows Data Query with matching names.
- Create new query opens the Data Queries Editor and you can start defining a new Data Query.
Data Queries List
List all Data Queries and provides ways to interact with them:
- ID - Internal identifier of the Data Query
- Name of the Data Query - You can click the name to edit the Data Query.
- CREATED - Shows when the Data Query was first created.
- MODIFIED - Shows when the Data Query was last modified.
- - When you click here, you get the option the Edit or Delete the Data Query.
Pagination
Data Query Editor
The Data Query Editor allows you to create, modify and test Data Queries.
Menu
The menu items let you control the Data Query Editor:
- Show result as - Determines if the raw data is shown (Table) or the resulting file (File). If file is selected, the File template needs to be properly configured in the Data Query.
- Parameters - By clicking
Add parameter, you can map a fixed value to the Parameters used in the SQL
SELECT
query:- Key - Name of the parameter used in the SQL
SELECT
query without the leading@
. - Value - Value of this parameter.
- Each mapping can be removed by clicking on .
- Key - Name of the parameter used in the SQL
- Run - Executes the Data Query
- This button opens the test environment to test and debug your Data Query:
- - Closes the Data Query Editor without saving the changes.
- - Saves all changes and closes the Data Query Editor
- - Saves all changes
- - Saves the Data Query under a new name
- Clicking here opens more save options:
Configuration
Configuration of the the Data Query:
- GUID - Unique ID of the Data Query
- Name - Name of the Data Query that must be unique within the Subscription
- Description - Explanatory text describing the Data Query
- File template - If a file is selected here, the result of the Data Query is put into the template. The Data Query then returns the resulting file instead of the extracted Entities. How templates are designed is given in section Templates.
- Convert report to PDF? - If a file template is configured so that the Data Query returns a file, this configuration controls if the file is returned directly (No) or converted and returned as a PDF (Yes). This option only works for Microsoft Word and Excel templates but not for plain text templates.
- Permissions - The permissions configure which User Groups are allowed to see the results of the Data Query. If no User Groups are configured here, all User Groups are allowed to the the result. If one or more User Groups are configures, one these User Groups can see the results of the Data Query.
- Add user group - Add a User Group by its name
- Each User Group can be removed by clicking on
Editor
You write your SQL SELECT
query in the Editor. Apart from the main editing space, there are also menu items offered by the Editor:
- Font size - Select the font size
- Color theme - Select different colors and highlighting schemes
- Template - Here you can select SQL
SELECT
query that are often used. Pressing the button overwrites any query in the Editor with the selected SQL template.
SQL for Data Queries
Data Query only support SQL SELECT
queries. Here, only the Novunex specifics of SQL SELECT
queries are given. Further information about SQL SELECT
queries can be found in the Microsoft SQL Server documentation. The SELECT
queries used in the Novunex Platform have several extensions of the Microsoft SQL Server queries:
- Entity Type can be used as table names when putting them in square brackets like
[MyEntityType]
.- Attributes of an Entity Type can be used like column names
- If the Track Changes setting of an Entity Type is set to Yes, the Attribute modification history of each Entity is stored in a table with the name of the Entity Type and the suffix
_History
. For example the history of the Attributes of Entities with the Entity Type[MyEntityType]
can be accessed with[MyEntityType_History]
.
- Core Tables can also be used like table names when putting them in square brackets like
[File]
- Vales passed to the SQL
SELECT
queries as parameters start with a leading@
sign followed by an alphanumeric string., i.g.,@MyParameter
,@LowerBound
,@SerialNumber
. The Novunex Platform does not check the parameters. This means if you want to handle special cases like empty parameters, you have to write the SQLSELECT
query accordingly.
Here is an example SQL SELECT
query that retrieves the two Attributes FirstAttribute
and SecondAttribute
of the Entity Type [MyEnity]
for all Entities where the Attributes ThirdAttribute
equals the value of MyParameter
SELECT FirstAttribute, SecondAttribute FROM [MyEnity] WHERE ThirdAttribute = @MyParameter
The Novunex Platform provides you will the following SQL functions in addition to the functions the Microsoft SQL Server already has.
ConvertDateTime( userId, DateTime)
- Converts a UTCDateTime
into the local DateTime for the user specified byuserId
.
Global Variables in Data Queries
Data Queries can access Global Variables in a similar fashion as Process Context Expressions.
SQL Global Variables
SQL Global Variables start with a @
-sign:
@CurrentUserId
- Hold the unique ID of the User executing the Data Query. Be aware that this can be the real User or the Novunex Platform Engine user as described here.
Process Context Expression Global Variables
Some of the Global Variables accessible in Process Context Expressions can be used in the SQL SELECT
statements of Data Queries in exactly the same way:
##[MySubscSetting]
- Access the value of theMySubscSetting
Subscription Setting.##[CurrentUser]
- The currently active User can be a real User or the Novunex Platform Engine user as described here.##[CurrentUser.Id]
- Unique ID of the User##[CurrentUser.UserName]
- Email address representing the user name##[CurrentUser.FirstName]
- First name of the User##[CurrentUser.LastName]
- Last name of the User##[CurrentUser.JobTitle]
- Job title of the User##[CurrentUser.Department]
- Name of the department the User belongs to##[CurrentUser.Location]
- The location information of the User##[CurrentUser.Division]
- Deprecated - do not use##[CurrentUser.ProfileImage]
- URL of the User’s profile image##[CurrentUser.IsoLanguageCode]
- Language information of the User##[CurrentUser.TimeZone]
- Time zone the Users works in. The time zone is specified as a TZ database name##[CurrentUser.UserGroups]
- Array of all User Groups this User belongs to##[CurrentUser.UserGroups.Id]
- Array of IDs identifying the User Groups this User belongs to##[CurrentUser.UserGroups.Name]
- Array of names identifying the User Groups this User belongs to
##[CurrentSubscription.Id]
- ID of the current Subscription##[Now]
- Current date and time##[Today]
- Current date
While other Global Variables of the Process Context Expressions also work in the SQL SELECT
statements, their usage is discouraged since these variables are not defined in all cases. E.g., when the Data query is executed by a Widget, Process related values like ##[Context.Process]
and ##[Context.Activity]
are not defined.
Templates and Reports
The template files define how the results of Data Queries should be formatted inside a file. When the data form a Data Query is filled into a template, a Report is created. Hence, a Report is nothing more that the result of a Data Query formatted by a template and stored in a file.
Different file types of templates supported, but each Data Query can supports one template. I.e., if you require different templates for the same SQL SELECT
query, you have to create multiple Data Queries with the same SQL SELECT
queries and different templates.
All values are inputted as strings by the Novunex Platform into the template. This means that the strings have to be properly formatted in the Data Query itself, e.g., data and time strings.
The first Result Set is referred to as Entity
, the second Result Set is referred to as Entity1
, the third as Entity2
, etc. in the templates.
Microsoft Word
Microsoft Word templates are DOCX-files that use so called MergeFields to determine where the results of Data Queries are inserted. Layouting has to be done in the Microsoft Word, like usage of different fonts or colors.
For each Result Set in the Data Query a block marked with MergeField must be defined in the Microsoft Word templates. The block corresponding to the first Result Set starts with the MergeField named TableStart:Entity
and ends with the MergeField named TableEnd:Entity
for independent Result Sets. The block corresponding to the second Result Set starts with the MergeField named TableStart:Entity1
and ends with the MergeField named TableEnd:Entity1
. The block corresponding to the third Result Set starts with the MergeField named TableStart:Entity2
and ends with the MergeField named TableEnd:Entity2
. Further blocks and Result Sets are handled by continuing the numbering scheme. While the numbering scheme is the same for independent an Result Sets with relations, Results Sets with relations use a different prefix. Each line in the Result Set becomes one line in the block. The columns in the Result Set are addressed by their name. A MergeField with the name of the column is used to access the value that column.
Alphanumerical Results Set
This examples shows how a Result Set is handled by MergeFields in the Microsoft Word templates. The Result Set is described in this table
Result Set | Name |
PartCode |
Quantity |
---|---|---|---|
First Row | Nail |
n123 |
50 |
Second Row | Nut |
c456 |
35 |
Third Row | Bolt |
t789 |
15 |
To corresponding Microsoft Word templates uses the values from the tree columns and rows. To make this example more readable, all MergeField are enclosed in «
and »
marks. These are not part of the actual MergeField name.
List of delivered parts
«TableStart:Entity»
The «Name» with the part code «PartCode» was delivered «Quantity» times.
«TableEnd:Entity»
We thank you for your purchase!
The values from the Result Set are filled into this template thus creating the final Microsoft Word file.
List of delivered parts
The Nail with the code n123 was delivered 50 times.
The Nut with the code c456 was delivered 35 times.
The Bolt with the code t789 was delivered 15 times.
We thank you for your purchase!
Images in Result Sets
Images stored on the Novunex Platform can also be inserted into the Microsoft Word template. In your SQL SELECT
query you have to select the Url
column of the File table as in this example
SELECT Url AS FileUrl FROM [File] WHERE Id=1234
Obviously, also other columns can be included in SQL SELECT
query, but are omitted here to keep the example simple.
The image is referenced in the Microsoft Word templates by a MergeField field named Image:FileUrl
. Be aware, that FileUrl
must be replaced with the name of the Url
column, if the column is named differently. However, in this example the corresponding Microsoft Word templates in this example could look like this.
Image from the Novunex Platform:
«TableStart:Entity»
«Image:FileUrl»
«TableEnd:Entity»
To make this example more readable, all MergeField are again enclosed in «
and »
marks.
Relations between Result Sets
When a Data Query has multiple Result Sets, there can be parent-child relation between the Result Sets. E.g., one Result Set describes an invoice with the billed products and the second Result Set holds detailed information about each item on the invoice. To work with the parent-child relation between the Result Sets in the template, each Result Set needs an extra column called CommandMapping
. The CommandMapping
column in the parent Result Sets needs to be an column of empty strings. The CommandMapping
column in the child Result Sets needs to specify the relation linking the two Result Sets. Therefore, an example to retrieve two Result Sets can look like this
--Entity
SELECT Id, InvoiceNumber, InvoiceDate, CustomerId, '' as CommandMapping
FROM Invoice
WHERE Id = 1234
-- Entity1
SELECT Product, Description, Quantity, Price, InvoiceId, 'InvoiceId = %Entity.Id%' as CommandMapping
FROM InvoiceItem
WHERE InvoiceId = 1234
The first Result Set is always referred to as Entity
, the second as Entity1
, the third as Entity2
, etc. The example above shows how the parent Result Set Enitiy
required the empty string column CommandMapping
. The child Result Sets Entity1
defines the parent-child relation as the link between the column InvoiceId
in Entity1
and the column Id
in Entity
. Note the percentage signs around the fully qualified name of the column in the parent Result Set Entity
.
The data of the Result Sets in inserted into MergFields in the Microsoft Word template. However, the blocks have to marked with the prefix BeginGroup
and EndGroup
in contrast to to the usual TableStart
and TableEnd
. Hence, the block corresponding to the first Result Set starts with the MergeField named BeginGroup:Entity
and ends with the MergeField named EndGroup:Entity
, block corresponding to the second Result Set starts with the MergeField named BeginGroup:Entity1
and ends with the MergeField named TableEnd:Entity1
. Further blocks and Result Sets are handled by continuing the numbering scheme. The corresponding Microsoft Word template for the query given above could look like the example below. To make this example more readable, all MergeField are enclosed in «
and »
marks. These are not part of the actual MergeField name.
List of Invoices
«BeginGroup:Entity»
The invoice «InvoiceNumber» received on «InvoiceDate» and by customer «CustomerId» consisted of:
«BeginGroup:Entity1»
«Product» («Description») delivered «Quantity» times at «Price» $.
«EndGroup:Entity1»
«EndGroup:Entity»
We thank you for your purchase!
Microsoft Excel
Microsoft Excel templates are empty XLSX-files where each Result Set in the Data Query requires one tab in the Excel template. The first Result Set is mapped onto the tab Entity
, the second Result Set on Entity1
, the third Result Set on Entity2
, etc. Each column in the Result Set becomes one column in the corresponding tab. While any existing data in the columns is overwritten, the formatting is kept. Hence, the Excel file template defines the format of the resulting file.
Plain Text
Paint text templates are empty files with a file extension of .utf8
. The file name can be chosen arbitrarily, so template.utf8
and plain.utf8
are both valid examples. When using a pain text template, the Result Set is exported into one singe text file with no column names. Each row becomes one line in the text file. The columns in each row are concatenated together without any delimiting character, i.e., if you need a delimiter between columns, you have to insert it in the SQL SELECT
statement. Values in array are indicated by square brackets and the values inside the arrays are enclosed by double quotes "
and separated by a comma ,
. Empty arrays are exported as a opening and closing square bracket []
.
The file generated form a plain text template cannot be converted into a PDF file.
This example query exports the three Attributes Count
, Name
and Dimensions
of the Entity MyProduct
and separates the columns with a semicolon.
SELECT Count, ';' AS Delimiter1, Name, ',' AS Delimiter2, Dimensions FROM [MyProduct] WHERE Id<=123 AND Id>=456
The Attribute Dimensions
is an array of decimal values. Therefore, the first three lines of the resulting file can look like this:
2;test first;["1.2","2.3","3.4"]
6;test second;[]
1;another;["0.2","3.4","5.2"]
In case of multiple Result Sets, the first row of the next Result Sets is appended to the last row of the previous Result Sets with no line brake separating them. If you want to separate them, you have to insert the SQL SELECT
query SELECT CHAR(13) + CHAR(10)
in your Data Query to generate a line break.