Skip to content

User Defined Reports

The User Defined Reports feature allows Site Administrators to create custom, SQL-based reports that retrieve data directly from the tenant database. Once created, these reports can be accessed by users with the Site Auditor or Site Administrator role through Reports > User Defined.

User Defined Reports provide flexibility for administrators to design and manage custom reports using SQL-like queries. The reports support several interactive and export features, enabling users to explore and share report data efficiently.

Key Features

  • Filtering: Reports may include configurable filters, allowing users to refine displayed data (if supported by the report design).
  • Sorting: Columns can be sorted directly from the report interface.
  • Pagination: Results are paginated to improve performance and usability.
  • Exporting: Reports can be exported to PDF or CSV formats for offline use or distribution.

Two sample reports are included to provide report design examples:

Management > Scripts > Report - Asset Types Count
Report to display the distribution of asset types in the site.

Management > Scripts > Report - Monthly Sessions By User
Report to summarize monthly sessions by user.

Creating a User Defined Report

To create a new User Defined Report:

  1. Create a Script: Navigate to Management > Scripts and click Add.
  2. Define Report Parameters: Select the following parameters:
    • Name: Enter a unique, per understandable Name as this is what will be displayed in the User Defined Report list.
    • Description: Optionally, add a description.
    • Type: select SQL.
    • Role: select Report.
    • Orchestration: select Standalone.
    • Parameters: leave empty.
    • Code: Enter your HQL report script.
    • Parser: select Empty Parser.
  3. For the report Code, use HQL (Hibernate Query Language):
    • HQL supports multiple databases and operates on application objects rather than raw database entities.
    • This approach provides database independence and simplifies data access within the application environment.

Example Code from the default report named Report - Monthly Sessions By User

select 
    user.name as User, 
    count(*) as Sesisons
from 
    SessionEntity
where
    created > :month
group by 
    user.name

Note: System objects referenced in HQL are documented in the API Documentation linked from the My Profile > About page.

Supported Placeholders

HQL reports support the following dynamic placeholders:

Placeholder Description
:now Current date and time
:week Date and time one week ago
:month Date and time one month ago
:filter User-provided filter value

These placeholders can be used within the query to dynamically adjust the data returned at runtime.

Column Naming

Columns in a report are defined in the HQL query using the standard SQL AS clause. For example:

SELECT s.userName AS User, s.loginTime AS LoginTime FROM SessionEntity s

If no aliases are defined, columns will automatically be named Column-1, Column-2, and so on.

Running Reports

User Defined Reports can be executed in two ways:

  1. From the Reports Menu:
    Navigate to Reports > User Defined to view and run published reports.

  2. From the Script Interface:
    Reports can also be executed directly from the script editor after a script is created, or from the script list view for any Report type scripts. This enables rapid testing and development of new reports.