Ok I had some help from a database person (who liked the more full version rather than AS statements) and now have the following with the Filters working and the Grades being looked up in the correct table/column (grade_grades.FinalGrade).
Just need to add in a CASE WHEN statement thing I guess which reflects looking at either the 'duedate' and 'cutoffdate' as this isn't always active. Currently the report shows '01-01-1970' for those fields that aren't active.
Also need to add in the +- 2 weeks from either the due date or cut off date!
SELECT
prefix_user.firstname AS "First",
prefix_user.lastname AS "Last",
prefix_course.fullname AS "Course",
prefix_course_categories.name AS "Trimester",
prefix_assign.name AS "Assignment",
Date_Format(FROM_UNIXTIME(prefix_assign.duedate), '%d-%m-%Y') AS "Due Date",
Date_Format(FROM_UNIXTIME(prefix_assign.cutoffdate), '%d-%m-%Y') AS "Cut of Date"
FROM prefix_assign_grades
JOIN prefix_assign ON prefix_assign.id = prefix_assign_grades.assignment
JOIN prefix_user ON prefix_user.id = prefix_assign_grades.userid
JOIN prefix_grade_grades ON prefix_assign.id = prefix_grade_grades.ItemID AND prefix_user.Id = prefix_grade_grades.UserId
JOIN prefix_course ON prefix_course.id = prefix_assign.course
JOIN prefix_course_modules ON prefix_course.id = prefix_course_modules.course
JOIN prefix_course_categories ON prefix_course_categories.id = prefix_course.category
WHERE prefix_grade_grades.FinalGrade is null AND prefix_course_modules.instance = prefix_assign.id
AND prefix_course_modules.module = 1
%%FILTER_CATEGORIES:prefix_course_categories.id%%
%%FILTER_COURSES:prefix_course.id%%
ORDER BY prefix_course.fullname, prefix_assign.name, prefix_user.lastname