reportbuilder/API

Jump to: navigation, search

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

The 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.

Sources

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).

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).
  • Parameter options: Defines what params can be passed to the report to filter the result (paramoptions).
  • Content options: Allows the user who is setting up the report to filter on certain fields based on values. (contentoptions).

Construction

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->sourcetitle = get_string('sourcetitle', 'mod_choice');
$this->add_user_objects();
 
parent::__construct();
}

When constructing a source, you can set the 'base' variable to a table name with no prefix in braces, e.g. "{choice_answers", or you can use an SQL statement.

$this->base = '(SELECT * FROM {choice_answers} WHERE choiceid <> 2)';

This is a very simple example that sets the base to all entries in choice_answer besides the entries where choiceid equals 2. Next set the source title. The 'sourcetitle' is retrieved from the relevant module's language file (in this case, mod_choice). The constructor automatically calls the functions to define the structure of the source. It is possible to define all the information by overriding __construct and filling the base, joinlist, columnoptions, filteroptions and defaultcolumns directly, but this quickly becomes large and unwieldy. A number of helper methods can be used. This will assist the inclusion of common fields and filters in the source. The available methods are:

  • add_user_objects()
  • add_course_objects()
  • add_course_category_objects()

The add_user_objects is used in this example

We will go through the methods to fill out the rest of the sources properties below.

Join list

/**
* Other tables that are related to this source.
*
* @return array An array of \core\reporting\join objects.
*/

protected function define_joinlist() {
$joinlist = array(
new \core\reporting\join(
'choiceoptions',
'LEFT',
'{choice_options}',
'choiceoptions.id = base.optionid'
),
new \core\reporting\join(
'choice',
'LEFT',
'{choice}',
'choice.id = base.choiceid'
)
);
 
return $joinlist;
}

define_joinlist is used to define all of the possible other tables that will link with the base one. The \core\reporting\join object in this example has four properties.

  1. Table alias
  2. Join type
  3. The table name
  4. SQL join code (i.e. what would ordinarily come after JOIN ON)

Column options

/**
* Columns that can be selected in this report.
*
* @return array An array of \core\reporting\columnoptions.
*/

protected function define_columnoptions() {
$columnoptions = array(
new \core\reporting\column_option(
'choice',
'title',
get_string('name', 'mod_choice'),
'choice.name',
array(
'joins' => 'choice'
)
),
new \core\reporting\column_option(
'choice',
'choiceoption',
get_string('choicename', 'mod_choice'),
'choiceoptions.text',
array('joins' => 'choiceoptions')
),
new \core\reporting\column_option(
'choice',
'timemodified',
get_string('timemodified', 'mod_choice'),
'base.timemodified',
array(
'displayfunc' => 'modified_on_date'
)
)
);
 
return $columnoptions;
}

Column options are possible columns that can be used in the report. The \core\reporting\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 specify a grouping type with 'grouping' => "type".
      1. average
      2. comma_list
      3. comma_list_unique
      4. count
      5. list_dash
      6. max
      7. min
      8. percent
      9. stddev
      10. sum
      11. unique_count
    3. You can also specify a function to format the display of a column. To use a custom display function, create a function with the displayfunc preceded by "display_". So for 'displayfunc' => 'modified_on_date', create the function 'display_modified_on_date' in your source class. Here we have created a very basic function to determine how the timemodified row should be displayed.

As well as creating your own display functions, you can use one of the predefined display functions. The predefined display functions include list:

  • nice_date
  • nice_time
  • nice_time_in_timezone
  • nice_date_in_timezone
  • nice_datetime
  • nice_datetime_seconds
  • yes_or_no
  • ucfirst
  • round2
  • percent
  • link_user
  • userfield_textarea
  • link_user_icon
  • user_picture
  • link_course
  • link_course_icon
  • course_icon
  • course_type_icon
  • link_course_category
  • yes_no
  • hours_minutes
  • country_code
  • deleted_status
  • language_code
  • user_email
  • user_email_unobscured
  • grade_string

Filter options

/**
* Filters that can be added to this report.
*
* @return array An array of \core\reporting\filter_option objects.
*/

protected function define_filteroptions() {
$filteroptions = array(
new \core\reporting\filter_option(
'choice',
'choiceoption',
get_string('choicename', 'mod_choice'),
'select',
array('selectfunc' => 'choice_option_list')
)
);
 
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 filteroptions array elements are:

  1. The column type.
  2. The value of the column. This should be the same as the column_option's value.
  3. The string for the name of the column.
  4. The type of filter this filter option will be. The default filter types are:
    1. cohort
    2. date
    3. enrol
    4. multicheck
    5. number
    6. select
    7. text
    8. textarea
  5. other options
    1. A select function can be specified here to return the information required for the filter. To use a custom select function, create a function with the selectfunc preceded by "filter_" Here we have created a very basic function to return the choice options.

Default columns

/**
* 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 by the user if not required. The two elements of the array match with the first two elements of one of the column_options (column type and value)

Default filters

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

protected function define_defaultfilters() {
$defaultfilters = array(
array(
'type' => 'choice',
'value' => 'choiceoption',
),
);
 
return $defaultfilters;
}

It is also possible to define default filters that will be included immediately after the source has been specified. Again, the user can edit or remove these filters later. The two elements of the array match with the first two elements of one of the filter_options (column type and value)

Required columns

/**
* Defines the columns that a report must have if it is created from this source
*
* @return array An array of required columns.
*/

protected function define_requiredcolumns() {
$requiredcolumns = array(
array(
'type' => 'choice',
'value' => 'title'
)
);
 
return $defaultcolumns;
}

This will define what columns must be in all reports created from this source. The user cannot remove these columns. The two elements of the array match with the first two elements of one of the column_options (column type and value).

Params options

/**
* Defines the options that can be given as parameter to the report page
*
* @return array An array of the param options.
*/

protected function define_paramoptions() {
$paramoptions = array(
new \core\reporting\param_option(
'userid',
'user.id'
)
);
 
return $paramoptions;
}

This will define what columns can be filtered on, using parameters on the report page. In this example you would go to report.php?id=X&userid=Y to report on only a specific user. The first element is the name of the parameter that will be used, and the second is the database field that it will be used to filter on.

Content options

/**
* Defines the options that can be given as parameter to the report page
*
* @return array An array of the param options.
*/

protected function define_contentoptions() {
$contentoptions = array(
new \core\reporting\content_option(
'date',
get_string('timemodified', 'mod_choice'),
'base.timemodified'
)
);
 
return $contentoptions;
}

Content options allow the user, who is setting up the report, to filter on certain fields based on values. This is different to the filteroptions as the filteroptions are used by the person viewing the report. The first parameter should be the type of content restriction, the second is the display name of the content option, and the third is the database field to restrict based off of. The available types of content restriction are:

  • user
  • date
  • tag

Each of these is defined in a class <TYPE>_content (user_content, date_content, tag_content)

Custom functions

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

function 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. It retrieves all the possible options in the choice and returns the values.

/**
* Custom function to display the date something was modified.
* @param integer $date Unix timestamp
* @param object $row Object containing all other fields for this row
*
* @return string The date preceded by "Modified on".
*/

function display_modified_on_date($date, $row) {
if ($date && is_numeric($date)) {
return "Modified on " userdate($date, get_string('strfdateshortmonth', 'langconfig'));
} else {
return '';
}
}

Here is the custom function that we used to display the timemodified. Please note that you would not want to include the text "Modified on" as demonstrated here. Instead you would want to use a language string. It is written as a literal here to simplify the example.

Embedded Reports

Embedded reports allow you to embed reports directly on php pages. They also appear in a list on the Manage Reports page.

The php page in which the report will be embedded will vary, but should almost always be in the relevant plugin's folder or an appropriate sub-folder. The embedded report must always exist in the relevant plugin's folder.

Like source classes, embedded report classes should exist in the relevant module's namespace, under "reporting" (e.g. \mod_assign\reporting\submission_embedded). By convention it should have the "_embedded" suffix. 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_embedded.php). The class must extend \core\reporting\base_embedded.

The embedded report class defines a number of defaults for the embedded report that will be applied upon installation. However, the report can be changed later through the Manage Reports interface.

An embedded report consists of:

  • URL: The address of the php page on which the report will be embedded.
  • Source The source upon which this embedded report is based.
  • Short name A short name identifying the source.
  • Full name A longer name identifying the source.
  • Columns The columns that will be included in the embedded report.
  • Filters The filters that will be available in the embedded report.
  • Content mode.

Construction

defined('MOODLE_INTERNAL') || die();
 
class \core\reporting\choice_embedded extends \core\reporting\base_embedded {
 
public function __construct() {
$this->url = '/mod/choice/report.php';
$this->source = 'choice';
$this->shortname = 'appraisal_detail';
$this->fullname = get_string('sourcetitle', 'rb_source_appraisal_detail');
 
parent::__construct();
}

Here an embedded report is created for the choice source. The url is the address of the php page that will be embedding the report. The source property should be set to the non-namespaced name of the source class. E.g. '\core\reporting\choice' is just 'choice'. The shortname and fullname are identifiers for the report.

Content mode determines whether how the rules behave.

  • REPORT_BUILDER_CONTENT_MODE_NONE: No criteria are shown, all rows are always displayed.
  • REPORT_BUILDER_CONTENT_MODE_ANY: If any of the criteria match, the row is displayed.
  • REPORT_BUILDER_CONTENT_MODE_ALL: Only rows that match all of the criteria will be displayed.

Columns

protected function define_columns() {
$columns = array(
array(
'type' => 'choice',
'value' => 'title',
'heading' => get_string('name', 'mod_choice')
),
array(
'type' => 'choice',
'value' => 'choiceoption',
'heading' => get_string('choicename', 'mod_choice')
)
);
 
return $columns;
}

To add a column into the embedded report, override the define_columns function and return an array of arrays. Each array should have type and value which are the same as the first two elements of the equivalent column in the columnoptions array in the source. A heading for each column must also be defined.

Filters

protected function define_filters() {
$filters = array(
array(
'type' => 'choice',
'value' => 'choiceoption',
'advanced' => 0
)
);
 
return $filters;
}

To include filters in the embedded report, simply override the define_filters function and return an array of arrays. Each array should have a type and a value of the first two elements of the equivalent filter in the filteroptions array that was defined in the source. Advanced simply determines whether the filter should appear hidden under the advanced heading

Embedded params

protected function define_embeddedparams() {
$embeddedparams = array(
array(
userid => 1,
)
);
 
return $embeddedparams;
}

Embedded params override the params in the source. They can be used to apply further specificity in the embedded report. It is simply the param followed by its valued. In this example, only rows pertaining to a user with an id of 1 will be displayed.

Content settings

protected function define_contentsettings() {
$contentsettings = array(
'user' => array(
'enable' => 1,
'who' => 'own'
)
);
 
return $embeddedparams;
}
}

Content settings override the content options in the source. They can be used to apply further specificity in the embedded report. In this example the \core\reporting\user_content class is used. Only the current users own records will be displayed.

Embedding a report

To embed the report you need to create a new php page that will display it.

require('../../config.php');
 
$context = context_system::instance();
require_capability('mod/choice:readresponses', $context);
 
$sid = optional_param('sid', '0', PARAM_INT);
$format = optional_param('format', '', PARAM_TEXT); // export format
 
$PAGE->set_context($context);

First you will need to include the necessary libraries to embed a report. Most notably reporting/lib.php and admin/tool/reportbuilder/lib.php. At this point you should also require any capabilities that the user should have in order to be able to view the report. A number of optional params must be included, the 'sid' for handling saved searches and the 'format' for exporting.

$report = \core\tool\reporting\get_embedded_report('choice', null, false, $sid);

Here the actual report is created. This should be the same name as your embedded report class \core\reporting\choice_embedded

// Handle a request for export
if($format!='') {
$report->export_data($format);
die;
}

This is where the exporting is handled. The format optional param will be set when a user attempts to export a report. Then this code will export the data instead of displaying the report.

echo $OUTPUT->header();
echo $OUTPUT->heading(get_string('name', 'mod_choice'));
echo $OUTPUT->container(get_string('choiceoptions_help', 'mod_choice'));

Will display the site header and headings and the content container. Required on any Moodle page.

$report->display_search();
echo $report->display_saved_search_options();

This enables the search functionality and the saved searches functionality. This will use the 'sid' param.

$report->display_table();
$output = $PAGE->get_renderer('tool_reportbuilder');

This will display the report using the report builder renderer.

$output->export_select($report->_id, $sid);

This code will display the controls that allow a user to export the report.

echo $OUTPUT->footer();

Will display the site footer. Required on any Moodle page.