Create a report

  1. Click Reports > Report builder > Manage reports on the Site administration menu.

  2. There are two types of reports:

    • User Generated Reports: User generated reports are the reports that site administrators create and make available to other users. Once a report has been created, it can be viewed by clicking on the title or view link. To update a report, click the Settings icon button under the Options column or remove it using the delete button.

    • Embedded Reports: Embedded reports are used throughout Totara Learn to display data. The Alerts panel shown in the screenshot below displays the Alerts report. Click the Settings button under the Options column to modify an embedded report. To reset the report back to its default settings, click the Restore Default Settings button under the Options column.
  3. To create a new report, scroll to the bottom of the Manage reports page and enter the following:

    • Report Name: Enter a name for the report that the user will use to run the report.
    • Source: Select the data source for the report. See Report Sources for more information on available data sources and options. 
    • Hide in My Reports: When checked, this report will not be displayed on the My Reports page for users, but will be accessible via its URL.

  4. Click the Create report button.

(This video is taken from the Reports course in the Totara Academy, where you can access more resources and learning materials - including other videos). 

Clone a report

Once a report has been created it is possible to clone it. Both user created and embedded reports can be cloned.

  1. Select the user generated or embedded report.
  2. Click the Clone report icon.
  3. Click the Clone button.

Cloning a report creates a full copy of the report and its columns, filters and settings.

Cloning a report does not copy any scheduling or caching that has been set up for the report.

In order to clone a report the user must have the Create, edit and delete report builder reports (totara/reportbuilder:managereports) capability.

Configure your report settings

General

Here you can update any of the following general report settings:

  • Report Title: This is the name that will appear at the top of your report page and in the Report Manager block.
  • Description: When a report description is created the information displays in a box above the search filters on the report page.
  • Source: The source for this report will be displayed.

    Once saved, a report source cannot be changed. To use a different report source a new report must be created.

  • Hide in My Reports: When checked, this report will not be displayed on the My Reports page for users, but will be accessible via its URL.
  • Number of records per page: Enter the number of rows that will appear on each page of the report. At the bottom of each page, the user can click to view the next page of data.
  • Report type: This is an informational field that displays whether this is a user generated report or an embedded report.

Columns

The Columns tab allows you to select the columns that will be available on the report.

A set of default columns will be displayed based on the report source that was selected. Additional columns can be added by clicking the Add another column dropdown.

Data available for reports depends on the source, but may include:

  • User profile information.
  • Competency details and learner completion status.
  • Course details and completion status.
  • Seminar event details and attendance.
  • Assignment submissions.
  • Audiences.
  • Program details and learner completion status.

See Report Sources for more information on available data sources and column options.

Any custom fields added will automatically be available in the relevant report column.


Aggregation or grouping

For each record that has the same value a calculation that is related to the column field type can be chosen, This includes

  • Counts for each record, count of unique values in the field. 
  • Average, Total, Max, Minimum for numeric columns.

Time period from hours, days, weeks, months for time columns.

Some reports already use data aggregation internally, in which case a warning is displayed under the report Columns tab.

It is not possible to have multiple count aggregations in a report that use the same field for data. This is because once the field has been grouped for the first count is it not ungrouped again, meaning any subsequent counts will simply reflect the number form the initial count. 

Therefore it is advised where possible you use columns that are already aggregated. 

For example, if you wanted to aggregate course completion, using both the Number in progress and the Number completed columns from the Course Completion by Organisation report source will work, as these columns already contain an aggregated number.

However, using both the Is in progress? and the Is complete? columns from the Course completion report source with the count aggregation set on them will not work, as the aggregation would be correctly calculated for the first column but then repeated in the second as they use the same field for aggregation. 

Aggregation examples

Assume that the following is the output of my report with no aggregation or grouping turned on:

USER NAMECOURSE 
Sample UserCourse A 
Sample UserCourse B 
Sample UserCourse A


Comma separated values: Lists the values from the column in one row.

If the Comma Separated Values option is turned on for column two (the courses) it produces:

USER NAMECOURSE 
Sample UserCourse A, Course B, Course A


CSV without duplicate: Same as CSV, but if Course A is listed twice, this would just show once for the user.

USER NAMECOURSE 
Sample UserCourse A, Course B


Count: Instead of listing course names, it would have a count for the column.

USER NAMECOURSE
Sample User3


Count unique: Eliminates duplicates from count

USER NAMECOURSE
Sample User2



Grouping examples

Dates: Date groupings will group by the date/time format chosen.

Assume that the following is the output of my report with no aggregation or grouping turned on:

USER NAMELAST SYSTEM ACCESS DATE
Sample UserJanuary 1, 2010
Sample TwoFebruary 1, 2016
Sample ThreeJanuary 1, 2016


To produce the numbers, not the names, of the users accessing the system set the User Name or ID to a count field and the date field use whatever grouping format required..

User Name (Count) | Last System Access Date (YYYY)

USER NAMELAST SYSTEM ACCESS DATE
12010
22016


User Name (Count) | Date YYYY-MM

USER NAMELAST SYSTEM ACCESS DATE
1January 2010
1February 2016
1January 2016


User Name (Count) | Month of Year

USER NAMELAST SYSTEM ACCESS DATE
2January (even if these aren’t the same year, both accesses took place in a January!)
1February


Customise heading: Next to each column, tick the check box under the Customise heading and enter a new column title if you want to override the existing title.

Options: Click the Show/Hide icon to hide a column from the report. To delete a column from the report, click the Delete icon. Click the up and down arrows to rearrange the order of the columns.

Sorting: The Sorting section allows you to setup default behaviour for column sorting. Select a Default column to sort the report by and the Default order to sort by. Note that the user running the report will be able to sort by other columns as well by clicking on the column heading.

Graph

This allows the results from the aggregation of columns in the report to be graphed using a variety of graph types including column, line, horizontal bar, pie, scatter, and area.

The graph settings can be configured to your preference, if you want you can even use the Custom settings section to further tailor the graph. If, for example, you'd like to change the colour of the graph then this can be done by using hexadecimal colours, e.g colours = #ff0000 would change the graph colour to red. If you have a graph with multiple colours, such as a pie chart or bar graph, you will use the same format but list the colours so they are seperated by a comma e.g. colours = #ff0000,#00ff00,#0000ff

A list of all available settings can be found at http://www.goat1000.com/svggraph-pie.php

Remember, the format of the Custom setting is: [ setting name ] = [ parameter ].

Filters

The Filters tab allows you to select the search filters which will appear at the top and side of the report.

There are three types of filters available:

  • Standard filters: Standard filters appear at the top of the page above the columns of data in the report. The user can enter/select criteria and click the Search button. Additional filters can be added to the page and hidden behind an Advanced button to allow users access to less commonly used filters.
  • Sidebar filters: Sidebar filters appear in a column on the side of the page. The user can put a tick in the check box next to the items they wish to filter by. A number displayed next to each filter option shows how many courses meet the criteria. As soon as the user clicks, the page automatically refreshes to display the appropriate course(s).
  • Toolbar search: The toolbar search allows the user to search multiple fields for text, including the Course Name, Summary, Shortname, ID Number, Course Category, and Tags. Site administrators define which of these fields will be included in the search.

Standard filter options

Standard filter options allow you to customise how the filters appear above your report. The available filters are determined by the source of the report. Each report source has a set of default filters.

A filter can appear in either the standard filter area or the sidebar filter area, but not both. Filters can be added, sorted, and removed.


Search Field: Use the Search Field options to select the fields that users will be allowed to search by. Additional filters can be added by clicking the Add another filter dropdown.

Customise Field Name: Next to each filter, tick the check box under Customise Field Name and enter a new label that will be displayed next to the filter when the report is run.

Advanced?: Check the Advanced? box next to any search options that should be available if the user clicks the Show advanced button on the report. This option is helpful to make infrequently used search options available, but hidden by default.

Options: To delete a filter from the report, click the Delete icon under the Options column. To rearrange the order of the filters on the page, click the up and down arrows. The filters will appear in the Search by box in the order they are listed.


Sidebar Filter Options

Sidebar filter options allow you to customise the filters on the side of your report. Sidebar filters have instant filtering enabled, each change made to a filter will automatically refresh the report data.

A filter can appear in either the standard filter area or the sidebar filter area, but not both. Filters can be added, sorted and removed.


Search Field: Use the Search Field options to select the fields that users will be allowed to search by. Additional filters can be added by clicking the Add another filter dropdown.

Customise Field Name: Next to each filter, tick the check box under Customise Field Name and enter a new label that will be displayed next to the filter when the report is run.

Advanced?: Check the Advanced? box next to any search options that should be available if the user clicks the Show advanced button on the report. This option is helpful to make infrequently used search options available, but hidden by default.

Options: To delete a filter from the report, click the Delete icon under the Options column. To rearrange the order of the filters on the page, click the up and down arrows. The filters will appear in the Filter by box in the order they are listed.

Toolbar search box: The toolbar search box allows users to search multiple fields by text, such as the course name and summary, instead of filtering individual fields.

Disable toolbar search box: Checking this box will prevent the search box from appearing at the top of the page.

Search Field: Use the Search Field options to select the fields that users will be allowed to search by. Additional filters can be added by clicking the Add another search column dropdown.

Options: To delete a filter from the report, click the Delete icon under the Options column.

Content

Content controls allow you to restrict report content based on the user who is running the report. This allows site administrators to pre-filter the report so that one report can be run by many users on the system by only showing the data most pertinent to the user. For example, a report can be designed for managers to run that will only display data for their direct reports.

Report content allows you to select the overall content control settings for this report:

  • Show all records: Every available record for this source will be shown and no restrictions will be placed on the content available.

  • Show records matching any of the checked criteria below: Display records that match any of the criteria set below.

  • Show records matching all of the checked criteria below: Display records that match all of the criteria set below.

It is then possible to configure the criteria for the Report content settings above. The criteria available will differ based on the report sources, and can include restrictions based on users, positions or dates.

Show by user: Show records based user: When selected, the report shows different records depending on the user viewing the report and their relationship to other users.

Include records from a particular user: Select from the following option:

  • A user's own records: Allow the user to see their own records only.

  • Records for user's direct reports: Allow the user to see the records belonging to any user who reports to them. These will be the same users who appear on the user's My Team page.

  • Records for users indirect reports: Allow the user to see the records belonging to any user who reports to them also include users who report to the people they manage. 

  • Records for users temporary reports:  Allow users to see users that they are currently temporary manager of.

  • Both: The user can view both their own records and those of their direct reports.

Show by the user's current position: Show records from staff in the user's position: When selected, the report displays results dependent on the user's assigned position.

Include records from child positions: Select from Staff at or below the user's positionJust staff in the user's position, or Just staff below the user's position.

Show by the user's current organisation: When selected, the report displays results dependent on the organisation the user has been assigned to.

Include records from child organisations: Select from Staff at or below the user's organisation, Just staff in the user's organisation, or Just staff below the user's organisation.

Show by the organisation when completed: When selected, the report displays results dependent on the organisation the user was assigned to when they completed the course or competency.

Show records based on the record date: When selected, the report only displays records within the time frame selected.

Include records from: Select from the following:

  • The past: The report only shows records with a date older than the current date.
  • The future: The report only shows records with a future date set from the current date.
  • The last 30 days: The report only shows records between the current time and 30 days before.
  • The next 30 days: The report only shows records between the current time and 30 days into the future.
  • The current financial year.
  • The previous financial year.

Check the Include record if date missing option to include records with no date in your report.

Access

The Access page allows site administrators to grant permission to access the report to other users based on their role.

Access Controls

Restrict access: This option allows you to select whether All users can view this report or if Only certain users can view this report based on the controls selected. The report author may restrict the visibility of a report to specific system roles. Only when the user possess the correct system roles (eg "learner") at the time the report is to be viewed will the Reports tab appear in the Totara menu.


Restrict access by role

Context: Context is the location or level within the system that the user has access to. For example a site administrator would have system level access (context), while a learner may only have course level access (context). Context allows you to set the context in which a user has been assigned a role to view the report.

Note that the Staff Manager role is typically assigned at the user context rather than the system level. If you need Staff Managers to view the report then you should use the Users may have the role in any context setting.

Roles with permission to view this report: Place a check next to any role(s) that should be granted permission to view the report. Available roles are:

  • Manager
  • Course creator
  • Editing Trainer
  • Trainer
  • Learner
  • Guest
  • Authenticated user
  • Authenticated user on frontpage
  • Staff Manager

Performance

The Performance page provides access to performance-based settings for each report.

Filters Performance Settings

Restrict initial display: Controls how the report is initially displayed and is recommended for larger reports where you will be filtering the results. By default a report automatically runs as soon as the report page is loaded. This can take some time if the report has large amounts of unfiltered data being returned. When this setting is enabled, it increases the speed of the report by requiring the user to apply search filters and click the Search button before displaying report data.

Caching Performance Settings

To set caching performance options, report caching must be enabled first on the Advanced features page. See Advanced Features for more information.


Enable Report Caching: When checked, a copy of this report will be generated on a set schedule and users will see the results of the stored or cached version report. This will make displaying and filtering of the report faster because it does not have to generate results every time it is accessed. Note that the data displayed will be from the last time the report was generated rather than 'live' data. We recommend enabling this setting only if necessary (reports are taking too long to be displayed), and only for specific reports where this is a problem.

Cache Schedule: Determines the schedule used to control how often a new version of the report is generated. The report will be generated by the background server job a.k.a. cron job that immediately follows the specified time. For example, if you have set up your cron to run every 20 minutes at 10, 30 and 50 minutes past the hour and you schedule a report to run at midnight, it will actually run at 10 minutes past midnight.

Current Server Time: All reports are cached based on server time. Cache status shows you current local time which might be different from the server time. Make sure to take into account your server time when scheduling cache.

Generate Now: If checked, then report cache will be generated immediately after changes are saved.

Note that both the Restrict initial display and Enable report caching features are setup on a report-by-report basis and that a report need not have both or either of these options enabled.

Use database clone: It is now possible to configure a second database connection for use by Report Builder. The purpose of this secondary connection is so that you can direct the main Report Builder queries at a read-only database clone, this can be done to improve performance. These settings should be considered highly advanced. Support cannot be provided on configuring a read only slave, you will need in house expertise to achieve this.

Those wishing to use the second database connection can find instructions for it within config-dist.php.You can use a read only database clone. These settings allow admin to configure a second database:

// connection that will be used for selected Report Builder reports to improve performance
// and lower the main database load.
//
// $CFG->clone_dbname = 'xxx';
// $CFG->clone_dbhost = $CFG->dbhost; // optional
// $CFG->clone_dbuser = $CFG->dbuser; // optional
// $CFG->clone_dbpass = $CFG->dbpass; // optional
// $CFG->clone_dboptions = $CFG->dboptions; //optional

The Totara Academy has a whole course dedicated to using Reports in Totara Learn. Here you can learn more on how to use reports, see best practice, and give it a go yourself.