Configurable reports: Difference between revisions

From MoodleDocs
m (clean up, typos fixed: github → GitHub (3), a ordering → an ordering)
 
(46 intermediate revisions by 14 users not shown)
Line 1: Line 1:
{{Infobox plugin
|type = Block
|entry = https://moodle.org/plugins/view.php?plugin=block_configurable_reports
|tracker = http://tracker.moodle.org/browse/CONTRIB/component/10753
|discussion = https://moodle.org/mod/forum/view.php?id=7979
|maintainer = [[User:Juan Leyva|Juan Leyva]], [[User:Sara Arjona Téllez|Sara Arjona Téllez]]
|float = right
}}
== Installation ==
== Installation ==


# Download the Configurable Reports installation file and unzip it to the '''\blocks''' directory in your Moodle folder.
# Download the Configurable Reports installation file and unzip it to the '''\blocks''' directory in your Moodle folder.
# Be sure that the final name of the directory is: configurable_reports, the full path in your Moodle installation will be blocks/configurable_reports
# Login to Moodle as Administrator and click Notifications under Site Administration.
# Login to Moodle as Administrator and click Notifications under Site Administration.


Line 9: Line 19:
== Overview ==
== Overview ==


This block is a Moodle custom reports builder.
This block is a Moodle custom reports builder. It its also connected to a [https://github.com/jleyva/moodle-configurable_reports_repository public repository of sample reports] (only plugin version 2.3 and above)


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


- Courses reports, with information regarding courses.
- Courses reports, with information regarding courses.
- Categories reports, with information regarding categories. A courses report can be embedded in this type of report.


- Users reports, with information regarding users and their activity in a course.
- 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 [http://moodle.org/mod/data/view.php?d=13&rid=2884 Tim Hunt's Custom SQL queries] plugin.
- Timeline reports, this is a special type of report that displays a timeline. A course or user report can be embedded in this timeline showing data depending on the start and end time of the current row.
 
- Custom SQL Reports, custom SQL queries. This block can use the same SQL queries as [https://moodle.org/plugins/view.php?plugin=report_customsql Tim Hunt's Custom SQL queries] plugin.


Note for developers: You can create your own type of reports.
Note for developers: You can create your own type of reports.
Line 47: Line 61:
You must enter a name, an optional description, and you have to choose the type of report, pagination and exports formats.
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:
Depending on the report chosen, 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..)
'''Columns''': Here you can choose the different 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.)
'''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.
'''Ordering''': Here you can choose how to order the report using fields and directions.
Line 61: Line 75:
'''Permissions''': Here you can choose who can view a report.
'''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
'''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.
'''Plots''': Here you can add graphs to your report based on the report columns and values.
Line 71: Line 85:
=== Creating a users' report ===
=== 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.''
Requirements: ''Users from Spain, but not 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 ordered by users' lastname.''




Line 93: Line 107:
'''Adding Columns'''
'''Adding Columns'''


Here you can define the report table properties, width, align, cell padding, cell spacing, etc..
Here you can define the report table properties, width, align, cell padding, cell spacing, etc.
 
Add a Column called "User profile field"
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.
Choose the field "firstname" and enter a name for the column in the text field. You can leave the rest of elements blank.


Repit the process above for the lastname and city fields.
Repeat the process above for the lastname and city fields.


Add a Column called "User module actions".
Add a Column called "User module actions".
Line 132: Line 147:
Click on the Ordering Tab.
Click on the Ordering Tab.


Add a ordering named "User field ordering".
Add an ordering named "User field ordering".


Choose the column "Lastname" and the ordering "ASC" (Ascending).
Choose the column "Lastname" and the ordering "ASC" (Ascending).
Line 172: Line 187:
Choose "Pie".
Choose "Pie".


Choose the colum "City" as Name and Value.
Choose the column "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.
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.
You can download the report in different formats (ods and xls), remember to check these options in the report page.


[[blocks/custom_reports/#Users_report | See screenshot]]
[[blocks/configurable reports/#Users report|See screenshot]]


=== Creating a SQL Report ===
=== Creating a SQL Report ===
Line 202: Line 217:
Enter a name, description, choose "SQL" as Type of 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"
The report will be saved and you redirect to the first Tab named "Custom SQL"


Add this query (''Courses activity''):
Add this query (''Courses activity''):
Line 213: Line 228:
</code>
</code>


Go to Calculations Tab
==== Adding a calculation ====
# Go to Calculations Tab
# Add a Sum calculation, choose the hits column


Add a Sum calculation, choose the hits column
==== Adding a Plot ====
# 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.


Go to Plot
Note: Bar charts are currently not displaying on systems using PHP 7: https://github.com/jleyva/moodle-block_configurablereports/issues/101


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


So, here you have, a report with a Graph and calculations based on a SQL Query.
Filters add a control to the report that allows the user to select a subset of data when the report runs. Filters require two steps:


# Select the filter you want to use on the "Filters" tab
# Add SQL code to make the filter affect the output.


You can add a Course filter, is very easy:
These are the filters that can be used to constrain your report:


Go to filters, choose Courses
<code sql>
%%FILTER_CATEGORIES:
%%FILTER_COURSEMODULEID:
%%FILTER_COURSEMODULEFIELDS:
%%FILTER_COURSEMODULE:
%%FILTER_COURSES:
%%FILTER_COURSEENROLLEDSTUDENTS:
%%FILTER_USERS:
%%FILTER_ROLE:
%%FILTER_SEARCHTEXT:
%%FILTER_SEMESTER:
%%FILTER_STARTTIME:
%%FILTER_ENDTIME:
%%FILTER_SUBCATEGORIES:
%%FILTER_COURSEUSER:
%%FILTER_SYSTEMUSER:
%%FILTER_YEARHEBREW:
%%FILTER_YEARNUMERIC:
</code>


Edit the Custom SQL query:
'''Using Filters'''
 
When using a filter you need at least one static WHERE condition, followed by one or more filters. For example
 
<code sql>
WHERE 1
%%FILTER_COURSES:c.id%%
</code>
 
You can technically add multiple filters, but once a user applies one filter to a report all filters for report are applied. So it can be a little tricky to have multiple filters on the same report. For example, if you add a course and date filter to a report, then if you just want to filter by course you have to make sure your date filters are set to date range that covers all courses.
 
Many of the filters are hard coded to be drop down menus which are sorted by database id number instead of alphabetically. This can be problematic if you have a large number of users, courses, or categories. However, you can use one search text filter per report, which can be used to work around the issue in some cases.
 
Many filters require a logical operator to specify how the filter works. For example
<code sql>
%%FILTER_STARTTIME:l.TIME:>%% %%FILTER_ENDTIME:l.TIME:<%%
</code>
 
You can add a Course filter; it's very easy.
# Go to filters, choose Courses
# Edit the Custom SQL query:


<code sql>
<code sql>
Line 244: Line 304:




You cand add also, and Starttime and endtime filter
You can also add a Starttime and endtime filter
 
Go to filters, choose Stard / End date filter


Go to Custom SQL tab, this is the new query:
# Go to filters, choose Start / End date filter
# Go to Custom SQL tab, this is the new query:


<code sql>
<code sql>
Line 260: Line 319:




Go to View report, now you can filter by time also
If you want to filter text (for example, lastname)
 
# Go to filters, choose Search Text filter
# Go to Custom SQL tab and type your query. Here is an example for querying the user table:
 
<code sql>
SELECT u.firstname, u.lastname
FROM prefix_user u
WHERE 1=1
%%FILTER_SEARCHTEXT:u.lastname:~%%
ORDER BY u.lastname
</code>
 
Replace WHERE 1=1 with your WHERE criteria. If you do not have any where criteria and do not specify WHERE 1=1, you will receive a SQL error.
 
One more example (for filtering a group of courses by category)
 
<code sql>
SELECT COUNT(l.id) hits, c.fullname as Course,c.id as CourseID
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id
%%FILTER_CATEGORIES:c.category%%
GROUP BY l.course
ORDER BY hits DESC
</code>
 
Go to View report, now you can filter by time also.
 
 
'''Filter Issue'''
 
CONTRIB-4414 describes an issue where if you use the date filtering function and specify the end date as, for example: 6/11/13, Configurable Reports only searches for end dates up until 6/11/13 12:00 AM. The afternoon data for 6/11/13 is not included, so data for 6/11/13 2:30 PM will be excluded.
 
The workaround to include the entire end date for the full 24 hour period is to change:
 
<code>
%%FILTER_ENDTIME:table.field:<%%
</code>
to:
<code>
%%FILTER_ENDTIME:table.field-86399:<%%
</code>
 
==== SQL variables ====
 
If you place one of these strings in your SQL queries, it will be replaced by the appropriate value (current course id, current user id, Moodle www root (url), etc.)
 
<code sql>
%%CATEGORYID%%
%%COURSEID%%
%%CURRENTUSER%%
%%DEBUG%%
%%FILTER_VAR%%
%%STARTTIME%%','%%ENDTIME%%
%%USERID%%
%%WWWROOT%%
</code>
 
For an example of how to use the %%FILTER_VAR%% to pass a variable into a report as a parameter in the report URL, see [https://docs.moodle.org/401/en/ad-hoc_contributed_reports#List_all_the_Posts_in_all_Discussions_of_a_single_Forum this query] as an example.]
 
[[blocks/configurable reports/#SQL report|See screenshot]]
 
=== Creating a timeline report ===
 
''This report assumes that you have created at least one user or course report''
 
Timeline reports are a special type of reports. A timeline report displays a timeline in which a course report or user report can be embedded.
 
In this example we are going to create a report showing the user logins to the site during the last month (day per day).
 
Create a User report (see tutorial above) with three columns (firstname, lastname and user statistics)
 
Select the statistic (User logins)
 
Create a Timeline report, in the Timeline tab you can select the dates or days of the report (in this case, the last 30 days) and the interval of time for every row.


[[blocks/custom_reports/#SQL_report | See screenshot]]
Add three "Other report column" columns, selecting all the previous user report columns.


And that's all, you can create a new type of plot (line graph). This graph is suitable for data depending on time.


== Advanced options ==
== Advanced options ==


=== Export and import of reports ===
Embedded reports exporting is not working because the reports include references to another reports. A workaround is edit the imported report for changing the references in the "Other report field" column.
Reports that uses the user module outline column can't be exported either
=== Use the same report in different courses ===
(Available only in Configurable Report version 2.3 and above)
In order to use the same report in different courses you have to check the "Global report" option where creating a new report. This report will be then displayed in all the courses where the configurable report block is displayed and the block instance settings are set to "Display global reports".
If you don't want to display global reports in a certain block instance you can always edit the instance settings for selecting "No" in the "Display global reports" block setting.
Notice that these types of reports inherit the context of the course where is displayed, so any condition related to course inside the report is calculated using the course where the report is being viewed
=== Reports can run on a different DB then the current (production) DB ===
(Available only in Configurable Report version 2.3 and above)
This functionality is for using a copy of the same moodle database, not for queries to other database sources.
Go to Admin -> Plugins -> Blocks -> Configurable Report settings
=== DataTables for the report table ===
(Available only in Configurable Report version 2.3 and above)
Go to Admin -> Plugins -> Blocks -> Configurable Report settings to enable this functionality
=== CodeMirror.js for highlighting SQL query code ===
(Available only in Configurable Report version 2.3 and above)
Go to Admin -> Plugins -> Blocks -> Configurable Report settings to disable this functionality
=== Using GITHUB as a repository to distribute and manage sharable SQL queries ===
(Available only in Configurable Report version 2.3 and above)
Go to Admin -> Plugins -> Blocks -> Configurable Report settings to point to your own repository
=== Cron SQL queries ===
(Available only in Configurable Report version 2.3 and above)
=== Settings to control security of SQL Queries ===
(Available only in Configurable Report version 2.3 and above)
Go to Admin -> Plugins -> Blocks -> Configurable Report settings to change the security level


=== Templates ===
=== Templates ===
Line 278: Line 465:
Header:
Header:


<code html4strict>
<syntaxhighlight lang="html">
<p align="center"><b>##reportname##</b></p>
<p align="center"><b>##reportname##</b></p>
<table width="60%" align="center">
<table width="60%" align="center">
<tr>
<tr>
<td>
<td>
</code>
</syntaxhighlight>


Record:
Record:


<code html4strict>
<syntaxhighlight lang="html">
<table width="100%">
<table width="100%">
<tr>
<tr>
Line 296: Line 483:
</tr>
</tr>
</table>
</table>
<br>
<br></syntaxhighlight>
</code>


Footer:
Footer:


<code html4strict>
<syntaxhighlight lang="html">
</td>
</td>
<td valign="top">
<td valign="top">
Line 309: Line 495:
</td>
</td>
</table>
</table>
</code>
</syntaxhighlight>


=== Embedded reports ===
=== Embedded reports ===


A embedded report consists in a report's set of columns that are embeeded in other report.
An embedded report consists in a report's set of columns that are embedded in another 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.
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.
{| class="wikitable"
|-
! Type of report
! Reports that can be embedded
|-
| Courses
| Users
|-
| Categories
| Courses
|-
| Timeline
| Users, courses
|}


To embed a report just add a "Other report" column, a list of your reports will be showed.
Imagine 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 shown.


Inside a course report you can embed a user report and the other way.
Inside a course report you can embed a user report and the other way.
==== Timeline reports ====
Timeline reports are a special type of reports. A timeline report displays a timeline in which a courses report or users report can be embedded.
Timeline reports should be used when your user or course report includes statistics (course stats or user stats columns). I.e: you can create a report showing the number of user logins to the site during a week or month.
== Reports repository ==
Configurable Reports is connected to two different repositories:
* https://github.com/jleyva/moodle-configurable_reports_repository A repository of any type of reports (users, courses, sql, etc...)
* https://github.com/jleyva/moodle-custom_sql_report_queries A repository of just SQL queries to be used in the SQL repor type
You can use your custom repository following this instructions:
* First, you need a GitHub account (free)
* Fork any of the previous repositorys, using the GITHUB fork tool
* Using GIT, clone the repository to your computer in order to add your custom reports (some GIT knowledge is needed)
* Go to Administration / Plugins / Blocks / Configurable reports and change the settings for pointing to your repository (GitHub account + / + repository name
* If you want to contribute to the official repository, use the Pull request GitHub feature (So I can easily add your reports)


== Developers documentation ==
== Developers documentation ==
Line 330: Line 557:
This folder contains a .class.php file with the child - class of the report_base.
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.
You only need to modify a few methods 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,  
Please, take a look at 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.
meanwhile the users and courses reports are standard reports.


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




Line 342: Line 569:
Others components are filters, permissions, plot, calculations..
Others components are filters, permissions, plot, calculations..


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




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


== How to request a new feature ==
* Go to the Plugin Tracker http://tracker.moodle.org/browse/CONTRIB/component/10753
* Check in the open issues if your feature have been already requested http://tracker.moodle.org/secure/IssueNavigator.jspa?reset=true&jqlQuery=project+%3D+CONTRIB+AND+component+%3D+%22Block%3A+Configurable+report%22+AND+status+%3D+Open+ORDER+BY+priority+DESC&mode=hide
* If not, create a New issue (Component: Non-core contributed modules Issue type: New feature) (You have to register yourself in the tracker)
* Select the component Block: Configurable Reports and your Moodle version
* Explain in the Description field your needs
* If you have funding, you can contact me using the forum or the moodle internal messaging system, since I work for a Moodle Partner I can work on your request.
* Vote and Watch the issue you have created


== Credits ==
== Credits ==
Line 355: Line 591:


[http://twitter.com/jleyvadelgado Follow me in Twitter]
[http://twitter.com/jleyvadelgado Follow me in Twitter]
[[Category:Contributed code]]


== Screenshots ==
== Screenshots ==
Line 367: Line 601:
[[Image:block_custom_reports_sample_sql_report.png|600px]]
[[Image:block_custom_reports_sample_sql_report.png|600px]]


[[es:blocks/configurable_reports/]]
 
== See also ==
 
* CONTRIB-2386
* [https://tracker.moodle.org/issues/?jql=project%20%3D%20CONTRIB%20AND%20component%20%3D%20%22Block%3A%20Configurable%20report%22 Block: Configurable reports] Tracker component
* [http://moodle.org/plugins/view.php?plugin=block_configurable_reports Modules & Plugins] database entry
* [http://moodle.org/mod/forum/discuss.php?d=159820 Help forum]
* [[ad-hoc contributed reports|contributed reports]] (in plain SQL)
* [https://github.com/jleyva/moodle-configurable_reports_repository Configurable Reports Repository on GitHub]
* [https://www.youtube.com/watch?v=lF6CuY2Qiw0 Configurable Reports as a Learning Analytics Tool] - iMoot 2015 presentation by Elizabeth Dalton
* [http://opensourceelearning.blogspot.co.za/2015/08/moodle-configurable-reports-plugin-all.html All Variables and Filters] - Lists the Variables and Filters you can use in a report
 
[[Category:Report]]
[[Category:Contributed code]]
 
[[es:blocks/configurable reports/]]

Latest revision as of 14:56, 3 November 2022

Installation

  1. Download the Configurable Reports installation file and unzip it to the \blocks directory in your Moodle folder.
  2. Be sure that the final name of the directory is: configurable_reports, the full path in your Moodle installation will be blocks/configurable_reports
  3. 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 its also connected to a public repository of sample reports (only plugin version 2.3 and above)

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.

- Categories reports, with information regarding categories. A courses report can be embedded in this type of report.

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

- Timeline reports, this is a special type of report that displays a timeline. A course or user report can be embedded in this timeline showing data depending on the start and end time of the current row.

- Custom SQL Reports, custom SQL queries. This block can use the same SQL queries as 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 chosen, there will be more or less tabs. These are the tabs for the courses and users report:

Columns: Here you can choose the different 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 not 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 ordered 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 the rest of elements blank.

Repeat 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 an 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 column "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 different 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 first 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

Adding a calculation

  1. Go to Calculations Tab
  2. Add a Sum calculation, choose the hits column

Adding a Plot

  1. Go to Plot
  2. 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.

Note: Bar charts are currently not displaying on systems using PHP 7: https://github.com/jleyva/moodle-block_configurablereports/issues/101

Filters

Filters add a control to the report that allows the user to select a subset of data when the report runs. Filters require two steps:

  1. Select the filter you want to use on the "Filters" tab
  2. Add SQL code to make the filter affect the output.

These are the filters that can be used to constrain your report:

%%FILTER_CATEGORIES: %%FILTER_COURSEMODULEID: %%FILTER_COURSEMODULEFIELDS: %%FILTER_COURSEMODULE: %%FILTER_COURSES: %%FILTER_COURSEENROLLEDSTUDENTS: %%FILTER_USERS: %%FILTER_ROLE: %%FILTER_SEARCHTEXT: %%FILTER_SEMESTER: %%FILTER_STARTTIME: %%FILTER_ENDTIME: %%FILTER_SUBCATEGORIES: %%FILTER_COURSEUSER: %%FILTER_SYSTEMUSER: %%FILTER_YEARHEBREW: %%FILTER_YEARNUMERIC:

Using Filters

When using a filter you need at least one static WHERE condition, followed by one or more filters. For example

WHERE 1 %%FILTER_COURSES:c.id%%

You can technically add multiple filters, but once a user applies one filter to a report all filters for report are applied. So it can be a little tricky to have multiple filters on the same report. For example, if you add a course and date filter to a report, then if you just want to filter by course you have to make sure your date filters are set to date range that covers all courses.

Many of the filters are hard coded to be drop down menus which are sorted by database id number instead of alphabetically. This can be problematic if you have a large number of users, courses, or categories. However, you can use one search text filter per report, which can be used to work around the issue in some cases.

Many filters require a logical operator to specify how the filter works. For example %%FILTER_STARTTIME:l.TIME:>%% %%FILTER_ENDTIME:l.TIME:<%%

You can add a Course filter; it's very easy.

  1. Go to filters, choose Courses
  2. 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 can also add a Starttime and endtime filter

  1. Go to filters, choose Start / End date filter
  2. 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


If you want to filter text (for example, lastname)

  1. Go to filters, choose Search Text filter
  2. Go to Custom SQL tab and type your query. Here is an example for querying the user table:

SELECT u.firstname, u.lastname FROM prefix_user u WHERE 1=1 %%FILTER_SEARCHTEXT:u.lastname:~%% ORDER BY u.lastname

Replace WHERE 1=1 with your WHERE criteria. If you do not have any where criteria and do not specify WHERE 1=1, you will receive a SQL error.

One more example (for filtering a group of courses by category)

SELECT COUNT(l.id) hits, c.fullname as Course,c.id as CourseID ,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id %%FILTER_CATEGORIES:c.category%% GROUP BY l.course ORDER BY hits DESC

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


Filter Issue

CONTRIB-4414 describes an issue where if you use the date filtering function and specify the end date as, for example: 6/11/13, Configurable Reports only searches for end dates up until 6/11/13 12:00 AM. The afternoon data for 6/11/13 is not included, so data for 6/11/13 2:30 PM will be excluded.

The workaround to include the entire end date for the full 24 hour period is to change:

%%FILTER_ENDTIME:table.field:<%% to: %%FILTER_ENDTIME:table.field-86399:<%%

SQL variables

If you place one of these strings in your SQL queries, it will be replaced by the appropriate value (current course id, current user id, Moodle www root (url), etc.)

%%CATEGORYID%% %%COURSEID%% %%CURRENTUSER%% %%DEBUG%% %%FILTER_VAR%% %%STARTTIME%%','%%ENDTIME%% %%USERID%% %%WWWROOT%%

For an example of how to use the %%FILTER_VAR%% to pass a variable into a report as a parameter in the report URL, see this query as an example.]

See screenshot

Creating a timeline report

This report assumes that you have created at least one user or course report

Timeline reports are a special type of reports. A timeline report displays a timeline in which a course report or user report can be embedded.

In this example we are going to create a report showing the user logins to the site during the last month (day per day).

Create a User report (see tutorial above) with three columns (firstname, lastname and user statistics)

Select the statistic (User logins)

Create a Timeline report, in the Timeline tab you can select the dates or days of the report (in this case, the last 30 days) and the interval of time for every row.

Add three "Other report column" columns, selecting all the previous user report columns.

And that's all, you can create a new type of plot (line graph). This graph is suitable for data depending on time.

Advanced options

Export and import of reports

Embedded reports exporting is not working because the reports include references to another reports. A workaround is edit the imported report for changing the references in the "Other report field" column.

Reports that uses the user module outline column can't be exported either

Use the same report in different courses

(Available only in Configurable Report version 2.3 and above)

In order to use the same report in different courses you have to check the "Global report" option where creating a new report. This report will be then displayed in all the courses where the configurable report block is displayed and the block instance settings are set to "Display global reports".

If you don't want to display global reports in a certain block instance you can always edit the instance settings for selecting "No" in the "Display global reports" block setting.

Notice that these types of reports inherit the context of the course where is displayed, so any condition related to course inside the report is calculated using the course where the report is being viewed

Reports can run on a different DB then the current (production) DB

(Available only in Configurable Report version 2.3 and above)

This functionality is for using a copy of the same moodle database, not for queries to other database sources.

Go to Admin -> Plugins -> Blocks -> Configurable Report settings

DataTables for the report table

(Available only in Configurable Report version 2.3 and above)

Go to Admin -> Plugins -> Blocks -> Configurable Report settings to enable this functionality

CodeMirror.js for highlighting SQL query code

(Available only in Configurable Report version 2.3 and above)

Go to Admin -> Plugins -> Blocks -> Configurable Report settings to disable this functionality

Using GITHUB as a repository to distribute and manage sharable SQL queries

(Available only in Configurable Report version 2.3 and above)

Go to Admin -> Plugins -> Blocks -> Configurable Report settings to point to your own repository

Cron SQL queries

(Available only in Configurable Report version 2.3 and above)

Settings to control security of SQL Queries

(Available only in Configurable Report version 2.3 and above)

Go to Admin -> Plugins -> Blocks -> Configurable Report settings to change the security level

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:

<p align="center"><b>##reportname##</b></p>
<table width="60%" align="center">
<tr>
<td>

Record:

<table width="100%">
<tr>
<td><img src="http://yourmoodle.com/user/pix.php/[[id]]/f1.jpg"></td>
<td><h2>[[Firstname]] [[Lastname]]</h2>
City: [[City]]
</td>
</tr>
</table>
<br>

Footer:

</td>
<td valign="top">
##graphs## 
<br /><br />
##exportoptions##
</td>
</table>

Embedded reports

An embedded report consists in a report's set of columns that are embedded in another 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.

Type of report Reports that can be embedded
Courses Users
Categories Courses
Timeline Users, courses

Imagine 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 shown.

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

Timeline reports

Timeline reports are a special type of reports. A timeline report displays a timeline in which a courses report or users report can be embedded.

Timeline reports should be used when your user or course report includes statistics (course stats or user stats columns). I.e: you can create a report showing the number of user logins to the site during a week or month.

Reports repository

Configurable Reports is connected to two different repositories:

You can use your custom repository following this instructions:

  • First, you need a GitHub account (free)
  • Fork any of the previous repositorys, using the GITHUB fork tool
  • Using GIT, clone the repository to your computer in order to add your custom reports (some GIT knowledge is needed)
  • Go to Administration / Plugins / Blocks / Configurable reports and change the settings for pointing to your repository (GitHub account + / + repository name
  • If you want to contribute to the official repository, use the Pull request GitHub feature (So I can easily add your reports)


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 methods to add a new report type. Please, take a look at 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 to 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 to duplicate an existing one.


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

How to request a new feature

Credits

Juan Leyva Moodle profile

Follow me in Twitter

Screenshots

Users report

block custom reports sample user report.png


SQL report

block custom reports sample sql report.png


See also