Data queries with the SQL query module are run directly on the database without checking user access permissions. This means that a column to be used in the report is displayed even though the user may not have access permission to it.
| Property | Meaning |
|---|---|
|
Name |
Name of the data source. |
|
Description |
Description of data source. |
|
Max. lines |
Maximum number of result lines for this query. NOTE: The report only displays results up to this maximum even if the number of results exceeds it. In the default, no error messages or tips are displayed. Any possible messages must be customized in the report. |
|
Parent query |
Not used. |
|
Query module |
Select the SQL query module. |
|
Query |
Full database query SQL syntax. The query must contain all the columns used in the report. You can also use SQL parameters in the query. Add these parameters subsequently to the report by entering them on the Parameters tab. Syntax for parameters: @<parameter name> Syntax for parameters of Date data type and a scope (time period from/until): @<parameter name>Start @<parameter name>End Example of usage in the SQL query: and StartDate between @<parameter name>Start and @<parameter name>End |
Example:
The query should return the identities (Person table) assigned to an department. The department (UID_Department) is found with the object key (XObjectKey). This is passed as a parameter to the report. The identity‘s first name (firstname), last name (lastname) and department name (departmentname) are queried.
Select Firstname, Lastname, Departmentname
from person join Department
on person.uid_Department = department.uid_Department
where Department.XObjectKey = @ObjectKeyBase