reportbuilder/API

Back to Index

Note: This page is a work-in-progress. Feedback and suggested improvements are welcome. Please join the discussion on moodle.org or use the page comments.

Overview

Report Builder provides an interface to allow administrators to generate reports, customise their appearance, then make them available to groups of users. It is designed to be easily extensible, so developers can add to existing report sources and write their own to allow administrators to generate the kind of reports they are interested in.

The Report Builder API enables plugin contributors and Moodle core developers to create new sources that can be used by site administrators and teachers to generate and customise reports.

Elements

Core

The majority of the Report Builder's code lies in core\reporting, which is located in the 'reporting' folder in moodle. This houses the basic logic for reporting functionality and the base source class, core\reporting\base.

Tool

The viewing and creating of reports is handled in a tool, "tool_reportbuilder" which is located in admin/tool/reportbuilder.

Source

All reports are based on a single source, which defines the relationships between tables in the databases (amongst other things detailed below).

A source class should exist in the relevant module's namespace (which is the module's Frankenstyle component name), under "reporting" (e.g. mod_assign\reporting\submission). This means that the file should be located in the module's classes folder, in the reporting subfolder (e.g. mod/assign/classes/reporting/submission.php).

The class must extend report_builder_source_base.

A source consists of:

• A base table: Defines what table all reports from this source will be based off (base)
• A join list: Defines what tables should be joined to the base table (joinlist)
• Column options: Defines which columns from each table should be available in the report (columnoptions)
• Filter options: Defines what fields can be used to filter the results in the report (filteroptions)
• Default columns: Defines what fields will be selected when a report is first created from this source (defaultcolumns)
• Default filters: Defines what filters will be available when a report is first created from this source (defaultfilters)

Helper Functions

A number of helper functions exist in core\reporting\base that can be used to aid the creation of a source.

• User
  • add_user_table_to_joinlist
  • add_user_fields_to_columns
  • add_course_fields_to_filters

Course

• • add_course_table_to_joinlist
  • add_course_fields_to_columns
  • add_course_fields_to_filters

Course category

• • add_course_category_table_to_joinlist
  • add_course_category_fields_to_columns
  • add_course_category_fields_to_filters

As an example, 'add_user_table_to_joinlist' can be used to join the user table, 'add_user_fields_to_columns' can be used to add the columns, and 'add_user_fields_to_filters' can be used to add the filters. Usage: $this->add_user_table_to_joinlist($joinlist_array, 'base', 'userid'); // Where 'base' is the name of the already joined table that has the users' ids, and 'userid' is the name of its column. $this->add_user_fields_to_columns($columnoptions_array); $this->add_user_fields_to_filters($filteroptions_array)

$this->joinlist = $joinlist_array; $this->columnoptions = $columnoptions_array; $this->filteroptions = $filteroptions_array;

Example

Below is an example of a source for the activity module "Choice".

defined('MOODLE_INTERNAL') || die();

class mod_choice\reporting\choice extends core\reporting\base {

   /**
    * Constructor for defining a report builder source object.
    */
   function __construct() {
       $this->base           = '{choice_answers}';
       $this->joinlist       = $this->define_joinlist();
       $this->columnoptions  = $this->define_columnoptions();
       $this->filteroptions  = $this->define_filteroptions();
       $this->defaultcolumns = $this->define_defaultcolumns();
       $this->sourcetitle    = get_string('choice', 'mod_choice');

       parent::__construct();
   }

The constructor defines the basic structure of the source. It is possible to define all the information in this method, but this quickly become large and unwieldy. The main table (base) for this report source is choice_answers.

/**

* Other tables that are related to this source.
*
* @return array An array of rb_join objects.
*/

protected function define_joinlist() {

   $joinlist = array(
       new rb_join(
           'choiceoptions',
            'LEFT',
           '{choice_options}',
           'choiceoptions.id = base.optionid'
       ),
       new rb_join(
           'choice',
           'LEFT',
           '{choice}',
           'choice.id = base.choiceid'
       )
   );

   $this->add_user_table_to_joinlist($joinlist, 'base', 'userid');

   return $joinlist;

} In this section of the code we have defined all of the possible other tables that will link with the base one. The rb_join object in this example has four properties.

1. This is the table alias.
2. What type of join is being used.
3. The table name.
4. sql join code.

/**

* Columns that can be selected in this report.
*
* @return array An array of rb_columnoptions.
*/

protected function define_columnoptions() {

   $columnoptions = array(
       new rb_column_option(
           'choice',
           'title',
           get_string('name', 'mod_choice'),
           'choice.name',
           array('joins' => 'choice')
       ),
       new rb_column_option(
           'choice',
           'choiceoption',
           get_string('choicename', 'mod_choice'),
           'choiceoptions.text',
           array('joins' => 'choiceoptions')
       ),
       new rb_column_option(
           'choice',
           'timemodified',
           get_string('timemodified', 'mod_choice'),
           'base.timemodified',
           array(
               'displayfunc' => 'nice_datetime'
           )
       )
   );
   $this->add_user_fields_to_columns($columnoptions);

   return $columnoptions;

} Column options are possible columns that can be used in the report. The rb_column_option array elements in the example are:

1. The column type of the columns. Here I have defined three columns that will be under the 'choice' heading.
2. The value of the column.
3. The string for the column name.
4. The field in the database. Here we have the alias and the field name.
5. Further options
   1. If the field is not part of the 'base' table then the join should be included here.
   2. You can also specify a function to format the display of a column. In this example I am used one of the predefined display functions (nice_datetime).

There are also some helper functions which allow the inclusion of standard columns. User columns are very popular so I have included those as well. /**

* Filters that can be added to this report.
*
* @return array An array of rb_filter_option objects.
*/

protected function define_filteroptions() {

   $filteroptions = array(
       new rb_filter_option(
           'choice',
           'choiceoption',
           get_string('choicename', 'mod_choice'),
           'select',
           array('selectfunc' => 'choice_option_list')

       )
   );

   $this->add_user_fields_to_filters($filteroptions);
   return $filteroptions;

} It is possible to add filters to your report so that large reports can be condensed into a smaller and perhaps more meaningful display. The rb_filter_option array elements are:

1. The column type.
2. The value of the column. This should be the same as the rb_column_option value.
3. The string for the name of the column.
4. The type of filter this filter option will be. The current filter types are:
   1. cohort
   2. date
   3. enrol
   4. multicheck
   5. number
   6. select
   7. text
   8. textarea
5. other options
   1. a function can be specified here to return the information required for the filter. Here we have created a very basic function to return the choice options.

Once again there are helper functions for adding typical filter options. /**

* Defines the default columns when creating a report.
*
* @return array An array of default columns.
*/

protected function define_defaultcolumns() {

   $defaultcolumns = array(
       array(
           'type' => 'user',
           'value' => 'namelink'
       ),
       array(
           'type' => 'choice',
           'value' => 'title'
       )
   );

   return $defaultcolumns;

} It is possible to define default options that will included immediately after the source has been specified. These values can be edited and removed if not required. The two elements of the array match with the first two elements of the rb_column_options array. /**

* Custom function to return an array of choice options.
*
* @return array Choice options.
*/

function rb_filter_choice_option_list() {

   global $DB;

   $choiceoptions = array();

   $records = $DB->get_records_sql("SELECT text FROM {choice_options}");
   foreach ($records as $value) {
       $choiceoptions[$value->text] = $value->text;
   }

   return $choiceoptions;

} Here is the custom function that we used to return the choice options in the filter.