Hi All,
I'm learning, so go easy on me. I'm trying to make one report link to another, but the second report isn't filtering when it opens. I think the problem is the "Teachers" link in the first report isn't correct, but I'm not sure what's wrong. When the link is clicked, the correct report opens, but it's not filtered. Here are the two reports, based on various reports in the Ad-Hoc page.
LS Students, returns all the students with the LS field marked True on their profile, a link to their profile, and a link to the Teachers List report.
SELECT CONCAT(u.firstname," ",u.lastname) As "Student Name"
,CONCAT("%%WWWROOT%%/user/profile.php?id=",u.id) AS "Student Profile"
,CONCAT('<a target="_new" href="%%WWWROOT%%/blocks/configurable_reports/viewreport.php?id=15&filter_users=', u.username, '">List Teachers</a>') AS Teachers
FROM prefix_course as c
JOIN prefix_enrol AS en ON en.courseid = c.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_user as u ON u.id = ue.userid
LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS tra ON tra.contextid = ctx.id
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
WHERE uid.fieldid = 1
AND uid.data = 1
AND tra.roleid=3
AND c.visible=1
GROUP BY u.id
ORDER BY u.id
And the Second Report: Teachers by Student is filterable based on a username (supposedly set by the previous link), each teacher (displayed as an email), and the courses that teacher teaches that has the student enrolled in it.
SELECT CONCAT(tu.firstname,".",tu.lastname,"@nsaschool.ca") As Email
,c.shortname as Course
FROM prefix_course as c
JOIN prefix_enrol AS en ON en.courseid = c.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_user as u ON u.id = ue.userid
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS tra ON tra.contextid = ctx.id
JOIN prefix_user AS tu ON tu.id = tra.userid
WHERE 1=1 %%FILTER_USERS:u.username%%
AND tra.roleid=3 #Teacher Role ID
AND c.visible=1 #Course is visible
AND c.shortname LIKE "%22%"
Group BY tu.id
ORDER BY tu.id
If you have any ideas how to get a list of teachers for a specific student that is simpler than this, than I'm open for tips.