There are a few things that need to happen to get this ready to integrate:

1. Porting to Moodle 2. Currently report builder is written for 1.9.
2. Remove Totara specific dependencies. This should be fairly easy.
3. Make any changes required by Moodle for core code. We've stuck to the coding guidelines so this should be minimal. The only things I can think of immediately are:
A. Untested on Oracle (postgres/mysql/mssql are all fine).
B. Currently implemented as a local module.
4. Make any changes to the API suggested by Moodle. Since report sources are modular it would be good to make any changes before there are too many report sources 'in the wild'. I'm open to feedback and ideas on how to improve or extend the current API.

Our intention is to start on 1. in the next month or two.

Simon

I believe that much of the "report builder" funcionality is in the "Configurable Reports block" (http://moodle.org/plugins/view.php?plugin=block_configurable_reports). This block is already ported to moodle 2.1.x so maybe we could analyze and develop on top of that block as Martin pointed out. What do you think?

Hi Federico,

Of course it's up to Moodle to decide which direction they would like to go - presumably it would make sense for them to evaluate both options and see which suits their needs better.

We're intending to continue to develop what we've done so we'd naturally be keen to get it into core (with us acting as maintainers), and we do have plans for ongoing development to extend it further.

I think one key strength of report builder is the API, which lets our plugin authors add reporting to their functionality easily - we already have 20 report 'sources' with more on the way. Another benefit is scalability - we already have users working with reports with 4 million+ rows, and have some scalability work going on at the moment to confirm we can support even larger organisations.

Simon

Can the code be released to the community to help speed this up or are we reliant on Totara LMS Ltd to do the work?

+1 to add Totara reports to Moodle.

Two things I would like to see added (in order of priority):

• SQL queries (this already exists in configurable reports and is based on Tim Hunt's original development). Would be good to have this as an additional source / report plugin
• Graphical output (configurable reports provides some basic pie charts, but it doesn't really rock the boat)

Alex

Mark,

We need to extract the Totara-specific parts of report builder (such as reporting on hierarchies, manager relationships, etc) and refactor the code so that when Totara is updated to the version of Moodle that includes report builder it's straightforward to re-add the Totara-only features. I think we're best placed to do that work as we already have experience with report builder and know what is reliant on Totara-specific features and what isn't.

It has taken us longer than we'd hoped but the good news is the port to 2.2 is underway so hopefully we'll have an update soon.

Alex,

Yes those are both features on the roadmap, I've actually already made some progress on integrating graphical reporting using the FLOT javascript library.

Simon

I'm the configurable reports plugin author, Nick Koeppen from the University of Wisconsin is doing an excellent work refactoring the plugin for Moodle 2.2 and onwards adding new functionalities like subplugins support and converting the plugin to a report plugin.

The new code and architecture are pretty good, there are also some functionalities like the templates, graphs, support for sql, calculations, export/import, duplication, cell formatters (in the new version) not present in the current totara implementation (as far as I know)
We are going to improve the performance also

If the decission of integrating totara report builder in core is made, I want to cooperate for adding such functionallities if possible (I don't want to mantain a plugin that is similar to one in the core)

is there any place I can take a look to the code the current totara report builder?

Hi Juan,

Report builder does support calculations (aggregation), export (including export to google fusion using OAuth for generating graphs) and formatters (called display functions in RB).

You can try out the moodle 1.9 version at https://demo.totaralms.com/. Login as demoadmin/demoadmin then click Site Admin > Reports > Report Builder > Manage Reports.

Simon

As per the discussion in the May 22 Developer Meeting it appears that this is not going to make it into Moodle 2.3. Is that correct? The "Fixed Branches" tag on this issue still says : "MOODLE_23_STABLE".

Fixed that.

Juan, your report plugin looks pretty good and I wonder how I missed it before!

http://moodle.org/plugins/view.php?plugin=block_configurable_reports

Anybody want to compare/contrast these two or vote on one?

The ability to generate reports on users across courses is vital . Consumers of reports typically want to understand the performance, status of learners across courses for reviews, appraisals, compliance etc. These are essential requirement in academia and in other contexts.

Underlying the development of a robust reporting option is the issue that most of our clients aren't organised in the same way as Moodle i.e. Moodle contexts. They are organised into divisions, teams, branches, classes, years etc. Clients need to be able to collate and access the data in ways that relate to their internal structures. So, for example, a regional director will expect to see data for all of the region's branches but the branch managers will only have access to the data for their branch's staff. Again one can extrapolate this for head of year/department etc.

I think the importance of this aspect is confirmed in Simon (Coggin’s) comment above "We need to extract the Totara-specific parts of report builder (such as reporting on hierarchies, manager relationships, etc) ". This indicates to me that Totara recognise that this is currently a key differentiator. Without the ability to report with reference to the organisation/institution’s hierarchy a reporting tool has much less appeal.

Our clients also express a strong requirement for more detailed activity and access reporting e.g.

• Report on specific activities in courses, especially SCORM, Quizzes and Assignments
• Report by Moodle course group
• Report on if course has been accessed
• Report on SCORM activity commenced but not completed
• Report if logged in to site

From what I have seen of configurable reports (I’ve not seen the reworked version that Juan refers to above) it looks like an excellent base for the reporting functionality that we all know that Moodle users are crying out for.

2.4?

Hi Ray,

I think you're absolutely right that site-wide reporting and reporting on groups of users is a very important feature. To clarify, I'm not talking about removing cross-course reporting, just the column options for reporting on hierarchies etc. because they are not in Moodle - we can't report on tables that don't exist!

In a moodle version the equivalent functionality will still be possible. We have column options and filters for user (custom) profile fields and cohorts so you could still generate an assignment or course completion report across the whole site then filter by one or more user profile fields.

We also have course custom fields in Totara, which we've offered to moodle too (see http://moodle.org/mod/forum/discuss.php?d=203936), which are also available as report columns/filters.

Regarding module specific reporting, we currently have specific report sources for SCORM, feedback, site logs, course completion, users, courses and assignments. New sources are pretty straightforward to write.

Simon

Our clients also are asking for more reporting that is site wide across courses. Here is a recent request:
1. Refresh tracking and annual reporting of user completion and/or ability to track and report on all users’ year-to-year completion
2. Report that identifies frequently missed questions by associates.
3. Report that generates completion of training, completion of certification and score obtained
4. Report to identify individuals who have not completed required training or certification
5. Report to be sorted by individual, asu, immediate supervisor/manager

Juan's block also allows custom sql reports to be added - this feels similar to allowing users to inject PHP directly into a page to me - Last time I checked his block it didn't completely filter out all dangerous SQL commands and doing this for all databases that Moodle supports could be quite problematic unless it is able to use it's own db user that we can lock down to access the db and generate reports.

Reportbuilder allows plugins to specify their own report sources which feels more like the "Moodle way" to me and allows us to easily extend reports in a secure manner. It does rely on developers making these changes though.

I'm not sure we would want our clients writing their own SQL reports to run on our servers for performance reasons either - it's possible to write really in-efficient sql that could generate a heavy impact on the server.

Instead of looking at individual features, let's go through the typical reporting components:

1. Data Sources
a. SQL tables - Totara and Configurable Reports let you select fields, but only SQL command gives you full access
b. pre-calculated fields - CR provides some useful calculations, e.g. course stats
c. external data - neither supports this

2. Selection of data
a. List - depending on the data source selected, both provide comprehensive lists of data to select from
b. Manual - only CR via SQL queries

3. Filters
a. Data filters - both similar
b. Interactivity - Totara far more Moodle'esque

4. Database operations
a. Conditions - both
b. Ordering - both
c. Calculations - both, but different ones

5. Permissions
Depending on the type of report selected, both allow for different types of permissions to be set

6. Viewing
a. Look & Feel - Totara feels like Moodle, CR feels like CR
b. Interactivity - higher in Totara

7. Other
a. Graphs - None in Totara, basic in CR
b. Import / Templates - None in Totara, supported in CR
c. Export - both
d. Drill-down - None. We have developed this for CR extending the SQL query type. Happy to provide if of interest
e. Notifications (schedule to send a report by email) - Totara only

This list is by no means complete. Having used both tools extensively, I feel that a mix would be most valuable to Moodle. Totara reporting feels much more user-friendly and Moodle'esque, but CR has some nice additional features. My personal preference is to use Totara reporting as a base and then add
+ SQL queries
+ SQL Drilldown
+ Calculations
+ Graphs
+ Import / templates

Regarding the comments about the danger of SQL reports I must say that this is an optional feature you can disable in CR using capabilities, the SQL report component is based on Tim Hunt's one.

I think that enabling SQL reports only to admins it's safe (an admin by default could do a lot of worse things like deleting all the courses)

I've played with the TOTARA report builder and it's true that looks more Moodle-style but I think it's due to "architecture" reasons and because the way I create the datasources and columns. It's a shame not having access to TOTARA report builder code, it will help me to understand how it works and how it's plugabble for a better analysis, also it will help me to do performance tests and for comparing reports types

In any case, whatever the decision, if this is going to be present in 2.4 I will stop developing CR and I will put my efforts into the official report builder for adding subplugins, we freezed the new version for a while, if anyone wants to check the code of the newversion is here (thanks to Nick Koeppen for the University of Wisconsin for him efforts):

https://github.com/nickkoeppen/moodle-block_configurablereports/commits/MOODLE_22_STABLE/

Admins can delete content/courses etc but allowing them to use SQL directly could allow them to severely damage the Moodle installation so it becomes unusable and very difficult to repair - it could also potentially allow them to perform DOS attacks on the database.

If we could get the sql reports using a different DB and or DB User this could be mitigated quite a lot but without this level of abstraction I'd voice our objections to seeing this capability in core... but the other features are great!

Hi Juan,

I've uploaded some developer docs to the moodle wiki here (linked from Developer Notes page):

http://docs.moodle.org/dev/reportbuilder

It's not complete yet but hopefully it gives enough there to understand how sources are created.

We are pretty much finished porting to 2.2 now, so the next stage for me would be to branch it and pull out the Totara dependencies - like you I'm not particularly keen to press on with that knowing that it might be wasted effort.

If there are any lurkers out there with views to share, please chime in with your thoughts!

I know that the HQ developers are busy with the 2.3 release but I wonder if anyone would be available to do a quick code review to assess the relative suitability of the two options?

Simon

Hi Simon,

Could you put the code for that somewhere? (even with dependencies, I just want to look at the code)

I would like to have a better understanding of what RB looks like now.

Sure no problem. You already have access to the 1.9 version (local/reportbuilder in the totara repo, pull to get the latest release). I'll add a 2.2 branch and PM you with details.

Simon

I spent the best part of a year working on the Totara team and the report builder is the main thing I miss so am really pleased to see it aiming for inclusion in M2.4! It was sad that it was originally proposed for M2.3 so hopefully it won't miss the next release. It sounds from Simon that Totara 2.2 is pretty much ready now though, and I guess that was the hold up, so it's looking more feasible now.

We also commonly have a requirement to build in organisational hierachy and allow users to run reports for their level of the hierarchy. Things like divisions, teams, branches etc, are easy enough to do in Moodle with a little customisation by setting up a group of related custom fields, and I think Totara Report Builder will report on custom profile fields natively.

If/when an organisation hierarchy plugin is built for Moodle then it would require a Report Builder source to be setup, and from my time working with Totara I remember these are pretty simple to build, and Simon has kindly added information to the Wiki. I think we used to estimate about 2d developer time to build a new report source, on average.

A common requirement we also get is for scheduled reports by email, which Report Builder does.

The real attractiveness of the Report Builder is that users can build their own reports using an easy to use visual interface rather than writing SQL queries. This is what I miss - we can easily create custom reports for our customers but the reality is that they would much rather build new reports themselves.

The average LMS Administrator among our customer base is not a developer with SQL knowledge, but they do often have people at their disposal who could setup new reports using SQL and I do think this would be a valuable feature if the security concerns can be overcome.

I don't have detailed knowledge of CR but it sounds good. A very useful response from Alex re the comparison, thanks

Simon, if it is not possible to put the report builder code public at this point (but if you are pressed for time then I'm sure there will be no shortage of volunteers to identify and strip out the Totara related code ) then is it possible to just get the T2.2 report builder code to Juan for evaluation? it sounds like he needs to make a pretty major personal decision about whether to continue developing CR, which is a significant decision not just personally for him but for the Moodle community too.

Great to see this ticket moving along

I am using CR for a long time (and Tim's CustomSQL admin report too) on many systems, and i would like to share that it is very flexible, SQL wise, to the extent that we could always generate the reports we needed: Site wide, Category level, Course level and user level. Obviously, If one extend its own Moodle tables with more properties like departments, class, teams... it can be easily added to the queries.

Many of those SQL queries are shared here:
http://docs.moodle.org/22/en/ad-hoc_contributed_reports

Recently, We added some more features to CR:
1. Admin can create and "push" report templates on a course level, site wide.
2. Added a course/report/configurable_report plugin to display the report under the Report plugin tree
3. Added personalized SQL variables @@USERID@@ and @@COURSEID@@ for template course reports
4. Added CR plugins for categories, years and semesters
5. Report display the number of rows returned
6. Hebrew support

All can be downloaded from:
https://github.com/nadavkav/RTL-BIDI-Hebrew-Moodle-Plugins/tree/master/blocks/configurable_reports

BTW, Large academic institutes (Universities), that look for much wider range of reports, which are integrated with other systems, use BI tools like: Pentaho, JasperReports or BIRT (See comparison: http://www.innoventsolutions.com/birt-jasper-pentaho-comparison-matrix.html )

Good point Nadav, I've seen a few JasperReports/Moodle integrations in the past. We have some customers who use Crystal Reports and they just need some guidance on understanding the Moodle db schema and then they can go off and build reports to their hearts' content. I guess it's about finding the right balance as to what report building features to provide in Moodle and at what point it might be more preferable to integrate with an off the shelf BI tool.

Just a quick note to second Mark's comments on client requirements and the general ability/willingness to engage with SQL queries.

yeah - don't get me wrong - I think having the ability to allow SQL reports would be great! - as long as it's done in a secure manner and we can mitigate both security and performance issues.

Hi Simon,

thanks for the documentation, it's interesting see the approach you follow for implementing the report builder.

Also is nice to see that there are additional components and subplugins for CR (I didn't know about it)

I think that a technical review from Moodle HQ would be interesting

I'm happy to see the interest around this very hot topic.

As CR has been around from some time now, maybe a good approach could be trying to integrate CR, with special focus on usability and performance. That may also open up possibilities for those who already uses CR to migrate to the new, integrated version. However, suggestion and/or contribution from other modules would be surely of help, thus obtaining a very cool reporting system.

Just my two cents

I’ve done a bit of performance testing to compare how the two modules handle large datasets. I realise I’m hardly impartial but I’ve posted exactly what I did here:

http://docs.moodle.org/dev/reportbuilder/benchmarks

and I’m happy to modify the tests or run others if anyone feels they’re not representative.

The main issue I see is that configurable reports database reads are scaling linearly with number of records, which will really impact large sites.

There may be some simple changes that CR could make that would improve performance, but I think it would struggle to match report builder’s scaling characteristics, particularly for more complex reports (like course completion reports which pulls in user profile fields and course metadata).

I’m sure that with custom SQL it would be fine (if the admin knew how to write efficient queries), but part of the purpose of this module is to minimise the knowledge of SQL needed by admins.

Looking at configurable reports in more detail, it does seem quite complementary to report builder in a lot of ways. Personally I think it would be a shame to see CR development stop if RB was integrated into core (and vice versa) as they do seem to serve slightly different audiences.

Simon

Hi Simon,

thanks for your performance tests, just a few notes:

• The 2.2 branch used for the test is not stable but I think the results will be the same using the 21 branch so it doesn't mind

• The branch used for test does not include yet the performance improvements I was thinking to implement (as you mention before are simple)

1. Precaching for querys
Instead one or two SQL queries per field per user, one or two per all the user affected, ie: Instead two queries per user for obtaining the profile fields, two queries that obtain all the profile fields of the users affected in the query for caching the result.

2. Full SQL compliant mode
Currently, you can create a plugin for each column you want to get in the report.

The plugin can retrieve information from the original dataset (in a user report, it can get all the fields included in the original SELECT * FROM dbprefix_user WHERE xxx) or it can perform additional SQL queries for obtaining additional information (time spent in a course)

I was thinking in adding a 3th way, basically it will be a SQL mode, a few joins and conditions to append to the original dataset SQL query for obtaining extra information directly from the database (I think is the way Totara RB does).
With this approach, the number of queries for obtaining the information you mentioned in your test will decrease dramatically

Simon, can I ask you if in Totara RB you can add columns (fields) with information that can't be obtained directly using a SQL query?
Something that requiere some PHP processing, like the time spent in a course that requires a mix of SQL queries and PHP processing

Wouldn't that mean you'd be storing the data for those columns for the whole report in memory?

You are right - RB combines joins and conditions to build one query, I think your SQL mode sounds like the way to go.

Report builder tries to obtain as much information as possible via the query, then does PHP-only calculations to combine the fields using display functions. While it is possible to do queries directly in the display functions I would really discourage it as it leads to the same per record scaling issues that we've been discussing (which can be mitigated slightly by pagination but not when exporting the whole report).

In cases where more complex logic is required (query DB, do some PHP, query again) we have a cron-based preprocessor that individual sources can hook into to generate a temp table containing the data they need. This avoids doing expensive queries when displaying the report at the expense of not using the live data.

Simon

Hi Simon,

Wouldn't that mean you'd be storing the data for those columns for the whole report in memory?

No, I mean that I can perform some caching using blocks of users (instead a query per user, a query for sets of users or something similar) I explain myself badly in the previous post

In cases where more complex logic is required (query DB, do some PHP, query again) we have a cron-based preprocessor that individual sources can hook into to generate a temp table containing the data they need. This avoids doing expensive queries when displaying the report at the expense of not using the live data.

Yes, this is a goodidea, is something similar to Moodle stats system does

In a previous message you mentioned:

Looking at configurable reports in more detail, it does seem quite complementary to report builder in a lot of ways

Can you clarify this last comment?

By the way, I will try to create a document with a technical overview of CR and their new features listed (including the features implemented in the dev branch)

Thanks

It would be nice to add an option to query a slave (read only, maybe?) replica of Moodle's production DB, instead of using the main DB, to off load the performance impact from the main production server.

Juan,

I think the biggest difference is the level of abstraction for report creators.

CR gives the report creator full access to the SQL, which means they have a lot of control, but need a level of understanding of how SQL works to get the most out of it - for example, when selecting conditions quite a few LMS administrators might not know the difference between "LIKE" and "LIKE % %".

RB on the other hand leaves the management of SQL to the source developer, so the report creator only selects the options they want from the available choices. That makes things simpler for the administrator, at the expense of losing the full power of SQL.

I think that the ideal reporting tool would serve both those audiences, and of course both our modules could be extended to cover the other case. Until then though, I still think there's still be a place for both.

Nadav,

We actually have a partner who is looking at developing this for RB - they want to set up a reporting server using postgres write-ahead logs to keep it synced to the master, then switch to use the reporting database for report queries. It's actually pretty straightforward to implement, and has the benefit of near real-time data while avoiding any performance hit on the main database. They have 180k users though - it's not something I'd expect most small/medium sites would need to implement.

Simon

For making the development of the new CR version more open, I have created a META-ISSUE with subtasks

http://tracker.moodle.org/browse/CONTRIB-3717

After I talk with Nick (from University of Wisconsin) I will add more info in the comments of each issue

Things have been quiet on this one for a while - just wondering what the next steps should be? Can Juan or I provide anymore information? Are there any other factors that should be considered?

The comments above don't seem to indicate a clear preference either way - it seems like it will be necessary for Moodle HQ to choose - if that can be done fairly soon I think there's time to get RB into shape for 2.4 (if required).

Simon

Hi,

I agreed to put up a req. specification on Docs to help MD evaluate the options. I've got a bit bogged down with it tbh. I'll have it up on Doc before the weekend. We need some progress on this too, trouble is once you start thinking about it in detail requirements can grow pretty quickly.

What are the pans for RB btw? I notice that Totara are advertising for developers to work on an updated version.

Okay thanks Ray, that would be really useful.

I've just finished a big refactor of the way filters were handled to strip out some unneeded classes and speed things up a bit. We've also just added cohort filters so you can filter reports to only show results belonging to members of specific cohorts.

One of our partners has some funding to add graphical reporting so it looks likely that will happen fairly soon.

Coming up I want to add support for filtering via joins so I can try to make use of get_enrolled_sql() to do per record capability checks with a single query. I also want to refactor the content restrictions code to allow admins to restrict report content using any of the search filters. I would also like to provide a UI for aggregating data (currently supported but only via report sources). And I need to port the unit tests from simpletest to PHPUnit.

One other report builder feature I'm not sure I've mentioned before - embedded reports! These are basically hard-coded reports embedded in a standard moodle page that can still be modified by an administrator. For example see: https://demo.totaralms.com/course/find.php They are really useful for building admin pages - they come with search, sort, export, etc and can be configured to suit the site's needs - the "Browse List of Users" pages would be a prime candidate in moodle.

Simon

Nearly there, just need to add to docs. I don't seem to have the ability to create a new page atm, i've asked Helen if she can help me out.

Our clients are really looking forward to better reporting. Will these reports incorporate at least the basic SCORM fields? The basic report they all want is:

• Status
• Score
• Start date
• Completion date
• Time spend in course

The report builder SCORM report source will currently let you report on:

• SCORM Title
• SCO Title
• SCO Attempt Number
• SCO Start time
• SCO Total time
• SCO Status
• SCO Score
• Time SCO Status last modified
• SCO Min Score
• SCO Max Score

as well as all the user, course and category fields. I think those cover most of what you need - assuming "time spent in course" means the total_time reported by the SCO and "Completion date" is the same as "Time SCO Status last modified". If not adding extra fields is pretty straightforward, we just need to know where the original data is stored to work from.

Simon

OK, here it is: http://docs.moodle.org/dev/reporting_spec

There more introduction than perhaps needed and I ran out of steam a little but I hope this is a useful summary of what we need for our clients.

Prizes for everyone who can read my handwriting.

Thanks very much for taking the time to put that together - very enlightening!

I've made an attempt to convert your spec into a set of user stories detailing the specific individual requirements:

http://docs.moodle.org/dev/reportbuilder/spec_as_user_stories

feel free to add to it or edit if I've not captured a requirement as you see it.

If it would be useful I could add a column detailing which of these can currently be achieved by RB and which would need more work.

The main three interface areas you mention ("what to show", "who to show" and "who can view") are all plugin types in RB, so even where the requirements aren't yet met adding the functionality shouldn't be too hard.

This is some very interesting work going on. We've found this to be one of the major areas of improvement for Moodle, and so have been working on our own custom ad-hoc reports.

Is the functionality from Totara slated for the next release of Moodle? If so, that would be great news.

Let me know if there's anything in the way of testing that you are looking to have done.

Following discussions at the hackfest, this will be looked at again in December when 2.4 is out, presumably with the aim of getting something into 2.5.

It's disappointing that this ticket has dragged on for a whole year since Martin first raised it. It looks to me like the current blocker is that HQ needs to make a decision as to whether CR or RB makes it into core. Once that decision is made, either the Totara team or the CR team can forge ahead, but reading through the comments it sounds like neither wants to invest that time if it might turn out to be wasted, which is entirely reasonable and understandable. Is this summing up correct?

I have three questions for various folks if I may

Martin: if this summary is correct, please can HQ prioritise this decision so that the developers can get started?

Simon: It sounds like the Totara RB has now been ported to M2.2, refactored and performance-optimised. This is all really great news, what is now left to do and how long would it take if HQ gave the go-ahead for getting RB into core?

Juan: You posted your CR task list at http://tracker.moodle.org/browse/CONTRIB-3717. How long would it take if HQ gave the go-ahead for getting CR into core, and would you need to raise funding to achieve this?

Cheers,
Mark

I promise we'll lay down a roadmap in December for getting this done in 2.5. There are a lot of variables at play here, including funding, a new logging subsystem, and the different features in all the different reporting systems being used now.

And when it's done I don't want to hear ANY whinges about it not being exactly what you imagined.

Hey if it does all of the above then it will be AWESOME!

I'd be interested to hear how much funding is needed for this work once the roadmap is finalised.

FYI, and just for the record...
We just started the new 2012/3 Academic year using CR on many of our systems (Moodle 2.3 and Moodle 1.9.x) and we are very happy with it. (using most of the following reports: http://docs.moodle.org/23/en/ad-hoc_contributed_reports )

Is this still in the discussion for Moodle 2.5? It appears it did not "make the cut" on the roadmap if I'm reading things right.

We have been watching this one and hesitating to start developing reports on one platform or the other, waiting to see if one (or both?) are likely to be on the roadmap for core.

We'd be happy to contribute either development help (Nick Koeppen had been helping with CR for a while) or other resources if that would help - this is a feature we are very excited about (University of Wisconsin).

Thanks!

Moodle 2.5 is out since some weeks now. Is there a chance to see it integrated in 2.6 ?

My understanding (correctly me if I'm wrong) is that reporting is seen as inherently linked to the new logging processes (since they will need to be reported on) so there is unlikely to be further movement on this until the logging spec has stabilized:

http://docs.moodle.org/dev/Logging_2

Personally, I think the use case that most people are asking for here is more about querying and reporting on existing structured data stored in Moodle's SQL tables, rather than extracting data from a "firehose" of logged data for analytics.

Obviously there will need to be the ability to report on the new logging data too, but I think the use cases there are actually quite separate - especially since the log storage is a plugin type in the new model so retrieving the data for reporting is necessarily delegated to the plugin.

Simon

It's 2 years since Martin opened this ticket 'Integrate Report Builder into core'. Just an alert to Watchers that if anyone wants to have a play with the Totara/Report Builder code, it is now available to fork from https://github.com/moodlehq/totara

FYI, I have made several important improvements to a block/configurable_reports fork
https://github.com/nadavkav/moodle-block_configurablereports

Which is not yet pushed into it's original Juan Leyva repository (me bad)

1. Reports can run on a different DB then the current (production) DB
2. Reports can run on a CRON schedualer
3. Several filter plugins added (list)
4. Several inline "SQL" special variable added (%%USERID%%, %%COURSEID%%, ...)
5. Site level report can be shared in all courses
6. New Counterpart report plugin that enables a teacher to run reports from Administration->Reports block (no need to add CB block)
7. Integrated DataTables.js for the report table
8. Integrated CodeMirror.js for highlighting SQL query code, while editing.
9. Initial integration of pChart2 library.
10. Using GITHUB as a repository to distribute and manage sharable SQL queries
11. More...

I will publiclly publish it soon

Thanks for the update Nadav - I like number 1 a lot - even being able to use a different user with read-only privs to the same db helps a lot with security.

Hi Nadav,

I'm fixing some bugs in order to release a new version of configurable reports (2.6), I would like to add some of the new features you have created in your fork

How do you think can we cooperate?

Regards

Hi Juan,

Since it was developed over time and I was fixing and updating features sporadically, they are not arranged in coherent commits or branches. sorry. And it is a little bit hard for me to separate each feature to a single commit or patch and send you pull requests.

Maybe...
If you could review all the commits I made (in my M25 branch) on top of your moodle21 branch, you can decide to cherry-pick some or merge all of them?

Or, maybe, you can fetch my repository and try out the M25 branch and use a diff tool to see the different code changes?

Adding the filter plugins can be easy. And the extra codemirror/datatables/pchart2 JS libraries can be relatively easy, I guess.

Tomorrow, I am off onto my once a year Vipassana meditation break, so I will be available again after the 22nd for more co-working on the plugin. Maybe a Google "Hangout" session?

Regards
Nadav

I will add an updated readme with all the changes too.

Hi Nadav,

well, I see that your commits are all starting Jul 17, 2013 I only made 7 commits since that date so I think I can cherry pick your commits and then merge mines

I'll keep you posted

Hi Juhan,
One more "feature" (I almost forgot about), I made a "wrapper" course report plugin for the block/configurable_reports that shows all the reports under the "Administration > reports" block. In case teachers do not wish to include the block in the course, but they still wish to view "shared" system wide reports that are available to them from the frontpage CR block.

https://github.com/nadavkav/moodle-report_configurablereports

Hi all,

Now that Moodle 2.5, 2.6 and (soon) 2.7 are out with changes for logging and events, perhaps it's (soon) the good time to talk again about a reporting tool, if we want it in 2.8.

Séverin

Hi all,

I have put significant time into integrating the Totara ReportBuilder tool into a stock Moodle install with success. My manager and I spoke with Mark Aberdour at the MoodleMoot about our progress in integrating the Totara ReportBuilder into Moodle and he advised that it may be valuable to the community. I have not fully made it self-contained, as it has some requirements outside of the plugin itself, but I figure with a bit of community help we could get this integrated into Moodle, utilising some of Moodle's new caching features, and possibly update it for use with the new events based system in 2.7.

Within the next week I will be creating a public fork of Moodle and integrating what I have into that.

Any ideas are welcome!

Moodle HQ are looking at this again for 2.8.

I spoke with Mark at EPIC and he said his guys could devote some time to getting this in at least a stand alone state. Currently I have a fork of official moodle set up (not with any official MDL bug tracker numbers or anything) with report builder running on stock moodle. It is just running, there's been nothing more done with it, and there are several bugs in it. Would love to get some advice on how to move forward in the official Moodle HQ way.

You can either comment here or email me directly at jfowler (at) charitylearning (dot) org

Hi Jerrett, happy to put some people on this, would love to see the Github code when you're ready, but should do some planning I guess with HQ so we don't duplicate effort. If HQ want to do dev, then we could put QA resource into it instead? Or can put dev work in and take on certain tasks. Be good to have a planning session maybe, be good for HQ to lead on the right process, etc...

I totally agree with you Mark. If we could get these questions answered, I'd be ok with complying with whatever is the best option for moving forward on this for Moodle 2.8.

I though I'd mention that Totara have offered to do this in the past as well - they were just waiting on feedback from HQ (not sure if they still have the resources to do this but it's great that others have offered too!) - we had a big discussion about this at the hackfest in Perth but have been waiting on the new logging stuff to land before HQ was able to look at the reporting requirements again. There is some support for reportbuilder - there is also some support for other reporting plugins and HQ/Martin needs to review the different plugins and architecture and decide on a way forward eg to repurpose reportbuilder, or one of the other reporting apis (joule/ellis both have reporting apis that I believe have been made available to HQ and there are also a few reporting blocks for them to consider as well). Our benevolent dictator gets to make a few decisions before a way forward can be planned - good luck with that Martin!

Yes we are more than happy to help integrate Totara's report builder if that is the path that is eventually chosen but as Dan says it seems that the first step is for Moodle HQ to evaluate the reporting options available and decide which (if any) would be most suitable to meet Moodle's needs in this area.

Jerrett Fowler Hey, any chance I could see have a link to the code you've been working on? Even if it's not in a working state. Since we're looking at perhaps integrating Report Builder into Core for 2.8.

John Okely Absolutely. It's on my company github account as a fork of Moodle 2.7+, just email me and I'll get you any information you need.

It would probably be a good thing if I, or someone involved with this project, wrote a bit of documentation on how the system works, where files are located, and what needs to be changed in it to make it run in stock, also anything that needs to be fixed or adjusted later on to be more moodle friendly.

There is some documentation on moodle.org from when this first came up here:

http://docs.moodle.org/dev/reportbuilder

Plus the totara user docs here:

http://help.totaralms.com/index.htm#How_Report_Builder_Works.htm

Also if you want to see the current code there is the Totara seedlings release here:

https://github.com/totara/seedlings

Simon

I more meant that we should expand the moodle documentation a bit more to address all the "TO DO" items that are lurking around.

And under this ticket we should include the history of what's been done, where the tricky files are lurking, any edits to moodlelib from the stock version, and any edits to core functionality that need to be addressed, as well as features in report builder that probably should be looked at full Moodle integration, rather than using the totara bits. It should also include where all files are currently located and in what direction those files will go in for moodle 2.8.

Jerrett Fowler sent you an email, not sure if it came through. If not, could you send something to 'john at moodle dot com'. Thanks

When is the 2.8 branch going to be up?

We're currently in a sync period which means the 2.7 branch is currently in sync with master(all changes go to both). However I believe master is classified as 2.8 from now onwards

I think 2.8 will officially begin development, separate from 2.7 (in master) on the 9th of June. This might explain what I mean more clearly.

Hello all. Posting here results of the reportbuilder API discussion yesterday on integration meeting.
The decision was: We do not allow one plugin to define API that is used by another plugin. Exceptions are subplugins and plugins that define requirements in version.php

As a result, we need to introduce new API in moodle core that will be called reporting
Base class for reportbuilder source files will be located in lib/classes/reporting/base.php and have a name core\reporting\base
Other core methods to work with reporting API may be located in lib/reportinglib.php (if there are a lot of them like in lib/eventslib.php) or lib/datalib.php (if there is just one method similar to get_log_manager())

The actual reportbuilder UI must be a tool plugin and not a report, because it is a TOOL to build reports and not an actual report. So the name of the plugin is to be tool_reportbuilder

Still to be discussed what part of functionality should be implemented in core and what part in the reportbuilder plugin.

I think that decision the integrators make yesterday is a mistake. Please reconsider. Alternative proposal in https://moodle.org/mod/forum/discuss.php?d=261673

Hi all,

I think this tool will be very useful, and more necessary than ever. Thanks for your work.

I was looking for a long while the development documentation (and also Totara docs) for this Report Builder. But I didn't find a feature I think is very useful.

Some reports may run for a long time before showing any results, so that it should be useful that reports could be stored in database, instead of showing live results.

Some of you may be asking "For a long while? Really?". Yes. In my University we developed a some kind of plugin to extract very customized reports. The shortest time for one of the reports, profiling the sql query, was 15 minutes, and others surpassing 30 minutes. In addition, these reports extract statistics from passed events, so rerunning the reports for a second time will include no change in their values. Therefore, we stored results of the report in database. In case that values may differ due to the current date (imagine a report for an academic year, but we are still in the middle of this period of time), we added to our plugin a button to recalculate the report.

So, in conclusion:

1. Do you think this kind of long-timed reports could be run with the report builder (if, obviously we add the corresponding sources)?
2. Do you think the report builder could consider a new feature of "stored results", in addition to "stored searches"? The idea is then to calculate a report once, and see it many times, making it efficient. And, if necessary, ask to recalculate the report on demand after the first time.

If you need some more information or details, just ask me.

So many thanks in advance.

Saludos,

Jordi

Hi Jordi,

Yes, Totara's report builder has a feature called report caching which allows you to pre-generate the report on a time schedule that suits (for instance nightly). This generates a temporary table with all required columns already joined, with indexes on every column. This makes displaying the report much quicker in cases where you have large numbers of rows and/or lots of joins.

There is also a scheduled report feature which lets users request reports via email (again they can set the schedule).

The documentation so far is mostly the report source API so it doesn't mention these features. The Totara docs for caching are here:

http://help.totaralms.com/index.htm#Build_a_New_Report.htm

(scroll down to the "Performance" section).

Both caching and scheduled reports have been made available to Moodle so I would guess they would probably be included in the Moodle version - someone from HQ could probably confirm.

Simon

Thank you so much Simon!

That sounds simply.... Excellent!

And now, a new question that has come into my mind:

Do you think it would be possible to make that, manually on demand or a certain date and time, specified report results be sent to an external system for accounting purposes, for instance?

For instance, in our University, Moodle could seed a datawarehouse system, which could build integral reports using several data sources (Moodle statistics, Ldap statistics, Academic data, etc). It is a feature that, if we adopt this tool, we will need to integrate.

May be this feature is not available in the Report Builder, but maybe you can through light on how it could be developed and integrated.

Thank you so much in advance.

Saludos,

Jordi

Yes we support that too. You can provide a system directory to keep reports in. When scheduling reports users can then choose between 'email', 'save' or 'email & save'. You can use a network file system such as NFS to put the directory on a different machine on your network.

Simon

Hi Simon!

Simply.... Cool!!! I mostly want this tool !!!

Indeed, when a report is calculated and saved or whatever, is this report builder triggering an event of some kind? Why am I making this question? Because just saving results in a CSV or so will not be sufficient for us: we need to insert those records in an external database system to seed our datawarehouse. So, if this tool triggers an event, we could develop a specific handler to catch the event, with information like the path of the generated file and the kind of report, and then react accordingly to if necessary.

Thank you so much for your comments.

Saludos,

Jordi

There isn't an event at the moment (it was written before events existed), but it would be pretty quick and easy to add one.

Simon

Thanks Simon!

Hello all, then. Could we add a new issue to this EPIC in order to trigger events when a report is stored in filesystem or so? I think it could be really useful! What do you think?

Thank you so much for your attention!

Saludos,

Jordi

Hello Jordi,

Please note that MDL-45708 is already included in the epic and deals with adding events to the report builder.

Hello Adrian,

Just forgot my question. Sincerelly, I missed just this issue from the whole set for this epic. Sorry!

Thank you so much!

Wonderful tool!

Jordi

I've worked with Totara with a couple of really large installations and, while I'm glad to hear that Report Builder is being adopted by Moodle core, I'd like to highlight a couple of areas of concern which I think might be worth addressing during the integration. Both of these comments are on the current builds of Totara and are therefore "as things stand".

Firstly, when a user tries to export a report in Report Builder the system attempts the query the database and generate a file containing that data within a single pageload. This does not cause too much of a problem on smaller sites, but on larger sites this can result in browser timeouts before the file is complete. I really believe that for report builder to work on big databases there needs to be a "queue" workflow (similar to what is seen in Microsoft SQL Reporting and other similar system) where a user requests a report and is then notified when the report has been generated and is ready for downloading. Without this the system functionality just seems "broken" on larger sites.

Secondly, I have seen some very undesirable behaviour from the report caching in Totara which creates new tables within the schema to store data for each report instance which uses caching. When generating a new cache the system will rename the old table with a timestamp while it generates an updated cache table. This approach seems less than ideal, and we have had an experience with a client's database where report builder created these tables but "forgot" to delete the old caches resulting quite quickly in the database being overloaded. This entire concept should be reviewed before integration if possible.

There are further issues which could bear consideration for future development as well; for example it is a real limitation that filters cannot be pre-applied to a custom report by the site administrator, and that only single filters can be applied to a report making it impossible to build rule sets to refine results.

I'm just sharing these experiences to highlight a couple of weaknesses within the current implementation; overall I'm really looking forward to seeing a properly integrated version of report builder and think it will be a valuable addition to core!

Thanks

Mark

Thanks for highlighting these bugs Mark! Greatly appreciated.

We will endeavour to fix these and other bugs (I created MDL-46795 to handle them) with report builder before it is released to core.

'filters cannot be pre-applied to a custom report by the site administrator' I'd like content options and filter options to be merged together so they essentially become the same thing, just one is done when setting up the report and one is done when viewing the report.

Chaining together rules are things we will be looking at in the future (although exactly how far in the future is anyone's guess)

Regarding the first issue, we have come across this and identified it as being due to the use of format_text on many fields which was needed to support the multi-language filter. I filed MDL-41483 as a proposed solution but that ended up being dropped in favour of MDL-43524 which is now in 2.7. I'm hopeful that this will greatly reduce load on large reports once Totara is on 2.7.

Queuing reports is possible via scheduled reports, but I agree that your suggestion of a queue is a good idea that we should investigate.

The second issue you mentioned is new to me. Mark Ward Could you point me towards any Totara discussion or ticket you've raised relating to these issues and I will investigate (email simon dot coggins at totaralms dot com) their status?

Another note from our experience with Totara report builder, with an enhancement that is often missing.
Currently the Access tab in report builder allows granting access to a report to either all users or to users that have a certain system role.
Often this is quite limited.
The ability to also allow report access by another method would be very useful. Options may include allow report access to chosen cohorts, and/or allowing report access to specific users.
Cheers.
Amir

Thanks for the feedback everyone, that sounds great. Simon Coggins, the second issue is on the Totara bugzilla on ticket 12670. There has been some activity recently which seems to suggest it may have been fixed by another bug fix, however I still think there should be a more robust way of ensuring these are cleaned up since accidentally leaving them in place can have big implications.

https://bugs.totaralms.com/show_bug.cgi?id=12670

Thanks,

Mark

Ok thanks Mark Ward I'll chase that one up. John Okely we probably have some fixes for other issues that have come up since you got the code that will affect the Moodle version, perhaps we should catch up some time to discuss?

Amir's suggestion for more access control options is a good one too (been on our list for a while). It should be fairly straightforward to implement since access restrictions are designed to be extended.

Simon

Simon Coggins That's great. This work may be still a while away before we begin (sadly) so it's possible things will have changed even more by then. But I'm definitely interested to discuss what's happened so far.

Was a decision made to integrate the Totara report builder into Core rather than Configurable Reports? We depend on the SQL functionality of CR, and I have a developer working on adding the report scheduling and caching features from OU's Ad Hoc reports to CR, along with some improvements to code sanitization/security checking.

The menu based report construction offered in RB (and in CR) is nice, and we have some users who want it, but we also need to be able to supply reports created at the administrator level that use full SQL features such as joins to do multi-course and multi-user cross-site reporting.

Has there been discussion about merging these two plugins, at least at the feature level, and putting the result in core?

Hi Elizabeth Dalton

We looked at both Totara and Configurable Reports (among others) and came to the conclusion that Totara's Report Builder would be the best starting point.

Multi-course and multi-user reporting are both possible in Report Builder. As it cannot be done easily in Report Builder yet, I'd be interested to know what kinds of things you use cross-site reporting for

If it is in high demand we can allow for SQL based reports to be set up in Report Builder. The main problems that come up with it though are the high learning curve, capabilities, privacy and security.

And if there's anything else useful that CR can do that RB can't, we'd like to add it in the long run. But in any case, Configurable Reports will still be available on the plugins database after Report Builder is put in core.

The new features for Configurable Reports sound really interesting, it would be great to see them contributed back to the plugins DB if possible.

Thanks for the added insight.

I doubt that the OU will stop using report_customsql any time soon, even if there is a more all-singing, all-dancing alternative in Moodle core that does most of the same things.

In time, we will have to decide whether customsql says as a separate plugin, or whether we add the features we have there to what is in Moodle core.

I don't know about Configurable reports block, but I would guess they would have a similar answer.

Likewise for our institution, I'm not sure we could move away from Configurable Reports very easily if custom SQL statements are not provided for. Still, the move to have a Report Builder as part of core is very positive - thanks for the hard work being done on this.

We are also using CR on 40+ servers nation wide. With hundreds of custom SQL reports.
And we have also added several features on top of Juan's plugin.
(as mentioned on one of my comments above)
We are also using the GISMO block for teachers who need incourse usage reports.
Which seems to make the teachers happy.

Our next move would be, BI outside of Moodle, which includes other systems (SIS,...)
http://www.cooladata.com/behavioral_analytics
http://blog.prediction.io/build-discovery-feature-course-platforms-predictionio/#.VA7puGB515R

To be clear, we are not doing multi-site reporting, but we run reports across multiple courses, users, and other tables. We recognize the potential security risks of SQL, but we restrict creation of new SQL reports to site administrators. There are features we rely on for our operations that require SQL. Without this aspect of Configurable Reports and the OU Ad Hoc Queries (customsql) tool, I would have to program the reports we need via PHP, which would be prohibitively time consuming. Because this feature is available, I can create or adopt SQL reports that non-admins can use for college operations. The repository of SQL reports available from CR is especially helpful.

My concern about the decision to go with the Totara report builder as the base of Core functionality is that the Configurable Reports plugin already works as a standalone plugin, without requiring any further work to excise code dependencies related to other functions of Totara. CR has been in use by the community as an available plugin for an extended period, and the author has been responsive to requests from the community. As a general policy, I would prefer to see plugins that have been available and tested by the wider community considered for inclusion in core, rather than functionalities that have previously only been available to a subset of customers of a provider, as part of a system from which they would need to be disentangled.

This is not to say that Totara (or any other Moodle provider) doesn't do good work, but that if that work is to be integrated into Core, it would be helpful for these features to be developed in a way such that they are easily published as standalone plugins for review by the community first, before being considered for integration into Core.

Our intent in the new features for CR is to contribute all code back to the community, with documentation. We have already discussed this with the CR maintainer.

Elizabeth Dalton Are you aware this? http://prototype.moodle.net/

Unfortunately the test acct doesn't appear to give access.

We gave up on CR some time ago and built our own solution. Despite Juan's hard work it never really seem to provide an elegant solution to common reporting requirements.

Thanks for the pointer to the prototypes site.

CR works well for us, for the most part, but I'm able to write SQL to give us what we need. Did you develop a general reporting tool, or individually code the reports you needed?

Elizabeth Dalton NP

We developed a different reporting approach that is super-easy for clients to use and provides most of the data they require most of the time in a few clicks. It's probably not fair on everyone else for us to discuss further in this Tracker issue but feel free to contact me at ray howtomoodle com if you'd like to know more. There's a short video intro here: http://www.howtomoodle.com/extra-services/learner-data-report

There is a really big need for "time dedication". CR has the means to calculate this via logs. This is really important for many Moodle users who use this for certificates or other educational purposes that require some time dedication recording. I love CR and would love to see it part of Moodle's core as it is now. If improvements are made, then even better!

It's been several months without further movement on this. Is any work being done and is there any time frame?

Unfortunately this is not currently being worked on here at HQ, and there is no current time frame. (Although there was heavy investigation and some prototypes made last year)

The best option for the time being (depending on what you need to do) might be Configurable reports.

Is the source code for http://prototype.moodle.net/reportbuilder available anywhere?

Yeah, it's on my github: https://github.com/xow/moodle/tree/MDL-30193-master

I might need to add a quick disclaimer: it's a very early prototype, with a whole bunch of extra totara code that is not related to the report builder. It has not been tested, and was not designed for use on a live site.