Report builder

Report builder enables users to customize reports based on tickets' data. Users can alter the layout of report data by selecting columns, applying filters, highlighting, and sorting. Users can also define breaks, aggregations, charts, groupings, and add their own computations. Users can also set up a subscription so that an HTML version of the report will be emailed to them at a designated interval. Users can create multiple variations of a report and save them as named reports, for either public or private viewing.

The sections that follow summarize ways you can customize an interactive report.

Search Bar

At the top of each report page is a search region. This region (or Search bar) provides the following features:

• Select columns icon enables you to identify which column to search (or all).
• Text area enables you to enter case insensitive search criteria (wild card characters are implied).
• Search button executes the search. Hitting the enter key will also execute the search when the cursor is in the search text area.
• Reports displays alternate default and saved private or public reports.
• Rows sets the number of records to display per page.
• Actions Menu enables you to customize a report. See the sections that follow.

Actions Menu

Actions menu appears to the right of the Search button on the Search bar. Use this menu to customize an interactive report.

Select Columns

Used to modify the columns displayed. The columns on the right are displayed while the columns on the left are hidden. You can reorder the displayed columns using the arrows on the far right. Computed columns are prefixed with **.

Filter

Focuses the report by adding or modifying the WHERE clause on the query. You can filter by column or by row. If you filter by column, select a column (it does not need to be one that displays), select a standard operator (=, !=, not in, between), and enter an expression to compare against. Expressions are case sensitive. Use % as a wild card (for example, Subject like A%).

If you filter by row, you can create complex WHERE clauses using column aliases and any functions or operators (for example, G = 'VA' or G = 'CT').

Format

Format enables you to customize the display of the report. Format contains the following submenu:

• Sort
• Control Break
• Highlight
• Compute
• Aggregate
• Chart
• Grouping
• Pivot

Sort

Used to change the columns to sort on and determines whether to sort in ascending or descending order. You can also specify how to handle NULLs. The default setting always displays NULLs last or always display them first. The resulting sorting displays to the right of column headings in the report.

Control Break

Used to create a break group on one or several columns. This pulls the columns out of the interactive report and displays them as a master record.

Highlight

Enables you to define a filter. The rows that meet the filter criteria display as highlighted using the characteristics associated with the filter. Options include:

• Name is used only for display.
• Sequence identifies the sequence in which the rules are evaluated.
• Enabled identifies if a rule is enabled or disabled.
• Highlight Type identifies whether the row or cell should be highlighted. If Cell is selected, the column referenced in the Highlight Condition is highlighted.
• Background Color is the new color for the background of the highlighted area.
• Text Color is the new color for the text in the highlighted area.
• Highlight Condition defines your filter condition.

Compute

Enables you to add computed columns to your report. These can be mathematical computations (for example, BILLABLE/60) or standard functions applied to existing columns. Some display as examples and others (such as TO_DATE) can also be used). Options include:

• Computation enables you to select a previously defined computation to edit.
• Column Heading is the column heading for the new column.
• Format Mask is an format mask to be applied against the column (for example,S9999).
• Computation is the computation to be performed. Within the computation, columns are referenced using the aliases displayed.

Below computation, the columns in your query display with their associated alias. Clicking the column name or alias includes them in the Computation. Next to Columns is a keypad which functions as a shortcut to commonly used keys. On the far right are Functions.

The following example computation demonstrates how to display total compensation:

CASE WHEN AI = 'Techical Support' THEN B + C ELSE B END

Aggregate

Aggregates are mathematical computations performed against a column. Aggregates display after each control break and are defined at the end of the report within the column. Options include:

• Aggregation enables you to select a previously defined aggregation to edit.
• Function is the function to be performed (for example, SUM, MIN).
• Column is used to select the column to apply the mathematical function to. Only numeric columns display.

Chart

You can define one chart per saved report. Once defined, you can switch between the chart and report views using view icons the Search bar. Options include:

• Chart Type identifies the chart type to include. Select from horizontal bar, vertical bar, pie, or line.
• Label enables you to select the column to be used as the label.
• Axis Title for Label is the title that displays on the axis associated with the column selected for Label. This is not available for pie chart.
• Value enables you to select the column to be used as the value. If your function is a COUNT, a Value does not need to be selected.
• Axis Title for Value is the title that displays on the axis associated with the column selected for Value. This is not available for pie chart.
• Function is an optional function to be performed on the column selected for Value.
• Sort allows you to sort your result set.

Grouping

You can define one Grouping view per saved report. Once defined, you can switch between the grouping and report views using view icons on the Search bar. To create a Grouping view, you select:

• the columns on which to group
• the columns to aggregate along with the function to be performed (average, sum, count, etc.)

Pivot

You can define one Pivot view per saved report. Once defined, you can switch between the pivot and report views using view icons on the Search bar. To create a Pivot view, you select:

• the columns on which to pivot
• the columns to display as rows
• the columns to aggregate along with the function to be performed (average, sum, count, etc.)

Flashback

A flashback query enables you to view the data as it existed at a previous point in time. The default amount of time that you can flashback is 3 hours (or 180 minutes) but the actual amount will differ for each database.

Save Report

Saves the customized report for future use. You provide a name and optional description and can make the report accessible to the public (that is, all users who can access the primary default report). You can save four types of interactive reports:

• Public Report. Can be saved, renamed, or deleted by the end user who created it. Other users can view and save the layout as another report.
• Private Report. Only the end user that created the report can view, save, rename or delete the report.

If you save customized reports, a Reports selector displays in the Search bar to the left of the Rows selector (if this feature is enabled).

Reset

Resets the report back to the default settings, removing any customizations that you have made.

Download

Enables the current result set to be downloaded. The download formats differ depending upon your installation and report definition but may include CSV, HTML, or PDF.

Column Heading Menu

Clicking any column heading exposes a column heading menu. Options include:

• Sort Ascending icon sorts the report by the column in ascending order.
• Sort Descending icon sorts the report by the column in descending order.
• Hide Column hides the column. Not all columns can be hidden. If a column cannot be hidden, there will be no Hide Column icon.
• Break Column creates a break group on the column. This pulls the column out of the report as a master record.
• Column Information displays help text about the column, if available.
• Text Area is used to enter case insensitive search criteria (no need for wild cards). Entering a value reduces the list of values at the bottom of the menu. You can then select a value from the bottom and the selected value will be created as a filter using '=' (for example, column = 'ABC'). Alternatively, you can click the Flashlight icon and enter a value to be created as a filter with the 'LIKE' modifier (for example, column LIKE '%ABC%').
• List of Unique Values contains the first 500 unique values that meets your filter criteria. If the column is a date, a list of date ranges is displayed instead.

If you select a value, a filter will be created using '=' (for example, column = 'ABC').

Report Settings

If you customize an interactive report, the report settings display below the Search bar and above the report. This area can be collapsed and expanded using the icon on the left.

For each report setting, you can:

• Edit a setting by clicking the name.
• Disable/Enable a setting by unchecking or checking the Enable/Disable checkbox. Use this control to temporarily turn a setting off and on.
• Remove a setting by clicking the Remove icon.

If you have created a chart, grouping or pivot, you can toggle between them and the base report using the Report View, Chart View, Grouping View, and Pivot View links shown on the right. If you are viewing the chart, grouping or pivot, you can also use the Edit link to edit the settings.

Example

In the following example, the ticket list report with custom requirements is generated:

Example

1. Add columns to the Ticket list:
   • Closed on
   • Deadline
2. Filter ticket list by the Submission date: From date and To date
3. Download list in PDF, CSV (Excel) or HTML format.
4. Save the report as Private.

To generate custom the ticket list report, go to menu Reports > Report builder.

Add columns

a) Click the Actions button, and then choolse Columns from the drop-down menu:

Note

Before the column selection, make sure the list is sorted by the Submission date in the reverse order: Click the Submitted column header, and the choose the appropriate order.

b) In the columns selection window, select both Closed on and Deadline columns, and then click the Move button (>) to move columns from Do Not Display list to the Display in Report list.

c) Optionally, use the Up and Down buttons to re-arange the position of the columns in the list.

d) Click the Apply button. The new columns are now added to the list.

Filter ticket list

a) Click the Action button again, and then choose the Filter from the drop-down menu.

b) Select or enter the following values: - Column: Submitted - Operator: Between - Expression: 01.10.22 and 30.11.22.

Note

The date format for dates (Expression) must match those you set in the General settings section. You can also use the embedded calendar to select date values.

c) Click the Apply button. You'll get the filtered ticket list.

Download list

a) Click the Action button, and then choose the Download from the drop down menu.

b) Choose the desired format: CSV, HTML or PDF.

Tip

In case you choose the HTML format, you can additionally filter the list using the Search button in the downloaded report.

Save report

a) Click the Action button again, and then choose the Report > Save Report from the drop-down menu.

b) Enter the report Name and Description.

Afterward, you'll be able to choose the saved report as Private and run fom the drop-down list of reports.