Filters are not working

Filters are not working

by michael michael -
Number of replies: 3

Hi,

i add these filters to my Configurable report (Filter categories, Courses, Start / End date filter, Search text) but it doesn't reflect anything the report result, which code shall i add to the custom SQL Query?

the main code i have till now in the SQL Query is:

  • SELECT u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(u.firstname , ' ' , u.lastname) AS 'Display Name', 
  • c.fullname AS 'Course', 
  • cc.name AS 'Category',
  • CASE 
  •   WHEN gi.itemtype = 'course' 
  •    THEN CONCAT(c.fullname, ' - Total')
  •   ELSE gi.itemname
  • END AS 'Item Name',
  •  
  • ROUND(gg.finalgrade,2) AS Grade,
  • FROM_UNIXTIME(gg.timemodified) 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 
  • and gi.itemtype = 'course'
  • ORDER BY lastname

Average of ratings: -
In reply to michael michael

Re: Filters are not working

by Randy Thornton -
Picture of Documentation writers

Michael,

You have to add the filters you want into the SQL code too, as placeholders, and you need to indicate which column it is filtering on. What code you add depends on what you are filtering.

See some examples here: https://docs.moodle.org/32/en/Configurable_reports#Creating_a_SQL_Report for several filters.

Also, there are examples in the Ad-hoc reports page, too: https://docs.moodle.org/32/en/ad-hoc_contributed_reports - just seach the page by %%FILTER to find some.

I did a commented example on how to use the date-time related filters, here: https://docs.moodle.org/32/en/ad-hoc_contributed_reports#How_to_use_Configurable_Reports_Date_Time_Filters.


Randy

Average of ratings: Useful (2)
In reply to Randy Thornton

Re: Filters are not working

by michael michael -

Thx Randy,

i'm just little bet confused, now i wanna to integrate these 2 codes but i cant,

the below  report code show me all the data i need except the date of completion:

SELECT 

u.idnumber AS 'ID', 

CONCAT (u.firstname , ' ' , u.lastname) AS 'Employee name', 

u.Department AS 'Department', 

c.idnumber as 'Course ID',

c.fullname AS 'Course name', 

cc.name AS 'Category',

CASE WHEN gi.itemtype = 'course' THEN CONCAT(c.fullname, ' - Total') ELSE gi.itemname END AS 'activity name',

ROUND(gg.finalgrade,2) AS Grade,

FROM_UNIXTIME(gg.timemodified) 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 


%%FILTER_SEARCHTEXT:CONCAT (u.firstname , ' ' , u.lastname, ' ' , u.idnumber):~%%

ORDER BY firstname

WIth:

SELECT

u.username,

c.shortname, 

DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%Y-%m-%d') AS completed

FROM prefix_course_completions AS p

JOIN prefix_course AS c ON p.course = c.id

JOIN prefix_user AS u ON p.userid = u.id

WHERE c.enablecompletion = 1

ORDER BY u.username

to have at the end these data in the report i need:

Employee ID

Department

Employee name

Course ID

Course name

Started

Completed

Grade

 

 

 

 

 

 

 

 

Average of ratings: -
In reply to michael michael

Re: Filters are not working

by Randy Thornton -
Picture of Documentation writers

Well, you need to do two things:

Add the items from the SELECT statement that you want to see altogether, so probably you want to add the date statement for the p.timecompleted to the main part so it is a column in the report.

Next you need to add the JOINs in the second query to the first in a way that you get the information from the course_completions table. Your first query already uses the user and course tables. So all you need to add to that is the JOIN for the course_completion table and connection it to the course and user tables. Something like:

JOIN prefix_course_completions AS p ON p.course = c.id AND p.userid = u.id

should do the job.

Of course, the course completion is just that, the date of the completion of the course as set in course completion, not anything else.


Average of ratings: -