Report Generator for Maytas Hub

This feature requires Reports Library 1.0.4.5 or later to be applied, as per the instructions in the Maytas 5 release notes.

Maytas Hub allows you to create your own simple custom reports, with many of the same capabilities as Maytas 5's Report Generator / MList module. This is achieved via an inbuilt report which lets you choose which tables and columns to include, configure them as required, then view the results. A separate, limited version of the report is available which only allows a read-only view of custom reports, so that report creation and report viewing can be given separate permissions.

The two versions of the report can be run as follows:

  1. In Maytas Hub, go to Reports on the sidebar.

  2. From the dropdown list of reports, select the required report and click Load:

    • Report Generator Editor - This is the version used to create and edit custom reports. Report results are also viewable in this version. Access to the editor is restricted by default.

    • Report Generator - This is the read-only version used to view custom reports.

The Report Generator Editor is a powerful tool which assumes at least a basic knowledge of the Maytas database structure. If you are unsure on how certain tables are linked, we suggest using the Entity Relationship Diagrams as a starting point. We also recommend limiting access to the Report Generator Editor to users who are permitted to view all data in the database, as the editor effectively allows you to create reports to view any table in the database.

Enabling the editor

By default, the Report Generator Editor is restricted for all users, as it allows a large degree of access to data. Access is granted to users by modifying the value in the RS_REPORTS.REPORTPERMISSIONS column:

  1. In Maytas 5, go to Tools | Table Edit.
  2. Select the RS_REPORTS table from the dropdown box. If the table is not available, please see the instructions on the Table Editor page for information on including it.

  3. Locate the row for the Report Generator Editor report.

  4. Change the REPORTPERMISSIONS value as required. For example, if you have a number of users in a report group with the same value in M32$_WEBUSERS.REPORTPERMISSIONS, the value in RS_REPORTS.REPORTPERMISSIONS can be set to the same number to grant access.

    Please raise a request on the Self Service portal if you require assistance with what value to enter.

  5. Click OK.

How to create a new report

These are the basic steps for creating a new report. A more detailed guide can be found below in the Editor details section.

  1. Run the Report Generator Editor.
  2. Click Create new report.
  3. Choose a base table, any required joins and the columns to include on the report.
  4. Enter a report name and folder name (the report will be listed under the folder name header on the list of reports).
  5. Other parameters are optional. Click View Report when ready.
  6. Click Save report, then click Return to reports list and reopen the report. This step is very important immediately after creating a new report, as issues can occur otherwise.

How to order columns

  1. Run the Report Generator Editor.
  2. Open the custom report you wish to configure.
  3. Click Column ordering.

  4. Use the red buttons on the column headers to move columns left or right.

  5. Click Finish ordering.

How to edit column settings

  1. Run the Report Generator Editor.
  2. Open the custom report you wish to configure.

  3. Click a column header to open the Column Config screen.

  4. Choose options as required. To change the column title (i.e. what is displayed on the column header) or use custom SQL in the report, enter the required value and click View Report.
  5. Click Save changes. Click Return to report when finished.

Custom SQL

As with the Custom option in the Maytas 5 Report Generator, the custom SQL option allows you to enter SQL to use in place of the column (i.e. a sub-select).

When using custom SQL, if you reference a table that is already included in the report, the table should be aliased in the custom SQL to distinguish it, else the report may use the wrong table.

For example, if you have TRAINEE as your base table and you want to select DOB in the custom SQL, you could alias the TRAINEE table as t:

select DOB from TRAINEE t where t.TRAINEEID = TRAINEE.TRAINEEID

If the custom SQL results in report errors and you can't get to the column to fix it, there is an option on the reports list to clear all custom SQL from a report:

Please be aware that this will clear ALL custom SQL and custom filters on the report. If you need to fix some custom SQL but need to preserve some other custom SQL in the report, this can be done manually in the database by clearing the column M5REP_REPGEN_COLUMNCONFIG.CUSTOM for the relevant row. Please contact the Service Desk for assistance with this if required.

  1. On the Report List screen, set the Show option to clear custom SQL parameter to Yes and click View Report.

  2. Click Clear custom SQL for the required report.

  3. Click Yes to confirm.

How to add criteria to a column

  1. Run the Report Generator Editor.
  2. Open the custom report you wish to configure.
  3. Click a column header to open the Column Config screen.
  4. Scroll down and click the Add criteria button.

  5. In the parameters, select the criteria function and enter a value (e.g. Begins with and A), then click View Report.

  6. Click Return.

  7. Additional criteria can be added as required by repeating steps 4 to 6.

Editor details

The Report Generator Editor features the following screens, which can be navigated from top to bottom:

Parameters

Report screen Parameter Details
Reports List Show option to clear custom SQL When set to Yes, a Clear custom SQL button will appear next o each report, which allows you to clear out all custom SQL on a report in the event that it is causing the report to error
Basic Details Base table The base table is the starting point for the data in your report. Typically it should be the most relevant table to the data you wish to show - e.g. for a report listing learners, in most cases you would use Trainee Basic Details (TRAINEE).
Table joins

There are five parameters for table joins. This works hierarchically, where the first join parameter allows you to select tables that can directly join to the base table, the second join parameter has tables that can join to those selected in the first parameter, etc.

For example, if you had TRAINEE as the base table and you wanted to include assessors and employers in the report, in the first join parameter you would select Assessor Trainee Link (TRASSESSOR) and Employer Trainee Link (TREMPLOYER), then in the second join parameter you would select Assessor Details (ASSESSOR) and Employer Details (EMPLOYER).

Each join parameter only shows the tables it is possible to join to, along with the table it would join from (e.g. Assessor Trainee Link -> Assessor Details).
Columns to include This lets you select the columns to include in the report
Report name The name of the report
Report folder name This will group the report under the entered folder name on the Reports List screen
Colour scheme The colour to use for the header and footer of the report
Show SQL When set to Yes, the report will display the SQL that is being generated to produce the report results. This is primarily intended for troubleshooting purposes.
Hide buttons When set to Yes, everything except the results table will be hidden. This is intended for when you wish to export the report to Excel or PDF.
Show hidden columns When set to Yes, the report will display columns that have been set to hidden on the Column Config screen
Only include live learners When set to Yes (the default), only learners without a POT termination date will be included in the results
Custom filter

This can be used to enter a SQL filter for the report's WHERE clause, by unticking the NULL box and entering the SQL.

For example, if you wanted to filter by learners with a start date after 01/08/2023, you could enter:

TRAINEEPOT.STARTDATE >= '01-AUG-2023'

Additional filters can be added by separating them with AND - e.g.:

TRAINEEPOT.STARTDATE >= '01-AUG-2023' AND TRAINEEPOT.STARTDATE <= '31-JUL-2024'

Column Config Column title The title to display on the column header
Custom SQL SQL entered here will be used in place of the column's data. See here for details.
Add Criteria Function The criteria function to use (e.g. empty, greater than, begins with, etc)
Value

The value to compare the column's data against. For example, if you want to only include learners with surnames beginning with A, you would select Begins with as the function and enter A as the value.

For the Empty and Not empty functions, the value can be left blank.

Table Joins

If a report uses table joins, each join will be shown at the top-right of the Basic Details screen.

For each join, you can choose whether it is a left, inner or right join.

For information on how each join type works and when to use them, see here. Note that while this guide is for the Maytas 5 Report Generator, the logic and information around joins is equally applicable to the Maytas Hub Report Generator.

User Filtering

Report Generator for Maytas Hub will filter results for the logged in user as follows:

After creating a new report and clicking save, the user filter will only be applied after returning to the reports list and re-opening the report.

Limitations and known issues

While the Maytas Hub Report Generator includes many of the features of the Maytas 5 Report Generator, there are some features which are limited or not included. Additionally, there are minor known issues which users should be aware of: