Configurable reports

Installation

1. Download the Custom Reports installation file and unzip it to the \blocks directory in your Moodle folder.
2. Login to Moodle as Administrator and click Notifications under Site Administration.

For more information see Installing contributed modules or plugins

Overview

This block is a Moodle custom reports builder.

It is designed in a modular way to allow developers to create new plugins in less than an hour.

Who can create custom reports?

Anyone with block, managereports or manageownreports permissions at SITE level or COURSE level.

What type of reports can I create?

- Courses reports, with information regarding courses.

- Users reports, with information regarding users and their activity in a course.

- Custom SQL Reports, custom SQL queries. This block can use the same SQL queries that Tim Hunt's Custom SQL queries plugin.

Note for developers: You can create your own type of reports.

Who can view the reports?

When you create a report you can select which users can view it.

The reports are displayed in a block in the course or site frontpage.

Creating a report

This is a very simple process.

If you are going to create a report for a course, you have to add the block in the course and click in the "Manage reports" link.

On the other hand, for site reports, add the block in the frontpage and click in the "Manage reports" link.

You must enter a name, an optional description, and you have to choose the type of report, pagination and exports formats.

Depending on the report choosen, there will be more or less tabs. These are the tabs for the courses and users report:

Columns: Here you can choose the differents columns of your report depending on the type of report. (Course Name, User firstname, etc..)

Conditions: Here you can define the conditions (i.e, only courses from this category, only users from Spain, etc.)

Ordering: Here you can choose how to order the report using fields and directions.

Filters: Here you can choose which filters will be displayed.

Template: You can modify the report's layout by creating a template.

Permissions: Here you can choose who can view a report.

Calculations: Here you can add calculations for columns, i.e: average of number of users enrolled in courses

Plots: Here you can add graphs to your report based on the report columns and values.

View report: Self explanatory

Tutorial

Creating a users' report

Requirements: Users from Spain, but no from Madrid, in this course. This report can be viewed only by users from Spain. A filter based on the user's city must be displayed. A pie plot showing the users' cities is required. A calculation table showing the total forum post views is required. The report must be order by users' lastname.

Let's see how easy it is!

First of all, install the block.

Go to the course where the report will be created.

Add an instance of the Custom Reports block.

Click on "Manage reports"

Click on "Add report"

Enter a name, a description and choose "Users report" as Type of Report

The report will be saved and you will be redirected to the first Tab named "Columns".

Adding Columns

Here you can define the report table properties, width, align, cell padding, cell spacing, etc.. Add a Column called "User profile field"

Choose the field "firstname" and enter a name for the column in the text field. You can leave blank the rest of elements.

Repit the process above for the lastname and city fields.

Add a Column called "User module actions".

Choose a module from the list, in this example, a forum.

Adding Conditions

Click on the Conditions Tab.

Add a Condition named "User field Condition".

Choose the column "Country", the Operator "=", and enter the value "ES" (this is the international code for Spain as Moodle stores it).

Add a Condition named "User field Condition".

Choose the column "city", the Operator "<>", and enter the value "Madrid".

Add a Condition named "Users in current report course".

These are the main conditions of the reports. Note that at the bottom there is a text box called condition, with this text:

"c2 and c1 and c3"

Here you can define a logic condition, in this case, we don't need to edit this condition but you can create complex expressions like:

(c1 and c2) or (c4 and c3)

Adding Ordering

Click on the Ordering Tab.

Add a ordering named "User field ordering".

Choose the column "Lastname" and the ordering "ASC" (Ascending).

Adding Filters

Click on the Filters Tab.

Add a filter named "User field filter".

Choose city.

Adding Permissions

Click on the Permissions Tab.

Choose "User field Value".

Choose the column "Country", and enter the value "ES".

Here you can add more permissions and a logic condition to be achieved.

Adding Calculations

Click on the Calculations tab.

Choose Sum.

Choose the column "User module actions".

Adding Plots

Click on the Plots tab.

Choose "Pie".

Choose the colum "City" as Name and Value.

Finally, add a few users in Moodle with the country Spain, and different cities, Madrid, Barcelona, Seville etc. and test the report.

You can download the report in differents formats (ods and xls), remember to check these options in the report page.

See screenshot

Creating a SQL Report

You can find a lot of SQL Reports here: ad-hoc contributed reports

Since this block supports Tim Hunt's CustomSQL Queries Reports, you can use any query.

Remember to add a "Time filter" if you are going to use reports with time tokens.

First of all, install the block.

Go to the course where the report will be created.

Add an instance of the Custom Reports block.

Click on "Manage reports".

Click on "Add report

Enter a name, description, choose "SQL" as Type of Report

The report will be saved and you redirect to the firs Tab named "Custom SQL"

Add this query (Courses activity):

SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id GROUP BY courseId ORDER BY hits DESC

Go to Calculations Tab

Add a Sum calculation, choose the hits column

Go to Plot

Add a Pie graph, choosing coursename as Name and hits as value

So, here you have, a report with a Graph and calculations based on a SQL Query.

You can add a Course filter, is very easy:

Go to filters, choose Courses

Edit the Custom SQL query:

SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id %%FILTER_COURSES:l.course%% GROUP BY courseId ORDER BY hits DESC

Note that we are adding a token called: %%FILTER_COURSES:l.courseid%%

Go to View report, and you will see a Courses Filter, choose a Course and click on Add, here you have a report filtered.

You cand add also, and Starttime and endtime filter

Go to filters, choose Stard / End date filter

Go to Custom SQL tab, this is the new query:

SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id %%FILTER_COURSES:l.course%% %%FILTER_STARTTIME:l.time:>%% %%FILTER_ENDTIME:l.time:<%% GROUP BY courseId ORDER BY hits DESC

Go to View report, now you can filter by time also

See screenshot

Advanced options

Templates

You can customize the report output using a template.

Just enable the Template option in the same name tab.

This is an example html code for displaying a list of users:

Header:

##reportname##

Record: <img src="http://yourmoodle.com/user/pix.php/id/f1.jpg"> Firstname Lastname City: City Footer:

graphs## exportoptions##

Embedded reports

A embedded report consists in a report's set of columns that are embeeded in other report.

You can embed a user report into a course report, in this case, the resulting rows will be expanded duplicating each row for each user.

Imaging you have a courses report, you can embed a user report, every row of the course report will be cloned for each user.

To embed a report just add a "Other report" column, a list of your reports will be showed.

Inside a course report you can embed a user report and the other way.

Developers documentation

It's very easy to create new report types, components and plugins.

Report: A report plugin is a folder located at /report. This folder contains a .class.php file with the child - class of the report_base.

You only need to modify a few methos to add a new report type. Please, take a look to any of the current reports. The SQL report is an example of a non-typical report, meanwhile the users and courses reports are standard reports.

The best way to create a report is duplicate an existing one.

Component: A component is part of a report, a component may be used by more than one report. The main Component is the component Columns, that is used to add the columns of a report.

Others components are filters, permissions, plot, calculations..

The best way to create a component is duplicate an existing one.

Plugin: A plugin is a component module. The component column has a few of plugins (coursefield, userfield, etc..) A plugins works usually for an unique report, but there are plugins that works for more than one report. The best way to create a plugin is duplicate an existing one.

Credits

Juan Leyva Moodle profile

Follow me in Twitter

Screenshots

Users report

SQL report