Custom Report Advanced Options

Several advanced options are available to provide additional tailoring of the custom reports.

Select Optional Tables for Custom Reports

When creating a custom report, you have the option to choose additional tables. If an optional table is missing data for any records in the report, its selected columns will display empty values. For instance, if you select "Direct Appraiser - User ID" as a column in a report listing learners, and wish to include those without a direct appraiser, mark the "Direct Appraiser" table as optional.

By default, all tables are selected as optional.

To select optional tables

  1. Go to Manage Center > Reports > Report Generators > Report Wizard.
  2. Locate and open the appropriate custom report.
  3. Click Advanced.
  4. Click Ext. tables. The tables you can select as optional are listed.
  5. Select a table's check box to include rows in the report output that do not have a record in the table. Alternatively, clear its check box to exclude results where the table contains no data.
  6. Click Save.

Select Custom Report Columns Sort Columns

You can determine how your results are arranged. They can be sorted based on one or multiple columns, regardless of the order chosen, but they will always be sorted in ascending order. To configure the sorting of a report, choose the columns to sort by and specify their sequence. For instance, you could sort by "Module ID" first and then by "User ID" to organize the results by course and, within each course, by participant.

You can sort the columns in reports output to CSV files directly in a spreadsheet application.

To select the sort columns

  1. Go to Manage Center > Reports > Report Generators > Report Wizard.
  2. Locate and open the appropriate custom report.
  3. Click Advanced.
  4. Click Sorting. The tables you can sort on are listed.
  5. Select the check boxes of the tables you want to sort on.
  6. Enter the order in which the columns are used to sort the results. Enter 1 for the highest priority, 2 for the next highest and so on.
  7. Click Save.

Edit Custom Report SQL Selection Criteria

In custom report creation, you have the option to add extra filter conditions to the SQL WHERE clause for ad hoc filtering. Additionally, you can modify the WHERE clause itself, reflecting the filters chosen on the Filters page of the Report Wizard. The SQL statement provides a view of the tables available for your filtering requirements.

To edit the SQL for selection criteria

  1. Go to Manage Center > Reports > Report Generators > Report Wizard.
  2. Locate and open the appropriate custom report.
  3. Click Advanced.
  4. Click Edit SQL. The SQL SELECT statement for the report is shown, along with the SQL for the filter.
  5. Enter your additional filter criteria in the bottom, editable text box. Start with AND.
  6. Optionally, select the check box to edit the original filter criteria (the WHERE clause).
  7. Click Save.