Targets

The Target of a report is the object type from which the query will select data, not unlike a SQL SELECT statement. The purpose of a report is to retrieve Resources, Groups, Activities, and/or Processes of desired types and display their data for users, and a target specifies a type of any of these to query.

For example, a user building a report to display all Resources of the type "Car" would need to create a target for the "Car" Resource Type. In more technical terms, a target is a table of the system's underlying database that will be queried in order to build a report. The "Add Target" button in the "Select" section of the Queries & Reports page opens the "Add Report Target" dialog shown below.

When creating a new target, a user must first enter a Code, unique within the data query. Next, the user must select the object type of the query. Once this is selected, the Fields column will appear, containing a checkbox for each of the fields of the selected object type.

Only the fields explicitly selected by the user will be queried by the report, allowing the report to ignore a potentially large amount of unwanted fields, resulting in a more efficient lookup of data.

Advanced Options

If the user has the "Advanced Editing" permission, then the Advanced Options column will appear on this dialog as well.

A data query can have any number of targets, but having multiple targets can complicate the results of the query, as these targets can be combined in ways unintended by the user. These advanced options allow an advanced user to better control how multiple targets are combined together.

The Inner flag, for example, marks a target's underlying database table as the inner table in a join operation, which can help with query optimization when used correctly.

The Join Field and Inverse Join Field of two targets work in tandem. If the Join Field of one target and the Inverse Join Field of another are specified, the query will only return combinations where the two values match; that is, only when the value of the join field of a Target X object matches the value of the inverse join field of a Target Y object, will the system return the combination of those two objects in the results of the query. If these fields are not specified, the system will typically take the cross-product of the underlying tables of the two targets.

It is important to note that only fields of the Text data type can be chosen for these two fields. Additionally, a target can only select a Join Field from a target listed above it; the third target of a query can only select fields from the first or second targets, not the fourth; the Inverse Join Field must be a field within a target itself, so it has no such restriction.

The last of the advanced options is the Group By field. This field appears as a drop-down menu containing a list of all Text-type fields of the selected object type. If selected, the query will not return a list of resultant objects as usual; instead, it will group the objects of the target together when they have the same value of the Group By field.

Since the results of the data query are groups, not individual objects, the normal fields of the target become meaningless; a target object type may have an "ID" field, but each object in a group can have a different value for these, and no value is preferable over the other. For Integer and Number-type fields, however, the system can aggregate the values of these objects together. Once a Group By field is selected, drop-down boxes will appear for each Integer and Number type field, allowing the user to select from a number of aggregation functions:

• minimum
• maximum
• sum
• average

The system will use the selected function to make sense of a field and allow that aggregate value to be exposed to the final report; for example, if the "Temperature" field of an object is selected with the aggregation function "average", the value of the "Temperature" field in the final report will be the average temperature of the objects within each group. Since fields of most data types, such as Text, Boolean, etc., cannot be aggregated together meaningfully, selected fields of these types are ignored, with the exception of the Group By field itself.

The Group By field appears as described only for the first target of a data query. However, targets beyond the first are only given the option to group by " * ". If this is selected, the system will group the results of the query by all the results of that target, instead of simply one field of the first target.