Report builder
This feature is part of Moodle Workplace™, which is available through Moodle Certified Partners and Service Providers only.Moodle Workplace provides a powerful custom reporting tool. Custom reports are built using the report builder interface, which provides advanced customisation options to administrators.
Overview
Moodle Workplace distinguishes between two types of reports:
- System reports: Embedded, pre-defined reports that are part of Moodle Workplace. Every time you see a table that can be interacted with (sorting columns, applying filters, or downloading data in various formats), you are most likely using a system report. System reports cannot be modified or removed, since various Workplace features rely on them. Examples are the list of custom reports or report schedules.
- Custom reports: User-generated reports made available to other users such as managers and trainers.
Moodle Workplace supports two types of reporting data sources: the Workplace database and the Workplace data store. The database is where all Workplace data is stored, excluding files. The data store is an alternative data source that keeps track of permanent records in Workplace. The following diagram illustrates the high-level elements of the reporting workflow:
Workplace offers a range of report sources to simplify the usage of the report builder. A record source contains a predefined set of fields and relationships that are available when building a report. Based on the selected report source, a certain amount of data will be available, which can be reduced by the following mechanisms:
- Configuring data fields lets you decide which columns are being made available, for example, user name, certification name, and completion status.
- Applying conditions lets you decide which rows will be made available, for instance, all records from a selected department.
- Access to reports is granted via audiences, for example, all members of a selected cohort.
- Filters, which have to be configured by the report creator, allow the viewer to select one or more criteria to reduce the amount of data further, for instance, by the level of progress.
Creating custom reports
You can access the management of custom reports via Site administration > Reports > Manage custom reports, or directly via the Report builder icon in the Workplace launcher. This functionality is only available if the Enable custom reports setting (enablecustomreports) in Site administration > General > Advanced features remains enabled.
To create a new report, select the +New report button.
A report contains two required settings: Name (displayed whenever the report is being made available to users) and Report source (defines where the data for the report will come from). When the Include default setup option remains enabled, the report will be populated with several predefined columns, conditions, and filters. If the Remove any duplicate rows setting is enabled and identical rows are available, only a single row will be kept. This setting has no effect if report columns are aggregated. Optional Tags can be provided for better report management and filtering.
The following report sources are available in Moodle Workplace, grouped by report plugin:
- Site
- Badges: Badge details, badges issued, badges received, and courses
- Blogs: Blog content, tags, users, and courses
- Cohorts: Cohorts and cohort members
- Comments: Comments and users
- Course categories: Category details and courses, cohorts, roles, and users
- Course participants: Course details, user enrolments, course completion, jobs, and tenants
- Courses: Courses and course categories
- Files: Files and users
- Groups: Groups, groupings, and group memberships
- Notes: Notes, recipients, authors, and courses
- Roles: Roles, role assignments, contexts, and users
- Tags: Tag collections, tags, tag instances, and tag authors
- Task logs: Tasks and users
- User badges: Badges issued to users in courses, including badge details
- Users: Users and their job assignments
- Appointment
- Appointments: Seminars, sessions and participants
- Certificate manager
- Certificate templates: Available certificate templates
- Issued certificates: Issued certificates to users
- Certifications
- Certification users allocation and completion: Certification details, including users’ progress information
- Certifications: Certification details
- Datastore
- Course completion from datastore: Data in Workplace datastore
- Program
- Program users allocation and completion: Program details, including users’ progress information
- Programs: Program details
- Workplace
- Course reset for individual user: Course resetting data during recertification
Once you have created the report, you will be redirected to the actual report builder.
Report Editor
The interface of the report builder is divided into three panels:
- Columns sidebar (left): Shows the list of available data fields grouped by entities, including a live search option. To add a field to the report, simply select the respective field name.
- Preview window (middle): Shows the data according to the current report configuration. Column headings and aggregation methods can be edited inline. To grasp a view of what the report will look like to users, select the Preview button.
- Settings sidebar (right): Lets you configure conditions, filters, sorting, and card view options.
Viewers also have the option to download the table data in various formats, namely CSV, XLSX, HTML, JSON, ODS, and PDF. You can reduce or rearrange the options in that list in Site administration > Plugins > Data formats > Manage data formats.
Configuring fields
In the left-hand side panel of the report generator, you will see all available fields for the selected report source, which have been grouped into entities. Each entity can be expanded or collapsed for better usability. You can also search for field entries to limit the number of displayed items.
To add columns to the report, click on the selected field in the list on the left. The column will be added automatically as the rightmost column in the table. You can then rearrange the column order via the standard Move handle. To remove a column, click on the standard delete icon.
The following field types are available to be added as columns to a report:
- Text fields: For example, full name, job, or program name. Some text fields are preprocessed by the report generator to support additional functionality:
- Hyperlinked text fields: Mainly to direct viewers directly to entries, for example, to a user profile or a program
- Highlighted text fields: To visualise a status, for example, status fields in programs and certifications
- Calculated text fields: To provide a numeric value, for example, the number of courses in a certification or the course progress for each user as a percentage
- Grouped text fields: To provide multiple data points as a single cell, for example, all users registered for an appointment. The values are available one per line or comma-separated.
- Image fields: For example, program icon or user picture
- Actions: For example, sending a message to a user or viewing a progress report
Custom fields, such as custom user profile fields or custom program fields, are fully supported by the report generator. However, if the visibility of a custom field can be configured (for example, custom user profile fields), it has to be set to "Visible to everyone" for the field to appear in the list of available fields.
Aggregation and Grouping
The Workplace report generator supports various aggregation and grouping operations at the column level. Depending on the data field selected, a set of possible aggregation methods is offered. Here is a full list of methods, along with their SQL function used internally:
- Average: Average of all (numeric) values / AVG()
- Count: Number of values in column / COUNT()
- Count distinct: Number of unique values in column / COUNT DISTINCT()
- Comma separate values: List of data points, separated by comma / GROUP_CONCAT()
- Comma separate distinct values: List of unique data points, separated by comma / GROUP_CONCAT(DISTINCT)
- Maximum: Biggest (numeric) value in column / MAX()
- Minimum: Smallest (numeric) value in column / MIN()
- Percentage: Ratio of (boolean) values in percent / AVG()
- Sum: Sum of all (numeric) values in column / SUM()
An aggregate function calculates on a set of values and returns a single value. Because an aggregate function operates on a set of values, it regularly applies grouping to fields, which divides the result set into groups of value, where the aggregate function returns a single value for each group. In the examples below, you can see the impact on grouping on the Seminar field, once the Average function has been applied to the Capacity field.
Report settings
The accordion panel on the right lets you define conditions and filters, set the default sorting order, and configure card view options.
Conditions
Conditions let you limit the number of data that is being presented to the report viewer. They are a predefined set of criteria that are applied when viewing a report. Conditions cannot be changed in viewing mode. Depending on the selected report source, the available conditions might differ slightly from the list of available fields.
There is a special condition related to users called Relation to the report viewer, which specifies which users should be listed in a report relative to the person viewing it. Using this condition, it is possible to create a single report that shows different results to different users, depending on their department or managerial duties.
The first relation is called Themselves, which limits the report to information relating to the report viewer. The Customise... option provides the following two user relations:
- Reports to the report viewer: For users with a managerial position, this option will include users who report to the user viewing the report.
- Direct reports only: This narrows down the previous option to users who report directly to the user viewing the report. A direct report is a manager user in a position that is a direct child of the managers position.
- In the same department as the report viewer: Includes users of the same department (optionally including sub-departments) as the user viewing the report.
If both options are selected, it is possible to combine them with the AND and OR Boolean operators to refine the report content further. According to the user who is viewing the report, both relations take effect, meaning it will return different results according to the report viewer and/or their own position within the organisation.
Filters
Filters are a predefined set of criteria that are not applied automatically but are available for report viewers (via the Filters icon). Viewers of the report then have the ability to reduce the amount of data further via filters.
There is no limit to the number of filters in a report. Furthermore, the list of filters is not limited to the fields shown in the report - you might add a filter to narrow down the search by fields that are not shown in the report but available in the data.
Sorting
Sorting defines the initial order that fields are being ordered by.
Once a field has been selected, the sorting order can be reversed by toggling the Up/Down icon. When in viewing mode, the order of all selected fields will initially be applied. Once a user has clicked on a column name, the data will be sorted by this field. When the same column is clicked again, the sort order will be reversed.
Card View
The card view feature supports responsive layout design in the report builder. Card View allows the configuration of the report layout for narrow views when the block is in the sidebar or a smaller device is used. A report will automatically switch to card view when displayed on small screens or in a narrow container via the report block in the sidebar, for instance, on the dashboard.
The following card view configuration options are available for every custom report:
- Columns visible: indicates the number of columns that will be displayed expanded in the card view.
- First column title: whether the first column title is shown or not.
In the screenshot below, the number of columns always visible has been set to 3 with the first column title hidden.
Report Audiences
Report audiences indicate which users have access to the report. They can also be used as recipients in scheduled reports. To access audiences, select the Audiences tab of a report.
In addition to selecting which position and department within an organisation should be included, it is also possible to manually select individual users and/or e-mail addresses. You can create the following audiences:
- General
- All users: Use this audience to give access to all users in the tenant to any report
- Assigned system role: Select at least one system role
- Manually added users: Select at least one user (via name or e-mail address). Only email addresses of registered users being are supported as schedule recipients.
- Member of cohort: Select at least one cohort
- Site administrators: Grant access to all site admins
- Organisation structure
- Job assignments: Select a Department and a Position, and optionally include subdepartments and subpositions, respectively
- Managers: Make reports available to managers. The Manager type options available are Manager, Manager (assigned manually), and Department Lead.
- Multi-tenancy
- Tenant administrators: Grant access to the admins in the tenant
To confirm which users now have access to the report, switch to the Access tab.
Scheduling Reports
All reports are available as pull reports; that is, users have to select and view a report proactively. The report scheduler lets you configure the automatic delivery of reports to specific audiences, also known as push reports.
To access the scheduler, select the Schedules tab of a report and select the + New Schedule button at the top right.
Each schedule contains four parts:
- General
- Schedule name is only used in the list of schedules
- Format: Any file format that has been allowed in Site administration > Plugins > Data formats > Manage data formats
- Starting from: Specifies when the report is to be sent for the first time
- Recurrence: Lets you specify whether the report should be sent once (None) or sent frequently: Daily, Daily (weekdays only), Weekly, Monthly, Annually
- View report data as: You can specify as which user the created report will be viewed as.
- Schedule creator (default): The generated report will contain data as if viewed by the user who created the schedule
- Schedule recipient: The generated report will contain data as if viewed by the recipient; that is, each user will potentially receive a different (personalised) report
- Select user: If a user is selected, the report data will be included as if viewed by the selected user; that is, all recipients will receive the same report
- Audience: To select which users will receive the scheduled report, you need to select at least one report audience. Scheduling a report will not be possible without any audiences, which have to be created first.
- Message content: Standard elements of any email message, namely Subject and Message.
- Advanced:
- If the report is empty: Choose an option what should happen when the report to be sent contains no data. The self-explanatory options are Send message with empty report (default), Send message without report, and Don't send message.
The cron process triggers the execution of the report. If you want to send a report straight away, select the Send schedule option from the actions in the list of schedules.
Report Access
By default, all users with permission tool/reportbuilder:read or tool/reportbuilder:edit can view all custom reports defined in their tenant. Additional audiences can be specified to grant access to a report.
To see who can view the report, select the Access tab of a report. Users who can see all reports in the tenant are labelled "Can view all reports"
In addition to granting user access to reports, it is also possible to specify which users should be listed in any given report. To achieve this, the "Relation to the report viewer" condition should be added to the report. This allows a manager to create a single report for others to view information about themselves or users in their departments or reporting lines. The condition takes effect according to the user who is viewing the report, meaning it will return different results according to the report viewer and/or their own position within the organisation.
After enabling Shared space, users can create shared reports inside of it. These shared reports will be listed on the custom reports page in all tenants and marked with a "Shared space" label.
This feature enables users to use the same report definition site-wide without duplicating the same reports in all tenants. It also allows the creation of cross-tenant reports. When a shared report is viewed from inside the tenant, it only displays the users and entities from this tenant. When a shared report is viewed from Shared space, it shows information from all tenants.
Reports in the shared space can be shared with all tenants or be only accessible to users with access to the shared space.
To create a shared report, switch to Shared space from the tenant switch dropdown in the navigation bar. When inside the Shared space, go to Report Builder in the Workplace launcher and create a new report as usual.
Reports in Shared space have all the features of non-shared reports, including audiences and schedules. However, there are two differences when working with reports in Shared space compared to standard tenant reports:
- Available in all tenants setting. If enabled, the report will be made available to all tenants. If this is not set, the report will be available only in the shared space. This is indicated by the Shared space indicator in the list of all reports.
- Some audience types might not be available in the shared space.
Data Store
The Workplace data store is a permanent record store used to keep track of course completions and badges awarded.
The data store supports course completions that were carried out outside Workplace but have to be tracked in Workplace. Any user who has the 'tool/datastore:uploadcoursecompletion' capability can upload course completion data, which will be stored in the data store for users who belong to the same tenant as themselves. The user upload feature has been extended with two new fields to support the course completion data:
- coursecompleted: Courses are matched on the short name of the course that has been completed. The course has to exist before the upload is started.
- coursecompleteddate: The date that the course was completed. You need to use the YYYY-MM-DD ISO standard format, which will then be localized during the upload process. If this field is omitted, the current date will be used.
Both variables have to have a numeric postfix to support multiple courses, for example:
username,coursecompleted1,coursecompleteddate1
user1,course1,2021-11-29
user2,course1,2021-01-01
user3,course2
Data in the Data store can be reported using the "Course completion from datastore" report source.
Custom report settings
The following settings can be configured for the report generator by navigating to Site administration > Reports > Report builder > Custom report settings:
Custom reports limit restricts the number of custom reports that can be created. If set to zero, then there is no limit.
For performance reasons you may consider disabling Custom reports live editing, to avoid the constant updating of report data while editing report content (columns, filters, conditions).
Both settings can also be specified by adding the following line(s) to the site configuration:
$CFG->customreportslimit = <VALUE>;
$CFG->customreportsliveediting = false;
The following config-only settings lets you limit the maximum number of custom reports for tenants. The value applies to all tenants across your site.
$CFG->tool_tenant_customreportslimit = <VALUE>
The settings $CFG->tool_reportbuilder_limitsenabled, $CFG->tool_reportbuilder_sitelimit, and $CFG->tool_reportbuilder_tenantlimit from Workplace 3.11 will only apply to the outdated reports from the tool_reportbuilder.
