Data retrieval using an object
Data queries with the Object query module are created using the object layer and therefore take user access permissions fully into account.
Table 154: Data source object properties
|
Name |
Name of the data source. |
|
Description |
Description of data source. |
|
Max. lines |
Maximum number of result lines for this query. If this number is exceeded, report creation is terminated. |
|
Parent query |
In a parent query, restrictions are applied to the data record that are passed on to subsequent queries, all members of a department, for example. Parameters that are defined in the parent query are also available in subsequent queries. |
|
Query module |
Select the Object query module. |
|
Table |
Select the table to find the object in. |
|
Columns |
Columns to use in the report.
Some columns are always added to the report definition and must not be explicitly entered here. These include:
- The table's primary key column.
- All columns used in the table display template.
- Dummy columns (_Display and _DisplayLong) supplied by the table's display template.
- An additional column (<column>_Display) is also created for the display value for foreign key columns and columns with a list of defined values or multi-language entries.
|
|
Resolve foreign key |
Set this option if the display value of the referenced object should be returned in <column>_Display rather than the UID. |
|
Condition |
Condition for limiting the data set returned from the table. You formulate the condition as a valid WHERE clause for database queries. You may use SQL parameters in the condition. Add these parameters subsequently to the report by entering them on the Parameters tab.
Syntax for parameters:
@<Parametername>
Syntax for columns of a parent query:
@<name of parent query>.<column of the parent query> |
|
Sort order |
The data queries are sorted by these table columns. |
Related topics
Data retrieval using single object history
Use data queries with the Single object history query module when you want to create reports about a single object, for example, one employee, with its history data.
Table 155: Properties of data source single object history
|
Name |
Name of the data source. |
|
Description |
Description of data source. |
|
Max. lines |
Maximum number of result lines for this query. If this number is exceeded, report creation is terminated. |
|
Parent query |
In a parent query, restrictions are applied to the data record that are passed on to subsequent queries, all members of a department, for example. Parameters that are defined in the parent query are also available in subsequent queries. |
|
Query module |
Select the Single object history query module. |
|
Object key |
The object key can be queried directly or using a parameter. Add these parameters subsequently to the report by entering them on the Parameters tab. Columns in a parent query are formatted with the following syntax:
<parent query name>.<parent query column> |
|
Min date or range |
Use the minimum date to specify the point in time that the history data should start from. You can define the date directly or using a parameter. In the case of a parameter, the minimum date of all affected entries in the connected History Database databases is determined. Add these parameters subsequently to the report by entering them on the tab Parameters. |
|
Columns |
Columns for which the changes are determined. |
|
Resolve foreign key |
Set this option if the display value of the referenced object should be returned rather than the UID. |
The data query returns the following columns.
Table 156: Columns from a data query using single object history
|
ChangeID |
Unique identifier (UID) for the record. |
|
ObjectKey |
Object key or the record. |
|
ObjectUID |
Unique identifier (UID) for the modified objects. |
|
User |
Name of user that caused the change. |
|
ChangeTime |
Time of change |
|
ChangeType |
Type of change (Insert, Update, Delete) |
|
Columnname |
Name of column whose value has changed. |
|
ColumnDisplay |
Display name of column whose value has changed. |
|
OldValue |
Old column value. |
|
OldValueDisplay |
Old column display value. Only if the option Resolve foreign key is set. |
|
NewValue |
New column value. |
|
NewValueDisplay |
New value display value. Only if the option Resolve foreign key is set. |
Related topics
Data retrieval using multiple object history
Use data queries with the Multiple object history query module to create reports about multiple objects with historical data that can be further restricted by a particular criterion, for example all employees with the last name "Miller".
Table 157: Properties of data source multiple object history
|
Name |
Name of the data source. |
|
Description |
Description of data source. |
|
Max. lines |
Maximum number of result lines for this query. If this number is exceeded, report creation is terminated. |
|
Parent query |
Not used. |
|
Query module |
Select the Multiple object history query module. |
|
Table |
Select the table to find the object in. |
|
Min date or range |
Use the minimum date to specify the point in time that the history data should start from. You can define the date directly or using a parameter. In the case of a parameter, the minimum date of all affected entries in the connected History Database databases is determined. Add these parameters subsequently to the report by entering them on the tab Parameters. |
|
Columns |
Columns for which the changes are determined. |
|
Criteria |
Column, table, and value used for further narrowing down the objects found. The value can be queried directly or as a parameter. Add these parameters subsequently to the report by entering them on the Parameters tab. |
The data query returns the following columns.
Table 158: Columns from a data query using single object history
|
ChangeID |
Unique identifier (UID) for the record. |
|
ObjectKey |
Object key or the record. |
|
ObjectUID |
Unique identifier (UID) for the modified objects. |
|
User |
Name of user that caused the change. |
|
ChangeTime |
Time of change |
|
ChangeType |
Type of change (Insert, Update, Delete) |
|
Columnname |
Name of column whose value has changed. |
|
ColumnDisplay |
Display name of column whose value has changed. |
|
OldValue |
Old column value. |
|
OldValueDisplay |
Old column display value. Only if the Resolve foreign key option is set. |
|
NewValue |
New column value. |
|
NewValueDisplay |
New value display value. Only if the Resolve foreign key option is set. |
Example
A history of all employees with the last name "Miller" should be created. The report data can be defined in the following way:
|
Table: |
Employee |
|
Minimum Date |
MinDate |
|
Criteria: column |
Lastname |
|
Criteria: value |
Miller |
Related topics
Data retrieval using historical assignments
Use data queries with the Historical assignments query module to create reports with historical data from object assignments, for example, employee role memberships. This type is used for queries through foreign key relations as well as through assignment tables (many-to-many tables) and child relations.
Table 159: Properties of data source historical assignments
|
Name |
Name of the data source. |
|
Description |
Description of data source. |
|
Max. lines |
Maximum number of result lines for this query. If this number is exceeded, report creation is terminated. |
|
Parent query |
In a parent query, restrictions are applied to the data record that are passed on to subsequent queries, all members of a department, for example. Parameters that are defined in the parent query are also available in subsequent queries. |
|
Query module |
Select the Historical assignments query module. |
|
Assignment direction |
Assignment to be used in the report. Permitted values are Assignments (CR & MN) and Referenced objects (FK). |
|
Table |
Table for the assignment. |
|
Minimum date or range |
Use the minimum date to specify the point in time that the history data should start from. You can define the date directly or using a parameter. In the case of a parameter, the minimum date of all affected entries in the connected History Database databases is determined. Add these parameters subsequently to the report by entering them on the Parameters tab. |
|
Criteria column |
Column in the table for linking to the base object. |
|
Criteria value |
The value of the criteria column can be queried directly or using parameters. Add these parameters subsequently to the report by entering them on the Parameters tab. Columns in a parent query are formatted with the following syntax:
<parent query name>.<parent query column> |
|
Foreign key to query |
Foreign key to retain historical assignments. |
|
Disabling columns |
Certain tables contain columns that can disable an object, for example, the AccountDisable column in the ADSAccount table. Enter these columns if an assignment should be labeled as "Deleted" when disabled and "Added" if enabled. |
|
Additional object columns |
Enter the columns from the table that should also be available in the report. |
|
Additional criteria |
Column of the table and value for further restriction of the base object. |
The data query returns the following columns.
Table 160: Columns from a data query using historical assignments
|
BaseKey |
Object key for assignment base object. |
|
BaseUID |
Base object unique identifier. |
|
ObjectKey |
Assignment object key. |
|
DestinationKey |
Object key for assignment target object. |
|
DestinationUID |
Target object unique identifier. |
|
Display |
Target object display value. |
|
CreationUser |
User that created the assignment. |
|
CreationTime |
Time of assignment. |
|
DeletionUser |
User that deleted the assignment. |
|
DeletionTime |
Time of deletion. |
|
Type |
More detailed specification of the assignment, for example, assignment table name or target system type. |
|
Origin |
Bit mask for mapping the type of assignment. |
|
OriginDisplay |
Display name of the bit mask for mapping the type of assignment. |
Related topics
