Moodle
  1. Moodle
  2. MDL-18323

interface for creating custom reports

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.0
    • Fix Version/s: None
    • Component/s: Administration
    • Labels:
      None
    • Environment:
      all
    • Affected Branches:
      MOODLE_20_STABLE
    • Rank:
      5044

      Description

      It would be wonderful if we had an interface that would allow admins to create custom reports.

      see this discussion: http://moodle.org/mod/forum/discuss.php?d=116895

      This might be a GSOC project.

        Activity

        Hide
        Dan Poltawski added a comment -

        Hmm, the more I think about this the more I think it would be surprisingly difficult to do in a generically customisable way, especially with the dreadfully difficult 'who is a student of course x' problem we have at the moment .

        It'd be great if we could get a wide variety of use case statements for this - just something simple like:

        • A report which tells me how many instances of each resource/module there is across the whole site.
        • A report which counts the total number of assignments marked in a specific course category

        etc

        Show
        Dan Poltawski added a comment - Hmm, the more I think about this the more I think it would be surprisingly difficult to do in a generically customisable way, especially with the dreadfully difficult 'who is a student of course x' problem we have at the moment . It'd be great if we could get a wide variety of use case statements for this - just something simple like: A report which tells me how many instances of each resource/module there is across the whole site. A report which counts the total number of assignments marked in a specific course category etc
        Hide
        Matt Campbell added a comment -

        I'm going to throw some ideas of some reports I've been asked for:

        All grades for a user by course category, grouped by course. Report should only give grades the requestor has access to see.
        All grades for a user for the entire site, grouped by course. Report should only give grades the requestor has access to see.
        All quiz submissions in a course, grouped by quiz and giving user, completion time, and grade.
        Distinct user access to a course by month. Each user should only be counted once per month, no matter how many hits. Use greatest legacy role to report back access (student, non-editing teacher, teacher)
        Same as above, but by site, looking at greatest legacy role in any course. Include administrators.
        Quiz results by question category or outcome, user and course levels.
        Instances and average score for outcomes at site, category, and course levels.
        Outcomes by user across entire site, report should only give outcomes the requestor has access to see.

        Show
        Matt Campbell added a comment - I'm going to throw some ideas of some reports I've been asked for: All grades for a user by course category, grouped by course. Report should only give grades the requestor has access to see. All grades for a user for the entire site, grouped by course. Report should only give grades the requestor has access to see. All quiz submissions in a course, grouped by quiz and giving user, completion time, and grade. Distinct user access to a course by month. Each user should only be counted once per month, no matter how many hits. Use greatest legacy role to report back access (student, non-editing teacher, teacher) Same as above, but by site, looking at greatest legacy role in any course. Include administrators. Quiz results by question category or outcome, user and course levels. Instances and average score for outcomes at site, category, and course levels. Outcomes by user across entire site, report should only give outcomes the requestor has access to see.
        Hide
        Helen Foster added a comment -

        atw, thanks for your suggestion, Dan and Matt, thanks for your comments, and thanks to everyone who has voted

        This feature idea is now included in the list http://docs.moodle.org/en/Projects_for_new_developers

        Show
        Helen Foster added a comment - atw, thanks for your suggestion, Dan and Matt, thanks for your comments, and thanks to everyone who has voted This feature idea is now included in the list http://docs.moodle.org/en/Projects_for_new_developers
        Hide
        Francois Marier added a comment -

        MDL-17420 might be useful for doing grade exports over more than one course.

        Show
        Francois Marier added a comment - MDL-17420 might be useful for doing grade exports over more than one course.
        Hide
        Red Morris added a comment -

        Here's a few reports I have fudged, but would like to see properly integrated:

        Activity of all courses in a category, grouped by the top level category, i.e., school or department activity
        Activity of all courses in a category, i.e., sub-department or area activity
        Activity by hour of the day

        Of course I have a few I'm working on that I'd like too. Top of the list is:

        Course Activity compared to last year - I've got this working in a MySQL script, but I'm struggling to integrate it into Moodle

        Red

        Activity of all courses in a category, grouped by the top level category, i.e., school or department activity

        SELECT REPLACE(substring(cats.path, 2, 2),'/','') AS toplevel, cats2.name, ".$param->fields
        FROM ".$CFG->prefix."course_categories cats, ".$CFG->prefix."course mc, ".$CFG->prefix."stats_".$param->table." msd, ".$CFG->prefix."course_categories cats2"
        WHERE timeend >= ".$param->timeafter.' AND stattype = \'activity\''
        AND mc.id = msd.courseid AND mc.category = cats.id"
        AND cats2.id = REPLACE(substring(cats.path, 2, 2),'/','')"
        GROUP BY toplevel"
        ORDER BY ".$param->orderby

        Activity of all courses in a category, i.e., sub-department or area activity

        SELECT courseid,".$param->fields.", ".$CFG->prefix."course.category, ".$CFG->prefix."course_categories.name"
        FROM (".$CFG->prefix.'stats_'.$param->table." INNER JOIN ".$CFG->prefix."course"
        ON ".$CFG->prefix."stats_".$param->table.".courseid = ".$CFG->prefix."course.id)"
        INNER JOIN ".$CFG->prefix."course_categories ON ".$CFG->prefix."course_categories.id = ".$CFG->prefix."course.category"
        WHERE timeend >= ".$param->timeafter.' AND stattype = \'activity\''
        GROUP BY category "
        ORDER BY ".$param->orderby

        Activity by hour of the day

        SELECT Count('x') AS line1, DATE_FORMAT(FROM_UNIXTIME(time),'%H:00 to %H:59') AS Hour"
        FROM ".$CFG->prefix."log"
        WHERE time >= '".$param->timeafter."'"
        GROUP BY Hour

        Show
        Red Morris added a comment - Here's a few reports I have fudged, but would like to see properly integrated: Activity of all courses in a category, grouped by the top level category, i.e., school or department activity Activity of all courses in a category, i.e., sub-department or area activity Activity by hour of the day Of course I have a few I'm working on that I'd like too. Top of the list is: Course Activity compared to last year - I've got this working in a MySQL script, but I'm struggling to integrate it into Moodle Red Activity of all courses in a category, grouped by the top level category, i.e., school or department activity SELECT REPLACE(substring(cats.path, 2, 2),'/','') AS toplevel, cats2.name, ".$param->fields FROM ".$CFG->prefix."course_categories cats, ".$CFG->prefix."course mc, ".$CFG->prefix."stats_".$param->table." msd, ".$CFG->prefix."course_categories cats2" WHERE timeend >= ".$param->timeafter.' AND stattype = \'activity\'' AND mc.id = msd.courseid AND mc.category = cats.id" AND cats2.id = REPLACE(substring(cats.path, 2, 2),'/','')" GROUP BY toplevel" ORDER BY ".$param->orderby Activity of all courses in a category, i.e., sub-department or area activity SELECT courseid,".$param->fields.", ".$CFG->prefix."course.category, ".$CFG->prefix."course_categories.name" FROM (".$CFG->prefix.'stats_'.$param->table." INNER JOIN ".$CFG->prefix."course" ON ".$CFG->prefix."stats_".$param->table.".courseid = ".$CFG->prefix."course.id)" INNER JOIN ".$CFG->prefix."course_categories ON ".$CFG->prefix."course_categories.id = ".$CFG->prefix."course.category" WHERE timeend >= ".$param->timeafter.' AND stattype = \'activity\'' GROUP BY category " ORDER BY ".$param->orderby Activity by hour of the day SELECT Count('x') AS line1, DATE_FORMAT(FROM_UNIXTIME(time),'%H:00 to %H:59') AS Hour" FROM ".$CFG->prefix."log" WHERE time >= '".$param->timeafter."'" GROUP BY Hour
        Hide
        Jose Cedeno added a comment -

        Is this feature still needed?

        Show
        Jose Cedeno added a comment - Is this feature still needed?
        Hide
        Helen Foster added a comment -

        Jose, yes this feature is certainly needed! Even though this tracker issue was created fairly recently (20/Feb/09), it already has 16 votes. It's good to hear that you're interested in it

        Show
        Helen Foster added a comment - Jose, yes this feature is certainly needed! Even though this tracker issue was created fairly recently (20/Feb/09), it already has 16 votes. It's good to hear that you're interested in it
        Hide
        Jose Cedeno added a comment -

        I believe it was during last year's OSCON conference that somebody from NPR gave a talk about the NPR api that they had recently released back then. They have an online query generator. What do you guys think of it's interface?
        http://www.npr.org/api/queryGenerator.php

        NPR - national public radio is a pretty popular radio station in the United States. It is one of the few good stations still left where you get to listen to informative shows that talk about all kinds of interesting topics.

        Show
        Jose Cedeno added a comment - I believe it was during last year's OSCON conference that somebody from NPR gave a talk about the NPR api that they had recently released back then. They have an online query generator. What do you guys think of it's interface? http://www.npr.org/api/queryGenerator.php NPR - national public radio is a pretty popular radio station in the United States. It is one of the few good stations still left where you get to listen to informative shows that talk about all kinds of interesting topics.
        Hide
        Jose Cedeno added a comment -

        Here is my application http://docs.google.com/Doc?id=dgffwp9z_58ggchpff7 . Let me know what you guys think.

        Show
        Jose Cedeno added a comment - Here is my application http://docs.google.com/Doc?id=dgffwp9z_58ggchpff7 . Let me know what you guys think.
        Hide
        Ron Meske added a comment -

        Though our focus is for SCORM reporting I believe this discussion of what should be included for reporting is still relevant.
        http://moodle.org/mod/forum/discuss.php?d=119567#p526261

        I will attach a mockup of how a generic custom reporting interface could be designed that walks a user through each step of what they want to report on, how to filter it and how to sort it.

        Show
        Ron Meske added a comment - Though our focus is for SCORM reporting I believe this discussion of what should be included for reporting is still relevant. http://moodle.org/mod/forum/discuss.php?d=119567#p526261 I will attach a mockup of how a generic custom reporting interface could be designed that walks a user through each step of what they want to report on, how to filter it and how to sort it.
        Hide
        Stuart R Mealor added a comment -

        I think the Advanced Filtering options used in spreadsheet apps like MS Excel and Open Office Calc might be a good model here. They have been refined over numerous versions (so quite user-friendly), and although they are used mainly to report on 'lists' (flat file database), they can filter from Access/Base files.

        Show
        Stuart R Mealor added a comment - I think the Advanced Filtering options used in spreadsheet apps like MS Excel and Open Office Calc might be a good model here. They have been refined over numerous versions (so quite user-friendly), and although they are used mainly to report on 'lists' (flat file database), they can filter from Access/Base files.
        Hide
        Tim Hunt added a comment -
        Show
        Tim Hunt added a comment - I implemented this (in a simple way). See http://moodle.org/mod/data/view.php?d=13&rid=2884 . There is also http://moodle.org/mod/data/view.php?d=13&rid=4283
        Hide
        Luis de Vasconcelos added a comment -

        Thanks Tim. Are you referring to your 1.9 version, or have you blessed us with a 2.0 version?

        Show
        Luis de Vasconcelos added a comment - Thanks Tim. Are you referring to your 1.9 version, or have you blessed us with a 2.0 version?
        Hide
        Martin Dougiamas added a comment -

        See also the custom report builder made by MITMS (later known as Totara) for 1.9.

        http://wiki.mitms.ac.nz/
        http://wiki.mitms.ac.nz/index.php/User_Feature_Documentation/Report_Builder

        Show
        Martin Dougiamas added a comment - See also the custom report builder made by MITMS (later known as Totara) for 1.9. http://wiki.mitms.ac.nz/ http://wiki.mitms.ac.nz/index.php/User_Feature_Documentation/Report_Builder
        Hide
        Juan Leyva added a comment -

        Configurable reports looks like Totara ones and works for Moodle 1.9 and Moodle 2.0 (I'm the developer)

        http://moodle.org/mod/data/view.php?d=13&rid=4283
        http://docs.moodle.org/en/blocks/configurable_reports/

        After questionnaire, book and certificate is one of the most downloaded contrib plugins:
        http://download.moodle.org/stats.php

        Show
        Juan Leyva added a comment - Configurable reports looks like Totara ones and works for Moodle 1.9 and Moodle 2.0 (I'm the developer) http://moodle.org/mod/data/view.php?d=13&rid=4283 http://docs.moodle.org/en/blocks/configurable_reports/ After questionnaire, book and certificate is one of the most downloaded contrib plugins: http://download.moodle.org/stats.php
        Hide
        Ron Meske added a comment -

        Both reports; custom report builder and configurable reports, are a good step. My main concern is how to get data out of the SCORM activities. I have done some searches but have not found any good examples for SQL queries to extract the SCORM data. It would nice to see these fields added to drop down selections, but a SQL query would work as well.

        Show
        Ron Meske added a comment - Both reports; custom report builder and configurable reports, are a good step. My main concern is how to get data out of the SCORM activities. I have done some searches but have not found any good examples for SQL queries to extract the SCORM data. It would nice to see these fields added to drop down selections, but a SQL query would work as well.
        Hide
        Juan Leyva added a comment -

        It seems that TOTARA Report Builder is going to be integrated (2.4 maybe)

        http://tracker.moodle.org/browse/MDL-30193

        Show
        Juan Leyva added a comment - It seems that TOTARA Report Builder is going to be integrated (2.4 maybe) http://tracker.moodle.org/browse/MDL-30193

          People

          • Votes:
            44 Vote for this issue
            Watchers:
            31 Start watching this issue

            Dates

            • Created:
              Updated: