Hi Stan,
I had the same problem and at the end I've find it out using this sentences below (see the FILTER commands)
SELECT u.lastname AS 'Apellidos', u.firstname AS 'Nombre', c.fullname AS 'Curso', gi.itemname AS 'Cuestionario', ROUND(gg.finalgrade,2) AS 'Nota', ROUND(gg.rawgrademax,2) AS 'Max.',
FROM_UNIXTIME(gg.timecreated, '%d/%m/%Y %H:%i') AS 'Realizado'
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 AND gi.itemname != 'Attendance'
%%FILTER_COURSES:c.id%%
%%FILTER_USERS:u.firstname%%
%%FILTER_STARTTIME:gg.timecreated:>%%
%%FILTER_ENDTIME:gg.timecreated:<%%
ORDER BY `Apellidos` ASC,`Nombre` ASC
I have been trying to figure this out for ever - thank you!!!!
Thanks for the reply Pedro.
When I go to the filter tab to add the filter, the only choices it gives me are category, course and date.
How do I add the filter to the view report page?
Stan.
You have also add the filter under the filters tab.
Hi Emma, That is where I have the issue.
I don't get the choice to add a user filter there.
Do I need to do something else to enable it?
For reference, this is the sql:
SELECT concat(u.firstname," ", u.lastname) AS 'name',
co.fullname AS 'Course' ,
CASE
WHEN gi.itemtype = 'course'
THEN co.fullname + ' Course Total'
ELSE gi.itemname
END AS 'Item Name',
ROUND(gg.finalgrade,2) AS Grade,
DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECOND) as DATE
FROM prefix_course AS co
JOIN prefix_context AS ctx ON co.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 = co.category
WHERE gi.courseid = co.id
%%FILTER_COURSES:co.id%%
%%FILTER_USERS:u.id%%
ORDER BY lastname,firstname,course
OK, that is very strange - I suggest checking you have the latest version installed or maybe doing a reinstall. Does the user filter show up on other reports?
Stan: I am on the same boat as you are. I don't have the choice to add the User filter.
I use face-to-face plugin as well, and was thinking of adding a face-to-face event Name filter, but haven't been successful yet. (I am using the latest version of Configurable reports on Moodle 2.5.1+)
The user field filter IS the user filter!
Thanks Emma!
The component I was using was the older version.
All solved.
Thanks... Is there any way to use the face-to-face session name as a filter? (May be custom code, if needed, where should I start)
You would just need to write an sql query that uses that table/field. I don't think you could filter by that but once you have your records it is easy to download to excel and filter there.
