Configurable reports block (plugin)

Thanks Juan. It looks great. I'll give more feedback once I've installed it.

The block have been renamed from Custom Reports to Configurable Reports (to avoid confussion with Tim Hunt's Custom SQL Reports plugin).

Thanks to the community feedback I have fixed some bugs, the block is available for download in the Modules and Plugins Database:

http://moodle.org/mod/data/view.php?d=13&rid=4283

Please, wait 24 hours to download it

For bugs reporting and issues request, visit:

http://tracker.moodle.org/browse/CONTRIB/component/10753

After looking at the tool a little more, there are a couple other features that would great:

Mass Export - ability to export multiple reports to excel at once instead of having to go through each one and click for each report

Export/Import the report itself - Here at UCLA we have multiple installations of Moodle, but are using the same code base.  As it is, we would have to re-create all the reports manually on both systems, but if we could export the report and create a new report on the other system via a report-import, that would be priceless.

Thanks for all your hard work on this tool Juan!

Nick

In the following link is available for download the last version of the block:

Notes:

- User profile fields are included in conditions and also permissions as Jason requested

- A basic print report option is included as Andreas requested

- A bug in the SQL Query on user filter (only for postgres db) is fixed, thanks to Barbara for debugging the plugin with Postgres

- The manage reports table is sortable and the default order  report's name. Thanks Luis for the suggestion

Download link:

http://tracker.moodle.org/secure/attachment/21926/configurable_reports.zip

Hola Juan,

Estaba intentando instalar tu módulo, que por cierto me parece grandioso, pero lo descomprimo en la carpeta blocks, voy a la opción de notificaciones pero no pasa absolutamente nada.

Espero que me puedas dar una pista,

Saludos.

PD: Uso,

Moodle 1.9.9.

IIS 6.0

SQL Server 2005

Windows Server 2003

Hi Juan,

This is a great block, i have been trying to find something that reports student enrolments by course and time frame, eg the month of November.

I seem to be having trouble figuring out which fields i am meant to use to do this. We use third party content for our delivery and need to report on the time that each student enrols in a particular course.

Would you be able to help me with my query, as i am not good with sql and only moodle when i get a chance.

Thanks,

Sarah

Hola Juan,

Por fin tengo el plugin funcionando! :D

Ahora quisiera extender la funcionalidad el plugin. Me he dado cuenta que en los reportes tipo sql no puedo agregar filtros de campos del perfil de usuario. Lo solucioné modificando una línea en la clase del plugin:

$this->reporttypes = array('users','sql');

Ahora me sale y puedo agregar el filtro, pero cuando ejecuto el informe (sin modificar el SQL) me saca éste error.

Fatal error: Call to undefined method report_sql::get_all_elements() in C:\Inetpub\wwwroot\moodle_xm\blocks\configurable_reports\report.class.php on line 295

Me puedes ayudar, cómo puedo hacer para agregar más filtros.

Gracias.

Hello Juan,

I have Oracle  11g and get the following error when I try to install. The change from mySql to Oracle  If I have a sequence for ID and set default for datetime for timemodified. I should be able to re-write the create statements.

I get the same error/complaint for all the table-create.

Anything else I need to check other than create stmt?

Thanks,

Chandrika

907: ORA-00907: missing right parenthesis

         ADOConnection._Execute(CREATE TABLE m_manual (  id int(10) unsigned NOT NULL auto_increment,  course int(10) unsigned NOT NULL default '0',  name varch..., false) % line  775, file: adodb-oci8.inc.php
      ADODB_oci8.Execute(CREATE TABLE m_manual (  id int(10) unsigned NOT NULL auto_increment,  course int(10) unsigned NOT NULL default '0',  name varch...) % line   89, file: dmllib.php
   execute_sql(CREATE TABLE m_manual (  id int(10) unsigned NOT NULL auto_increment,  course int(10) unsigned NOT NULL default '0',  name varch...) % line  235, file: dmllib.php
modify_database(/apps/zend/apache2/moodle/mod/manual/db/oci8po.sql) % line  392, file: adminlib.php
upgrade_activity_modules(http://msutmoo01.umdnj.edu/admin/index.php) % line  433, file: index.php

Hi Juan:

Very nice plugin! I played a little bit with it and tried to embed some columns from one report into another. This works fine, however one can embed columns from only one report at a time unfortunately...

Not sure if it is possible to modify the plugin to be able to embed columns from different reports?

Great work.

Thanks

Karim

HI

Great plugin i must say!

ok, i need some more filters adding. Courses, start and end dates are good but i need some of my own. How do i go about adding my own filter?

So, i basically want to create custom filters - how do I go about doing so?

Thanks Alot.

Hi

Does the PHP  code need to be edited if i wanted to add custom filters or is their a workaround does any one know?

If a php code file needs to be edited, which one do we add the custom filter code to?

Much appreciated thanks.

Juan

The first thing I would like to say is great job on this plugin.

I'm intrested in being able to create a configurable report. I have view the samples they have listed which were nice but I didn't understand how to add my users to the report by the personal settings that I have added on. So I have added fields to the USER PROFILE FIELDS. Can any one help direct me in the right direction. I have listed the fields below.

Thanks for you Help

Charles

User profile fields Personal Info. Profile Field Edit Last Name First Name Middle Initial Ticket Number # Case Number Date Ticket Issued County where ticket was received Address City/Town State Phone Number Driver's License Number Date of Birth How You Received Our Information Where did you attend court?

Hello all,

I haved created a new type of report: Categories report

This type of report can embed Courses reports, so it is a good choice for displaying the courses grouped by categories or only display certain categories, etc...

There is also a new type of report "Timeline report", that was added in previous releases

You can download the plugin and overwrite all the files (it doesn't require upgrade the dabase 'cause there are no modifications)

By the way, I'm working in the Moodle 2.0 version of this plugin, suggestions are very welcome.

For Moodle 2.0 a Cohorts report will be available

Also new columns regarding course completion for the user report

Juan

Hola Juan, he instalado Custom Reports y va de maravilla, pero encontré un problema en el enlace de descarga. Al ejecutar el informe la primera vez el enlace funciona bien, pero si se aplica algún filtro, la URL pierde el identificador del curso (pe.:  .../viewreport.php?id=2) y por tanto el enlace a la descarga tambien lo pierde y queda algo como: /viewreport.php&download=1&format=xls

Esto imposibilita descargar el fichero ¿hay alguna previsión de solucionar esto? descargué la última versión pero falla igualmente.

Gracias y buen trabajo.

Hola Juan.

Me he bajado e instalado Configurable Reports, una maravilla, felicidades. Pero tengo un problema en un informe que necesito. Necesito sacar en cada curso, los alumnos con sus correspondientes evaluaciones por cada módulo.

Algo así:

Alumno| .... | Test1Hecho | Nota Test1 | Fecha Test1 |....etc.

Respecto a las 3 columnas del medio, en la primera (Test1Hecho) no tengo problemas pero de la segunda, la Nota referente a un módulo y la tercera, fecha, no las consigo sacar. He probado la Nota en "User module outline stats" pero en el report dicha columna me aparece en blanco en la primera para todos los alumnos y no sé si es problema mío o que no sé puede hacer y la fecha no sé ni donde buscarla.

Tan solo quería saber si es posible sacar estos dos atributos de forma directa o me debería buscar la vida por otro lado.

Muchas gracias de antemano.

Thank you, we've wanted this for some time.

Hi,

I need to add custom filters to the Progress/Grades page (grade/report/grader/index.php). Currently, this page displays all students. I would like to add the following filters:

Date range
Church Code (custom field created for the user profile)
Course content (assignment, test, etc.) - multi-select field

In addition, I also need to add Date range and church Code to the Excel Export option for this page (grade/export/xls/index.php). The export should also include the column of "Church Code" (which is a custom field we created for the user profile).

Please help me out. Can I use this component to do the same?

regards,

Sarfaraz

Hi,

first thank you Juan for this great plugin. I have been playing around a little with the block and have some problem with the line graph.

I don't get any lines in the graph. When i' am using the Detailed ACTIONs for each MODULE query out of the ad-hoc contributed reports for Example. I set the x-axis to the module, the line series to the actions and the count to the y-axis. I want to have a graph to show wich action is done how often in which module.

As result i get a blank graph, even the axis are only counting from 0-1(y axis) and 0-4(x axis). So it is not only he dosen't print the graph. It seems to me he don't calculate how much the scale has to be too. Does anyone have similar experience with the line graph?

By the way the pie chart works fine so it is not a Problem with printing at all. May be something in the calculation of the line chart itself?

As suggestion to enhance the plugin i would like to have more plots like a bar chart and so on and make them more customizalbe.

Regards

He intentado instalar este plugin en un Moodle 1.9.8+ pero me da un error al intentar añadir un informe. Ocurre después de rellenar el formulario de «Add report». Al darle al botón «Add» para que guarde el nuevo informe me devuelve una página con un error «badcomponent». Esto ocurre escogiendo cualquira de los tipos de informe.

Después de buscar por Google sin encontrar nada he echado una ojeada al código fuente y creo que he encontrado dónde me está dando el error: en el fichero «editcomp.php» en la línea 66. Lo que no sé es porqué.

Me puedes orientar un poco?

Saludos

Hello All,

I don't know anything about SQL but I am wondering if I can use this block to create a site wide grade report for all users.  I would like the report to show the final grade of each course the user is enrolled in for all users.  Is this possible using this plugin?  If so, can you provide an example of what settings to use?

I have been testing various types of reports and have not been able to create what I am looking for.  I am running into errors but I think they may be due to the settings I have selected.

Any help or suggestions for a site wide grade report is greatly appreciated.

This block is really great. I've installed it in 1.9.8 and things seem to work except my charts. I tried creating a simple list of countries my students are in and the pie chart didn't turn out so well. Any ideas? Is it just becasue I have too many values

Hello Juan thx for the block. I have successfully installed it and its working. But I have a small problem I have a requirement of exporting the "configuration block report" into pdf format. I know that there is pdflib file and tcpdf directory. but dont know where to start working. Should I include a pdf folder in export directory ? Please help me!

Hello again. When I am choosing "Course reports" I can see "course field filter" and "start/end date" filters from the drop down menu. How can I add more filters here through coding. Could someone tell me which file to modify according to my requirement. Kindly help me!. thx in advance.

HI

Could anyone know to add extra filters (Category, Course, Department etc.,) in the type of report 'Users Report' as we have only

1.  Userfield Filter

2.  Start / End date filter

I need to add extra filters

EABYAS TEAM (INDIA)

Hello. Please can someone tell me what the two files plugin.class.php and editplugin .php useful for  in the configuration block source code. thx

thx for the replies juan.     For the course report if I choose Category filter the drop down list of catergory filter contains catergory id's but not category names. Could you please tell me what appropiate file to change the code. I've went through the source code several times but to no avail. thx.

Hello. When I am creating custom SQL Report the default filters aswell as my custom filters are not working .But if I am creating other reports ( not SQL report) then filters are working . Please can someone help me ! .. thx

Hi all. I've installed this on 1.9 but not really had a chance to try it (until now) but my sql knowledge is from 20+ years ago and isn't up to much so need some help.

We have roughly 50 students enrolled across 6 courses. I have been asked to produce (by Monday !) a report on each student showing what resources they have looked at (number of times and dates) for each course.

Each student is enrolled on 3 courses.

The standard Moodle activity report for a course is not bad but the only way I seem to be able to use this is to grab a screenshot and then convert it to a pdf. When doing so the results are very small and hard to read.

The other issue is that for each of their assignments (we don't use gradebook as our criteria are HND pass/merit/distinction) I set up a forum with posts of useful info for them to read. The Moodle activity report just says they haven't made any posts (and I'm not expecting them to) but doesn't say which posts they have looked at.

My question is, will this plug-in allow me to create the required report and show all the relevant info .... and if so, how ? (help !)

Many thanks

Kevin

Hi Juan Leyva,

Components/filters/startendfilter

I need to add an  extra operator (equal to) in an array i.e., my start and end dates should work with 'equal to' operator like

%%FILTER_STARTTIME:r.enrolldate:=%%
%%FILTER_ENDTIME:r.enrolldate:=%%

But this isn't working in the plugin, the operator (equal to) isn't taking and it shows No records.

With Regards,

Lavanya

Hi Juan Leyva,

I need to add a new filter 'Department' and here is the code i worked on.

//$departmentlist = array_keys(get_records('user_info_data'));
$departmentlist = get_records_sql('SELECT data FROM mdl_user_info_data WHERE fieldid=6');
}
$departmentoptions = array();
$departmentoptions[0] = get_string('choose');
if(!empty($departmentlist))
{
$departments = get_records_select('user_info_data','id in ('.(implode(',',$departmentlist)).')');
foreach($departmentlist as $d)
{
$departmentoptions[$d->id] = format_string($d->data);
}
}
$mform->addElement('select', 'filter_departments', get_string('departments'), $departmentoptions);
$mform->setType('filter_departments', PARAM_INT);        
}
}

I am was able to display the filter dropdown succesfully, but need help in adding  my sql query like %%FILTER_DEPARTMENTS:----%%

WIth Regards,

Lavanya

Hi Juan,

How could we give conditions in an SQL report from database

ex : %%FILTER_STATUS:c.id LIKE P %%

I want all the rows starting with alphabet 'P' from database.

Very useful!

In moodle 2.0.2. , selections often do not work in FF.
fir instance in Time report the Timeline settings do not save and in a user report the Calculations do not work.

Google Chrome posts a 500 error. Does this have to do with my installation?

Hi Juan,

That's an awesome plugin!

I've just started working on moodle. Came across your plugin, found it amazing and installed it. Was just wondering, if I could create custom filters or add additional filters in the SQL report.

Thanks

Sandeep

Hola, ante todomuchas gracias por el gran esfuerzo que comparte con nosotros, por mi parte pienso que deberia haber alguna forma de retribuite el esfuerzo.

Te cuento que copie la carpeta a mi carpeta blocks y luego fui notificaciones y hasta ahi todo bien

Luego en la portada habilite el modo edicion y elegi en el desplegable la opcion de repportes personalizados, hasta ahi todo bien.

Luegome di cuenta que no cambio nada, pero por el contrario ya no tengo el desplgable para agregarnuevos modulos o bloques en la portada, para descartar el error me fui a un curso e hice lo mismo y elmismo problema, no carga el bloque y se me desaparece el desplgable.

Que podria estar pasando. Gracias

Does this work in 2.0+ ?

Hi Juan Levya,

I created SQL Report and now i want to give access to the selected users (that I choose) to view the report. How this can be done?

Hey,

Just a quick question as to how I would go about creating a report that sorts users by completion dates of either receiving a "certificate" or a quiz completion from the most recent date to latest? Any help would be much appreciated thanks!!

I was trying to use a custom template in SQL Report, but the "##pagination##" variable was not working. To fix it I've added a line in the "print_template" function on "report.class.php" file.

$this->totalrecords = count($this->finalreport->table->data);

I wrote it on the 549 line in my report class sourcecode.

I am a beginner in Moodle development, so I don't really know this was a good idea... lol

Hi, are any of you available for custom report development? (paid, of course) If so, please message me directly.

Thanks!

Glen

Juan,  

Is there any way to have automatically generated/emailed reports?  I don't think I can do that with a cron but perhaps there is a method.

I know the orginal custom SQL queries allowed scheduling and I have manager types who would dearly love a spreadsheet in their inbox each month.

Hi all,

last night I fixed all the bugs mentioned above.

Please, wait 24 hours to download the plugin again, you can overwrite the old php files with the new ones.

Thanks all for your help (and also for the fix of some bugs)

Regards

Hello/Hola Juan

Spanish, then English translation below

Estoy probando Configurable Reports y me parece justo lo que estaba buscando. Me encuentro un problema: Al hacer el tutorial llego a un punto en que me da este error:

<code>

Fatal error: Allowed memory size of 100663296 bytes exhausted (tried to allocate 82 bytes) in /usr/local/moodle/moodle_test/lib/dmllib.php on line 728

Fatal error: Class 'ADODB_Session' not found in Unknown on line 0

</code>

Si simplifico los detalles del informe consigue generarlo. Estoy haciendo pruebas sobre un curso real, de 20 alumnos y 4 meses de duración. Me extraña que pidiendo "aparentemente" tan poca memoria de fallo. ¿Te suena a conocido?

Muchas gracias

Luis Bou

-----------

Estoy probando Configurable Reports y me parece justo lo que estaba buscando. Me encuentro un problema: Al hacer el tutorial llego a un punto en que me da este error:

 I'm testing Configurable Reports and seems just what I've been looking for, but I'm having an issue doing the tutorial. I get this error

<code>

Fatal error: Allowed memory size of 100663296 bytes exhausted (tried to allocate 82 bytes) in /usr/local/moodle/moodle_test/lib/dmllib.php on line 728

Fatal error: Class 'ADODB_Session' not found in Unknown on line 0

</code>

 If I simplify the demands for the report, it gets done. I'm testing this module on a real course, 20 students, 4 months long. It seems strange that requesting "apparently" so little memory I get this error. Does it ring a bell?

Thanks a lot

Luis Bou

Hi Juan,

First, thank you for your plugin. It´s very important for moodle.

I have a couple of questions. I´m working work with this version of moodle 2011070100.00 Release 2.1 and with this version of the module: 2007101509.

This is the scenario in my moodle. I have categories, subcategories and courses. I found an external application of statistcs only in forum in moodle by time.

1. I´m testing Creating a SQL Report I´m using the query of Courses activity with filters everything is ok but in the parts of calculations and graphics, it doens´t appear the column´s names

2. Is it posible to filter first by category and then by courses. Disabling first the field of courses

 this is an external app

Thank you

I can help you with the part of emails

this is my skype carlosmatin.contreras  .

Now, I´m full time moodle developer

Gracias por todo !

Hola a Todos,

Quiero ejecutar este query en informeSQL y me sale error en base de datos. Les dejo el query para que lo prueben y me avisen si el módulo no soporta este query:

SELECT DATE_FORMAT( FROM_UNIXTIME( fp.modified ) , '%Y/%m/%d' ) AS date, 
(SELECT
count(fp.userid)
FROM  prefix_forum_posts fp,
	  prefix_role_assignments AS ra,
	  prefix_context AS ctx,
	  prefix_course c,
	  prefix_forum_discussions AS fd
WHERE 
      ra.contextid = ctx.id AND
      ra.roleid = 5 AND
	  c.id = 199 AND
	  ctx.instanceid = c.id AND
	  fd.course = c.id AND
	  fp.userid=ra.userid and
	  fd.course = ctx.instanceid and
	  fd.id = fp.discussion and
      DATE_FORMAT( FROM_UNIXTIME( fp.modified ) , '%Y/%m/%d' ) = date) as studentParticipation,
(SELECT
count(fp.userid)
FROM  prefix_forum_posts fp,
	  prefix_role_assignments AS ra,
	  prefix_context AS ctx,
	  prefix_course c,
	  prefix_forum_discussions AS fd
WHERE  
	 ra.contextid = ctx.id AND
     ra.roleid in (3,4) AND
	 c.id = 199 AND
	 ctx.instanceid = c.id  AND
	 fd.course = c.id AND
	 fp.userid=ra.userid and
	 fd.course = ctx.instanceid and
	 fd.id = fp.discussion and
         DATE_FORMAT( FROM_UNIXTIME( fp.modified ) , '%Y/%m/%d' ) = date) as teacherParticipation
FROM prefix_forum_posts fp,
WHERE fp.discussion in 
	(select fd.id 
	 from prefix_forum_discussions fd 
	 where fd.course = 199) 
group by date

 Es sobre participación de alumnos y profesores en los foros. Además, he agregado un nuevo tipo de gráfico. Va a salir tal como este: 

http://pchart.sourceforge.net/documentation.php?topic=advexemple26

De antemano muchas gracias, mientras yo sigo invetigando y cambiando, si es que puedo, código.

Que buen módulo me ha ahorrado mucho trabajo

Saludos,

Hi Juan,

I made a report of participation of students and teacher in forums. But now, the user wants a report by activity and item in this activity. I plan to make a new report, maybe is a hybrid between sql report and the other one something like course report. This report should have 3 filters one by activitity , items of the activity and the last one by dates. Something like this

I plan to make a hardcode report only with two tabs Report and ViewReport, settings the above filters or maybe (I guess I have to spend more time) one report for the community. What is you suggest ? 

Thank you and I hope you can answer me asap 

Regards,

Martin

Your block is great thnks juan and tim

Today I have installed this block into Moodle 2.1 and getting some error (1)array_shift require first parametre is array you are given object

(2)print_table is deprecated use html::writer

my question is .Is this block is compatible to Moodle 2.1?

Regards

Gautam

I have this installed (version 2010060101) in a 1.9.10 Moodle install.  It works great except for two issues I'm now facing.

One - When a User report is exported then reimported to a new course, the Course Name does not change.  (The report runs with the new course data though)

Two - Once the block is placed in a course, it can not be removed.  The edit buttons do not display.  They do show up on the home page instance of the block though.  I am trying to remove it from a course, but can't.

Any help would be appreciated. The server is not mine so can't do too much  "playing".  I was just contacted to write a SQL report but have seen these two issues and have been asked if I can fix them.

For those who are having problems with the Moodle 2 version, I recommend you to download the Control Version System (Git) version

https://github.com/jleyva/moodle-block_configurablereports/zipball/MOODLE_21_STABLE

It's allways updated with minors bugs fixed

Hi Juan,

first congratulations for a great module.

My problem is that I'm trying to make a custom report showing all grades for students in a specific course. I found and ad-hoc contributed report that does almos that "Site-Wide_Grade_Report_with_All_Items" but I need a specific course instead for all courses. I tried to filter by course in the filters tab of the custom report but it didn't work so I think I should modify the sql statement.

Can you please help me a bit? Thanks in advance,

hugo.

SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name', 
c.fullname AS 'Course', 
cc.name AS 'Category',
 
CASE 
  WHEN gi.itemtype = 'course' 
   THEN c.fullname + ' Course Total'
  ELSE gi.itemname
END AS 'Item Name',
 
ROUND(gg.finalgrade,2) AS Grade,
DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECONDS) AS Time

 
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
 
WHERE  gi.courseid = c.id 
ORDER BY lastname

Hi Juan,

I was wondering if this plugin could be used to generate a customizable user report (grades). We were hoping to put the college logo on the page as well as the course name, student name, instructor name, etc all on one easy to print page.

Can we do this with your plugin?

Thanks,
Randah

Hi,Mr.Leyva, your configurable report block is very useful,but I need some other filter like "all Participant","all course activity","all action","download in EXCEL or show in the website" which in the original course report.How can I get these filter? Thank you

Hi to everyone

I nedd a quick way to generated automated email notifications on Report -> Statistics? I need to know every friday the statistics for a particular class, Is this something that is easy to implement?

best regards

Hi Juan,

I have used your plugin. I have need to do i have to pull reports of total hits per course for a particular period. i.e start date to end date. How can i do that.

Hi Juan,

I have an error while adding COUNT in my sql query

SELECT

COUNT(DISTINCT u.firstname),

COUNT(DISTINCT u.lastname),
u.firstname AS "First Name",
u.lastname AS "Last Name",
u.email AS "Email",
u.city AS "City",
u.country AS "Country",
u.sex AS "Gender",
org.fullname AS "Program"

FROM
prefix_user u,
prefix_org org

WHERE
u.programid = org.id AND

1%%FILTER_EMAIL:u.email%%
%%FILTER_FIRSTNAME:u.firstname%%
%%FILTER_LASTNAME:u.lastname%%
%%FILTER_PROGRAMS:org.id%%
GROUP BY u.firstname

And when I choose SUM from calculations tab for firstname and lastname. Now COUNT is showing at the top display report as well as at the bottom calculations display report. I want only the COUNT to be displayed at the bottom calcualtions. Could you suggest me the exact sql query.

Thanks in Advance,

Lavanya

Thank you for this plugin, it works well with Moodle 2.3

I'm poor in MySQL so would really appricate if someone shared the custom SQL for this:

a. Total Login of students only from x date to y date

b. Total views by students only from x date to y date

c. Total time spent online by students only from x date to y date

I'm sure everyone needs these reports but I can't find these in the list of contributed reports here - http://docs.moodle.org/24/en/ad-hoc_contributed_reports

Thanks!

Anil

Note - the link to documentation in the first post is now outdated and should be changed.

I have successfully created a sql report to show the last course access date by each enrolled student in a given course. At the Site level, I can use a course filter so I can see the results for one course. However, at the Course level, I still need to use the course filter and manually choose the course to restrict the results to that course. Is there a token to restrict SQL report results to the current course context? My code is below:

SELECT c.shortname AS CourseID, 
concat(u.lastname,', ',u.firstname ) AS Username, 
u.email AS email, 
( SELECT FROM_UNIXTIME(max(time)) FROM prefix_log as l WHERE course = c.id AND l.userid = u.id) AS LastActivity
FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid =5
AND ctx.instanceid = c.id

I have created a SQL report which counts how many of various resources and activities are in each course. The report works as designed, but when I try to add a categories token, I get an error as soon as I select a category in the report.

My report SQL:

SELECT c.fullname AS Course
 
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
 
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
 
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%quiz%') AS Quizzes
 
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments

,(SELECT COUNT(prefix_page.id)
FROM prefix_page
JOIN prefix_course ON prefix_course.id = prefix_page.course WHERE c.id = prefix_page.course) AS 'HTML Pages'

,(SELECT COUNT(prefix_resource.id)
FROM prefix_resource
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course) AS Files

,(SELECT COUNT(prefix_url.id)
FROM prefix_url
JOIN prefix_course ON prefix_course.id = prefix_url.course WHERE c.id = prefix_url.course) AS Links

,(SELECT FROM_UNIXTIME(MAX(prefix_resource.timemodified))
FROM prefix_resource
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS 'Syllabus Updated'


FROM prefix_course AS c

%%FILTER_CATEGORIES:prefix_course.category%%

The error:

The part I've marked in red seems to be the fail point. This works if I don't have multiple nested SELECT statements, but fails in this report.

Any suggestions?

Juan or anyone who can help,

I used this under 1.9 sometime back and really enjoyed what it allowed but for some reason now when I tried to set it up and use it on 2.4+, I cannot get it to read from the database. I'm sure I'm missing something very simple but I've been banging away at it for a while now with no progress. Does anyone know what could be causing a databse read issue?

Hmm, well I cannot attach the image because I keep getting an

Invalid json error

message......all I'm getting today is error messages

I'm attaching a screen shot of the error I'm getting.

Jason

Juan,

Love the plugin.  Is it possible to add a tab to show the SQL/MySQL query that will be used for the report created?

Hi Juan, i like your plugin. but i wanted to know can we add some bar chart in user report?

like in this link
http://docs.moodle.org/25/en/images_en/thumb/4/4c/block_custom_reports_sample_user_report.png/600px-block_custom_reports_sample_user_report.png

i don't want to use pie chart, i want to use bar chart, can we?

anyway, thanks and sorry for my English.

Hi Juan,

Thanks for such a great plg-in.

Hi,

When I enable the template from the "Template" tab, I see the "Download Report xls" link seems to disappear from the "View Report" tab.

Is there any way of keeping both options on the "View Report" tab, with the Download link just ignoring the template and the Print report using the template?

Cheers

Jeff

Juan (or anyone maintaining this),

following are a few fixes for configurable_reports 2.2 (2011040105), needed for Postgres and MS SQL and in order for PHP not to generate warnings. It's only for parts that I've used. Please, include them in your next release.

index 5c42050..ede81bd 100644
--- a/components/conditions/usersincurrentcourse/plugin.class.php
+++ b/components/conditions/usersincurrentcourse/plugin.class.php
@@ -42,7 +42,7 @@ class plugin_usersincurrentcourse extends plugin_base{
                global $DB;
        
                $context = get_context_instance(CONTEXT_COURSE,$courseid);
-               if($users = get_role_users($data->roles, $context, false, 'u.id')){
+               if($users = get_role_users($data->roles, $context, false, 'u.id', 'u.id')){
                        return array_keys($users);
                }
                
diff --git a/components/filters/fcoursefield/plugin.class.php b/components/filters/fcoursefield/plugin.class.php
index e1951e1..b942f91 100644
--- a/components/filters/fcoursefield/plugin.class.php
+++ b/components/filters/fcoursefield/plugin.class.php
@@ -38,7 +38,7 @@ class plugin_fcoursefield extends plugin_base{
        }
        
        function execute($finalelements,$data){
-               
+               global $DB;
                $filter_fcoursefield = optional_param('filter_fcoursefield_'.$data->field,0,PARAM_RAW);         
                if($filter_fcoursefield){
                        // addslashes is done in clean param
@@ -71,11 +71,13 @@ class plugin_fcoursefield extends plugin_base{
                $reportclass = new $reportclassname($this->report);
                                
                $components = cr_unserialize($this->report->components);                
-               $conditions = $components['conditions'];
+               $conditions = isset($components['conditions'])
+                       ? $components['conditions']
+                       : array();
                $courselist = $reportclass->elements_by_conditions($conditions);
                                
                if(!empty($courselist)){
-                       if($rs = $DB->get_recordset_sql('SELECT DISTINCT('.$data->field.') as ufield FROM {course} WHERE '.$data->field.' <> "" ORDER BY ufie
+                       if($rs = $DB->get_recordset_sql('SELECT DISTINCT('.$data->field.') as ufield FROM {course} WHERE '.$data->field." <> '' ORDER BY ufie
                                foreach($rs as $u){
                                        $filteroptions[base64_encode($u->ufield)] = $u->ufield;
                                }

Hi

It's a great addin but I have a repeated error:

Function get_all_mods() is deprecated. Use get_fast_modinfo() and get_module_types_names() instead. See phpdocs for details

• line 2970 of /lib/deprecatedlib.php: call to debugging()
• line 45 of /blocks/configurable_reports/components/columns/usermodactions/form.php: call to get_all_mods()
• line 191 of /lib/formslib.php: call to usermodactions_form->definition()
• line 143 of /blocks/configurable_reports/editplugin.php: call to moodleform->moodleform()

Looks like a database error but can you advise?

Thanks

Hi Juan,

Yesterday I upgraded from moodle 5.2.1 to 5.2.3 and re-activated configurable_report again. I noticed that in debugging mode a message appears in every course frontpage with a visible configurable_reports block:

Warning: First parameter must either be an object or the name of an existing class in /var/www/moodle/blocks/configurable_reports/block_configurable_reports.php on line 124

It only appears at the frontpage of those courses but when I move to the block to work with it (building reports, view them,...) the message goes away.

I also noticed that cron stops when executing the configurable_report task reporting this error message:

Starting blocks
Processing cron function for configurable_reports....... started 12:58:55. Current memory use 47.9MB.
!!! La tabla "blocks"no existe !!!
!! 
Error code: ddltablenotexist !!
!! Stack trace: * line 554 of /lib/dml/moodle_database.php: dml_exception thrown
* line 1429 of /lib/dml/moodle_database.php: call to moodle_database->where_clause()
* line 159 of /blocks/configurable_reports/block_configurable_reports.php: call to moodle_database->get_field()
* line 300 of /lib/cronlib.php: call to block_configurable_reports->cron()
* line 88 of /admin/cron.php: call to cron_run()
 !!

I'm not programmer but I could track the source of the error to line 159 in  block_configurable_reports.php :

        $lastcron = $DB->get_field('blocks', 'lastcron', array('name' => 'configurable_reports'));

Changing blocks to block seems to sort it out the cron problem but It will be safer if you can confirm it.

Best regards form Vigo!

Toni

Hi Juan,

We are using your plug-in quite extensively and it's great.

Since the last upgrade to the latest version of configurable reports, we are unable to put a search filter on reports which do not have JavaScript enabled.

We are using this syntax in the SQL:

%%FILTER_SEARCHTEXT:u.lastname%%

And here is the error we are getting:

What are we doing wrong?

how to use php files in moodle...is that any component used ya module.

Need help! 

I have no SQL programming experience, and am trying to set up a customizable report.  I need to return only those records where the grade > 7.9.  I also need to add two custom user fields:  County and State.  I'm attaching a file with my code.

Hi,

We have been using this block for our courses for some time and it has provided us with the reporting functionality we needed for most of that time.  However, we're seeing some issues with time tracking on configurable reports that we really need to clear up.

We've noticed recently that the time tracking on our reports is inaccurate and we cannot pinpoint the reason.  The time shown for each user in the course also seems to change randomly; for instance, we had one user who had spent over seven hours in one course when we checked the report towards the end of March, but now the user is showing 0 time spent, which is definitely incorrect as they have completed both parts of the course.

Searching through the user data is not giving us any helpful information.  Does anyone have pointers that would allow us to troubleshoot this feature on our reports?  Thanks!

Hi,

I have very little experience with SQL and was hoping someone on might be able to help...

I am trying to create a configurable report that simply replicates the Course Completion report (found in the Course Completion block) for a single course, but with the addition of some custom user profile fields.  So the columns would be:

• idnumber
  
• firstname
  
• lastname
  
• profile_field_employmenttype(Custom user profile field)
  
• department
  
• profile_field_dept(Custom user profile field)
  
• profile_field_subsection(Custom user profile field)
  
• profile_field_section (Custom user profile field)
  
• profile_field_directorate (Custom user profile field)
  
• Course completion date
  
• Course completion status
  

The SQL I currently have seems to be pulling the right kind of info but there are issues...

1) The report is returning course completions for all courses across the site.  How can I restrict this so the report only returns course completions for one course? (the course where I add the configurable report block)

2) The report is returning 896 records which is not the full number records I am expecting,

3) I'm getting a lot of duplicated records in the report, by up to 4 times.

Here's my current code:

SELECT  u.idnumber AS 'ID' , u.firstname AS 'First' , u.lastname AS 'Last',  aa.data AS 'Employment Type', ab.data AS 'Department', ac.data AS 'Dept', ad.data AS 'Sub Section', ae.data AS 'Section', af.data AS 'Directorate', ag.data AS 'Area',

CASE

  WHEN gi.itemtype = 'course'

   THEN CONCAT(c.fullname, ' - Total')

  ELSE gi.itemname

END AS 'Item Name',

ROUND(gg.finalgrade,2) AS Grade,