--- UNDER CONSTRUCTION ---
Report builder has an API intended to make it easy to extend the functionality in a number of ways. The primary feature is the ability to write report sources, that allow the user to generate reports based on specific data from within moodle.
It is also possible to create new content and access restrictions, filter types, and display functions.

Beginner Topics

Overview
Hello World Source
Adding a Column
A Basic Join
Adding a Filter
Setting Default Columns and Filters

Intermediate Topics

Column Display Functions
Combining Multiple Fields
Advanced Column Options
Advanced Filter Options TODO
Predefined Common Options TODO
General Report Options TODO
Storing files: Report sources, embedded report definitions and language strings TODO
Dynamic joins, column options and filter options TODO

Advanced Topics

Content Restrictions TODO
Access Restrictions TODO
Parameter Restrictions TODO
Advanced Joins TODO
Required Columns TODO
Grouping and Aggregation TODO
Preprocessors TODO
Creating new filter options TODO

Embedded Reports

Overview TODO
Access restrictions in embedded reports TODO
Embedded reports in My Reports list TODO
Resetting to defaults TODO
Embedded parameters TODO

Troubleshooting

Common Exceptions (automatic check errors) TODO
Other errors TODO
Debugging tools TODO
Choosing a base table / Performance TODO

Overview
========

Report builder is designed to be extensible, so developers can modify existing report sources and write their own to allow administrators to generate the kind of reports they are interested in.


Hello World Source
==================

The code below is one of the simplest report sources you could make:
<?php

class rb_source_example1 extends rb_base_source {
    public $base, $joinlist, $columnoptions, $filteroptions;

    function __construct() {
        global $CFG;
        $this->base = $CFG->prefix . 'course';
        $this->joinlist = array();
        $this->columnoptions = array(
            new rb_column_option(
                'course',
                'fullname',
                'Course Fullname',
                'base.fullname'
            )
        );
        $this->filteroptions = array();
        $this->sourcetitle   = "Example1";

        parent::__construct();
    }
}
?>
To create this source save the code above in a file called rb_source_example1.php in the /local/reportbuilder/rb_sources/ directory. The class name must start with 'rb_source_', and the class name and filename must match. You should now be able to generate a report based on this source as follows:
As an administrator, click on Site Administration > Reports > Report Builder > Manage Reports
Scroll down to New Report
Give your report a name (e.g. 'Example 1')
Choose the 'Example1' source from the pulldown menu
Click 'Save Changes'
You will be taken to the settings page for the new report, which is where an administrator can configure the report to appear the way they want. While the source describes what columns *can* be included in the report, it is up to the administrator to decide which columns *are* shown. Since we've only described one column so far, let's just add that one and see what the report looks like:
From the settings page for your report, click on the 'Columns' tab
In the 'Report columns' section, Choose the 'Course Fullname' column from the 'Add another column...' pulldown menu.
Click 'Save Changes'
You should now be able to see the report by clicking 'View this report'. It should look something like this:
[screenshot of report]
This should give a list of all the courses in your site, including the top-level 'front page' course. If you don't have any courses in your site yet you may want to create a few for test purposes.
Here's a breakdown of what this does:
class rb_source_example1 extends rb_base_source {
Defines a report builder source, which extends the base source. This gives the source access to a whole set of useful generic report builder methods.
    public $base, $joinlist, $columnoptions, $filteroptions;
Defines four class properties. These are the minimum properties needed to write a source, there are a number of additional, optional properties.
    function __construct() {
The constructor function is called when a new source is created. It initializes all the properties needed by the source (see below).
        global $CFG;
        $this->base = $CFG->prefix . 'course';
Defines the source's "base" table. This is the name of a report builder table that the source is based on. Your choice will determine the contents of the report. In this case, because we are creating a report on courses, the course table is the most obvious choice for base table. See more on [choosing a good base table]. It is also possible to [use a sub-query as a base table].
       $this->joinlist = array();
The joinlist property contains an array of join objects, each of which defines a connection between your base table and another database table. Without joins, the only database fields that would be available would be those in the base table. Because this is a very basic source there are no joins yet.
        $this->columnoptions = array(
            new rb_column_option(
                'course',
                'fullname',
                'Course Fullname',
                'base.fullname'
            )
        );
The columnoptions property contains an array of column option objects. Each column option object defines a column that can be included in a report that uses this source. It is up to the person building the report to decide which columns they actually want to use. See adding a column for more information about this section.
        $this->filteroptions = array();
The filteroptions property contains an array of filter option objects, which define possible search options for the report. Because this is a very basic source there are no search options yet, although some will be added [later].
        parent::__construct();
At the end of the constructor, we call the constructor of the base source. This contains a number of checks to validate the source and warn the developer if any required fields have been forgotten or invalid joins created.

Adding a column
===============

A simple column option looks like this:
        $this->columnoptions = array(
            new rb_column_option(
                'course',
                'fullname',
                'Course Fullname',
                'base.fullname'
            )
        );
Every column option object must include 4 required arguments:
Column Type. A string which describes the type of the column. Typically this might be the same as the table name, and is used to group similar sorts of columns together
Column Value. A string describing the column more specifically. Together the type and value must be unique within a source, and are used to reference the column elsewhere in the source.
Column name. A string which describes the column. This appears in the pulldown menu when choosing columns, and is also the default column heading in the report.
Column field definition. A snippet of SQL which describes how the database should access the column. Typically this will be of the form 'base.[field name]' to access fields in the base table, or '[join name].[field name]' to access fields from other tables (see below).
Once this column option is added to the source, a new option should appear in the pulldown on the columns tab of the report settings page.
See also advanced column options and [rb_column_option documentation].

A basic join
============

Our hello world source now makes it possible to display any database fields from the course table, but currently there is no way to get data from any other table in the database. For instance, what if we wanted to add a column that displayed the course category along side the course name? The name of the course category is stored in a different table:
mdl_course:
id   |    fullname    |   category
1    |   My Course    |   1

mdl_course_categories:
id   |  name
1    | Miscellaneous
We need to add a join to the joinlist, so report builder knows how to correctly connect the base table to the 2nd table. We do this by adding a join object to the joinlist array:
$joinlist = array(
    new rb_join(
        'course_category',
        'LEFT',
        $CFG->prefix . 'course_categories',
        'course_category.id = base.category',
    )
);
The rb_join method takes 4 required arguments:
Join name. A unique string, used to identify this join.
Join type. Describes the type of join (e.g. LEFT OUTER, RIGHT OUTER, INNER). This effects how the records are combined. See http://en.wikipedia.org/wiki/Join_(SQL)
Table name. The full name of the table to join to
Join condition. This describes which fields the tables should be joined on. Use the Join name to describe other tables, or 'base' for the base table. In this case we are joining the 'category' field of the base (course) table, to the 'id' field of the course_categories table.
See also [advanced join options] and the [rb_join documentation].
Once the join has been defined, column options can make use of fields from the joined tables, by specifying which joined tables they will use. For example, to include a column option which displays the course category:
new rb_column_option(
    'course',
    'category',
    get_string('category'),
    'course_category.name',
    array(
        'joins' => 'course_category'
    )
);
Here the field name uses the syntax '[join name].[field name]' to define the desired field. Any additional optional parameter 'joins' is added which lists which joins must be made to get access to this field. The 'joins' parameter can take a join name or an array of join names if you need to join multiple tables.
If you add the join and column option code to the Hello World source file and save it, you should see a new column option appear when you edit the report. Add the new column to the report and should look like the report below:
[SCREENSHOT OF REPORT]

Adding a filter
===============

Here is a simple filter option to add a text-based search field on the course fullname column we created earlier.
$this->filteroptions = array(
    new rb_filter_option(
        'course',
        'fullname',
        'Course Name',
        'text'
    )
);
Every filter option object must include 4 required arguments:
Column Type. Must match the type of an existing column option.
Column Value. Must match the value of an existing column option. The filter will act on the column option matched by the type and value.
Filter name. A string which describes the filter. This appears in the pulldown menu when choosing filters, and also next to the filter in the report.
Filter type. Defines the behaviour of the filter (e.g. text field, pulldown, checkbox etc.)
The filter types are modular, allowing new filter types to be written. The available filter types are found in local/reportbuilder/filters/. Some common filter types include:
text: Match text entered into a text field
select: Choose from a list of predefined options
date: Match against a range of dates
Some filter options require additional information to be passed into the filter options, see the advanced filter options and [rb_filter_option documentation] for more information.
For details on how filter types are created, see [creating new filter options].

Setting default columns and filters
===================================

Once a report has a large number of column and filter options it can be useful to define a set of default options. These columns and filters will be automatically added to any new report that is based on this source. This gives the user a headstart by generating a 'typical' report which they can further customise. To add default column options, add a new property to the class called defaultcolumns:
$this->defaultcolumns = array(
    array(
        'type' => 'course',
        'value' => 'fullname',
    )
 );
The 'type' and 'value' must match an existing column option.
To add default filters use a similar syntax:
$this->defaultfilters = array(
    array(
        'type' => 'course',
        'value' => 'fullname',
        'advanced' => 0
    )
);
The 'type' and 'value' elements match the column the filter is applied against. The 'advanced' option is described in the advanced filter options section.

Column Display Functions
========================

In some cases you may want the data for a particular column to be transformed in some way before it is output to the page.
For example, the hidden column in the course table contains either a 0 (for visible) or 1 (for hidden). If you wanted to create a column that displays the visibility status to a user, it would be better for the column to contain the words 'Visible' and 'Hidden' rather than 0 and 1.
Display functions provide a way to achieve that, by preprocessing the column data prior to display.
Here's the column option required to display the course hidden column:
$this->columnoptions[] = new rb_column_option(
    'course',
    'hidden',
    get_string('coursevisibility'),
    'base.hidden',
    array('displayfunc' => 'course_visibility')
);
The array is an optional argument which lets you provide additional information about the column option. In this case we've passed a display function name, which will be used to modify the appearance of the column. Each column option can have its own display function, or you can reused the same function for similar columns.
You can name the display function anything you like, but when the column is displayed report builder will look for a function with the name 'rb_display_' followed by the name you choose. So in this case it will look for a function called 'rb_display_course_visibility'. If found, the report will be processed as follows:
Instead of displaying a particular cell, the value from the database will be passed to the display function for that column (as the first argument)
All the data for the current row will be passed as a second argument as an object
Instead of displaying the value, the return value of the function will be displayed instead
If the display function can't be found, the original data will be displayed unchanged
You must define the display function as a method of the source: outside the constructor function but inside the source class definition. In this example the display function will look like this:
function rb_display_course_visibility($item, $row) {
    if($item === null) {
        // no value found, return an empty string
        return '';
    } else if($item == 0) {
        // the course is visible
        return get_string('visible');
    } else {
        // the course is hidden
        return get_string('hidden');
    }
}
It's important to note that although the appearance of the column has changed, any filters which are based on this column will still be comparing against the database value, not the display value. So a text filter for the word 'visible' would fail, because the database is still returning '0'.
If you want to create a display function that makes use of data from multiple columns see combining multiple fields.
There are a number of pre-defined display functions available to source creators. See predefined common options.

Combining Multiple Fields
=========================

It is common to want to combine the data from several columns together when displaying a report. For example, let's say we wanted to make our course name column contain the name of the course, but linked to the course page. Then we need two database fields (the fullname column, but also the course id to let us build the link).
The best way to achieve this is to use another optional setting in the column option definition:
$this->columnoptions[] = new rb_column_option(
    'course',
    'fullnamelink',
    get_string('coursename'),
    'base.fullname',
    array(
        'displayfunc' => 'link_course_name',
        'extrafields' => array('courseid' =>'base.id')
    )
);
The new optional setting 'extrafields' stores an associative array which can contain any number of key-value pairs.
The key is an arbitrary name, used to reference each extra field. It can be anything you like, although you should avoid duplications within a source and names that match the format '[columntype]_[columnvalue]' to avoid conflicts with existing columns. The name you choose will be used in the display function to access the extra field.
The value in the associative array is an SQL snippet similar to the 4th argument to column options. Typically this will be of the form [tablename].[fieldname] although more complex SQL syntax is permitted.
Although this lets you access multiple database fields, the primary field is still the one provided in the 4th argument to the column option. The primary column is the only one that will be used for filtering and sorting.
Here's what the display function to display linked course names might look like:
function rb_display_link_course_name($coursename, $row) {
    if($coursename === null) {
        // if no coursename found, return an empty string
        return '';
    }
    // access the extrafield 'courseid'.
    $courseid = $row->courseid;
    if($courseid) {
        // return the linked course name
        return '<a href=' . $CFG->wwwroot .
            '/course/view.php?id=' . $courseid . '">' . $coursename . '</a>';
    } else {
        // no course ID set, so return an unlinked course name
        return $coursename;
    }
}
The $row object will contain a property that matches the 'key' used in the column option definition (in this case 'courseid').
Note that if any additional table joins are required to access the extrafields, they should be included in the joins for the column option. Otherwise you cannot be sure that all the tables you need will be included in the query.
You might be tempted to create a report that defines two separate column options - course fullname and course id. If both columns were in a report, it would be possible to reference the course id column option from within the display function (through the $row object). But remember though that an administrator has full control over the report - if they decided to delete the ID column, the link would be broken. That's why it's best to use extrafields to ensure that the fields referenced by the display function are always available.

Advanced Column Options
=======================

Overview
--------

While the adding a column page describes the minimum information you must provide to generate a column option, there are a number of additional options you can set to change the columns behaviour. They are described below. Optional parameters are provided via an associative array passed as the 5th argument to rb_column_option().

Prevent column sorting
----------------------

By default columns can be sorted (alphabetically or numerically), by clicking on the column heading (once to sort ascending, click again to switch direction). If it doesn't make sense to sort the column (for instance a column that contains a picture), you can set the nosort flag as follows:
new rb_column_option(
    [type],
    [value],
    [name],
    [field],
    array(
        'nosort' => true
    )
);

Exclude column from export files
--------------------------------

Some columns may be useful in a report when displayed as a webpage, but are not needed when the report is exported. For example, your report may include an options column that lets a user perform actions on each row. To prevent it from appearing in the export file, set the noexport flag:
new rb_column_option(
    // first 4 arguments here
    array(
        'noexport' => true
    )
);

Adding style to columns
-----------------------

Each column in a report is assigned a class according to its type and value, in the format '[type]_[value]'. So each cell of the course fullname column will have 'class="course_fullname"' added. This makes it possible to use CSS to directly style a particular column. If you want to apply style to the column directly another option is to set the 'style' parameter:
rb_column_option(
    // first 4 arguments here
    array(
        'style' => array(
             'color' => 'red',
             'font-weight' => 'bold'
        )
    )
);
The above statement would add 'style="color:red; font-weight: bold;"' to each cell in the column.

Hidden columns
--------------

Report builder reports include a show/hide column functionality which allows the user viewing the report to remove columns from the report to only show the columns that interest them. This is separate functionality from the 'columns' table in a report settings, the user can only choose to show/hide the columns defined by the administrator.
By default, all columns chosen by the administrator are shown, and the user can choose to hide one or more columns with the show/hide button. However, if you set the hidden parameter on a column option, the column will be hidden from users by default, but still available to the user if they show it via the show/hide button.
rb_column_option(
    // first 4 arguments here
    array(
        'hidden' => 1
    )
);
Note this behaviour is different from the normal moodle 'hide' feature, e.g. hide courses, which prevents users from seeing the course at all. In report builder the hidden column is available to the report's user, just not by default.
Hidden columns are still loaded each time the report is displayed, so in most cases it makes sense to just carefully select the desired columns or create two or more reports, rather than including a large number of hidden columns.

Show columns by capability
--------------------------

The 'capability' parameter allows you to selectively show a particular column based on a capability in the system context. This is most commonly used to show a particular column when the report is viewed by an administrator but hide it from other users:
rb_column_option(
    // first 4 arguments here
    array(
        'capability' => 'moodle/site:doanything',
    )
);
As defined above, this column would only be visible to users with the 'moodle/site:doanything' capability set in the system context.

Default headings
----------------

By default, when a new column is added to a report, the column heading will be the same as the name of that column option. In some cases that might not be the most sensible heading name, so you can set 'defaultheading' to override what is shown. For example:
rb_column_option(
    'course',
    'namelink',
    'Course name (linked to course page)',
    'base.fullname',
    array(
        'displayfunc' => 'link_course',
        'extrafields' => array('courseid' => 'base.id'),
        'defaultheading' => 'Course name'
    )
);
In this case, had we not included 'defaultheading', adding this column would have used 'Course name (linked to course page)' as the column heading. Now instead it will just use 'Course name'.
Note: This functionality hasn't yet been implemented. See bug T-7247.

Other parameters
----------------

The 'joins' parameter has been previously discussed on the A Basic Join page. It accepts either a string containing the name of a join, or an array of join names if multiple joins are required.
The 'displayfunc' parameter is described on the Column Display Functions page.
The 'extrafields' parameter is described on the Combining Multiple Fields page.

Advanced parameters
-------------------

The 'grouping' parameter can be used to aggregate a column. See [aggregation and grouping] for details.