Configurable reports (SQL Reports)

Configurable reports (SQL Reports)

by Trevor Li -
Number of replies: 3

I have installed the configurable reports, and I have been trying to create an SQL report that shows the following data: (these are the columns)

Course name, Module name, User name, Module completion state/time, Grades (if applicable)

I guess it should not be too difficult, but so far I have tried many times, and still could not get the correct results. There are always many redundant rows.

 

Could anyone please kindly help me out with the SQL?

Thank you in advanced!

 

And one more question, is it possible to add more than 2 "user profile field" filters to the report?

Millions thanks, and please pardon my poor English.

Average of ratings: Useful (1)
In reply to Trevor Li

Re: Configurable reports (SQL Reports)

by Dawn Brown -

Hi! I am attempting to do the very same thing. Has anyone helped you with this?

Average of ratings: -
In reply to Trevor Li

Re: Configurable reports (SQL Reports)

by Naveen eAbyas -

Trevor,

Can you share you select query, Then we can easily understood the problem.

 Did you tried DISTINCT in your query?

Average of ratings: -
In reply to Trevor Li

Re: Configurable reports (SQL Reports)

by Pam Blasius -

We use the following to generate our "My Grades" report for each of the users (thanks to another Moodler for the code):

SELECT
    `prefix_user`.`lastname`,
    `prefix_user`.`firstname`,
    `prefix_user`.`city`,
    `prefix_course_categories`.`name`,
    `prefix_course`.`fullname`,
    `prefix_grade_items`.`itemname`,
    `prefix_grade_grades`.`finalgrade`,
    `prefix_grade_items`.`itemmodule`,
    FROM_UNIXTIME(`prefix_grade_grades`.`timemodified`) as `date_complete`
FROM
    (`prefix_course`
    INNER JOIN (`prefix_user`
    INNER JOIN (`prefix_grade_grades`
    INNER JOIN `prefix_grade_items` ON `prefix_grade_grades`.`itemid` = `prefix_grade_items`.`id`) ON `prefix_user`.`id` = `prefix_grade_grades`.`userid`) ON `prefix_course`.`id` = `prefix_grade_items`.`courseid`)
        INNER JOIN
    `prefix_course_categories` ON `prefix_course`.`category` = `prefix_course_categories`.`id`
WHERE userid =%%USERID%%
HAVING (((`prefix_grade_grades`.`finalgrade`) > 0) AND ((`prefix_grade_items`.`itemmodule`) Is Not Null))
ORDER BY `prefix_course_categories`.`name`,`prefix_user`.`lastname`,`prefix_user`.`firstname`,`prefix_user`.`idnumber`,`prefix_course`.`category`, `prefix_grade_items`.`itemname`,`prefix_grade_grades`.`timemodified` DESC

Average of ratings: Useful (1)