Working with the Ad Hoc reports tool

Some job responsibilities may require extracting job-specific data from the system. Use the ad hoc report feature to create, save, modify, share and schedule your own custom reports.

The Ad Hoc reports tool may be utilized for two primary purposes, described below:

Analytical tool. Administrators and DBAs may use the tool to gain insight into the database structure and analyze how tables are joined.
Data extraction tool. Administrators and other roles may use the tool to create reports that users may run to extract data that is relevant to specific job roles. For example, Supervisors may need to identify overtime worked within their employee group on a pay period basis. Administrators or Technical Support can provide a solution with an ad hoc custom report.

Adhoc Report page contents

The Adhoc Report page includes features that you can use to create simple reports to more complex reports. Some of the features require skills outside the scope of this document (for example, SQL programming language.) Each feature is described below.

General Information

This area of the ad hoc report includes high level information. Here is a brief description of each field:

Name. The name of the report. This is a required field. The field supports up to 100 characters.

Description. A short description. The field supports up to 255 characters. If the report will be available to other users, be clear about the purpose of the report.

Owner. The name of the user who created the report.

Row Height. The row height for the report output, in pixels. If the value is too small for the content of a cell, it will expand vertically. You can specify the width of each column (see “Columns.”)

Report Type. The type of data that the report extracts from the database. One report type may be selected for each report.

User Data. User data is directly associated with one or more users (for example, employee profiles, timesheets, leave balances, timesheet profile data, etc.) This is the default option.

Audit Data. Audit data is directly related to audit information (for example, invalid login attempts, etc.). This option is limited to Administrators.

Datasets. Datasets are useful for lookup values, for example, lists of Alternate Work Schedule values, Transaction Code values, Tour or Duty values, etc.

SQL Override

The SQL Override feature is only available to Administrators. This feature provides a text box for manual entry of Structured Query Language (SQL) commands and may be used independently or in conjunction with the Columns table so that you can give the manual SQL columns meaningful names on the report. Only product tables can be queried (i.e., you cannot query PostgreSQL system tables.) Reports that include the use of this feature cannot be published for access by other users.

Note: The SQL Override feature can be disabled if there are concerns about security. To prevent the use of the SQL Override functionality, submit a request to Community for a script. The script will disable the feature.

Generated SQL

This feature provides insight into the database structure and allows Administrators and DBAs to examine how tables are joined. Copy and paste the generated SQL into a third party tool to analyze the generated SQL.

Columns

This feature lets you specify what data you want to be displayed in the report, for example, the employee’s first and last name, their Tour of Duty, and the Timesheet Status Code. You can label each column and adjust the width of the row that is displayed in the report output, or accept defaults. You can also specify aggregate functions. Here is a brief description of each field:

Function. A list of aggregates that are used in conjunction with the Group Bys feature.

SUM. Adds values together. For example, use SUM to total hours in the timesheet pay period instead of listing separate daily totals.

AVG. Average of all the values.

MIN. Smallest of all the values.

MAX. Largest of all the values.

COUNT. Count of all the values.

Column. A list of data that you can specify for display in the report.

Name. The name for the data that you selected from the Column field. (This is a required field. The system displays an error if the Name is blank.)

Width. The width of the column, in pixels. By default, the width is 75.

Conditionals

This feature lets you construct custom queries that include conditions.

Connector. A list of logic connectors. Use connectors to construct conditionals. For example, you might construct a conditional that performs the following query: “Select data where ((first name is Bart and last name is Berman) OR (first name is Rashid and last name is Ahmed) AND (pay period is not 0000-01)). The query would select timesheet data for Bart Berman and Rashid Ahmed but not for pay periods with the year “0” and pay period number “1.” Connectors are listed below:

( )

AND

NOT

Column. A column that forms the first piece of the conditional.

Operator. A list of logic operators. Use operators to construct conditionals. Operators are listed below:

Equal to. The value in the Column is exactly equal to the Value Type and Value.

Case insensitive equal to. The value in the Column is equal to the Value Type and Value, whether or not the value is upper or lower case.

Not equal to. The value in the Column is not equal to the Value Type and Value.

Less than. The value in the Column is less than the Value Type and Value.

Less than or equal to. The value in the Column is less than or equal to the Value Type and Value.

Greater than. The value in the Column is greater than the Value Type and Value.

Greater than or equal to. The value in the Column is greater than or equal to the Value Type and Value.

Like. The value in the Column is a case-sensitive wild card.

Case insensitive like. The value in the Column is a case-insensitive wild card.

In. Use the “in” operator in conjunction with the “query” Value Type to run a sub-query.

Is null. The value in the Column is blank.

Is not null. The value in the Column is anything that is not blank.

Value Type

Literal. The value in the Column matches whatever is specified in the Value field.

Runtime Parameter. The value in the Column matches whatever the user types in the Report Parameter field.

Query. Lists all of the ad hoc reports you have created. This allows you to run an existing ad hoc report as a nested sub-query. Use this in conjunction with the “in” or the “equal to” operators.

Table Column. Links two table columns with each other. For example, you can compare the first and last name of an employee against the first and last name of their Timekeeper. If there is a match, the employee may be their own Timekeeper (unless there is another user in the system with the same first and last name as the employee in question.)

Group Bys

This feature works in conjunction with the aggregate function specified in the Columns feature. For example, if you specify SUM for total hours in the user’s timesheet, you would also need to specify how the data will be grouped.

Order Bys

This feature lets you specify the sort order in which the data will be presented in the report. Enable the check box to list data in ascending order.