Data Sources configured to track Activity-related data have, in their natural state, an "Activity Name" field. However, this field simply names the basic form of Activity ("Quiz," for example).
This case study will cover the process of adding a derived table that allows you to include the proper name of the activity ("User Management Quiz," for example).
For the purposes of this case study, we will add a Derived Table to the Activity Completion Data Source.
This Derived Table is not natively included with this Data Source, as you will require a select statement for each Activity you intend to track—but we will make this as smooth as possible.
To begin, you will need to copy the Activity Completion Data Source from the Public folder into your private organizational folder tree. To copy the Activity Completion Data Source, complete the following steps:
Now that you have an editable copy of the Activity Completion Data Source, you can edit it to add the Derived Table. BEFORE YOU OPEN THE DATA SOURCE DESIGNER, ENSURE YOU HAVE NOTED THE FOLLOWING THINGS FOR YOUR LMS ACTIVITIES
To add the Derived Table to your newly copied Activity Completion Data Source, complete the following steps:
In the Query field, you will need to enter a series of select statements based on the following formula:
select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from /*LMS TABLE*/ t join mdl_modules m on m.name = '/*NAME*/' union |
Note the two variables highlighted in this select statement:
/*LMS TABLE*/ : In place of this variable, insert the proper LMS module table name. For example, 'mdl_lesson' or 'mdl_quiz'.
/*NAME*/ : In place of this variable, insert the name of the Activity. For example, 'lesson' or 'quiz'.
To start, you can begin by entering the following sample Derived Table. You will likely find this series of select statements to miss some of your custom modules and name several that you do not use. Enter the following query into the Query field:
select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_assign t join mdl_modules m on m.name = 'assign' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_assignment t join mdl_modules m on m.name = 'assignment' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_book t join mdl_modules m on m.name = 'book' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_chat t join mdl_modules m on m.name = 'chat' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_choice t join mdl_modules m on m.name = 'choice' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_data t join mdl_modules m on m.name = 'data' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_feedback t join mdl_modules m on m.name = 'feedback' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_folder t join mdl_modules m on m.name = 'folder' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_forum t join mdl_modules m on m.name = 'forum' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_glossary t join mdl_modules m on m.name = 'glossary' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_imscp t join mdl_modules m on m.name = 'imscp' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_label t join mdl_modules m on m.name = 'label' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_lesson t join mdl_modules m on m.name = 'lesson' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_lti t join mdl_modules m on m.name = 'lti' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_page t join mdl_modules m on m.name = 'page' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_quiz t join mdl_modules m on m.name = 'quiz' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_resource t join mdl_modules m on m.name = 'resource' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_scorm t join mdl_modules m on m.name = 'scorm' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_feedback t join mdl_modules m on m.name = 'feedback' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_url t join mdl_modules m on m.name = 'url' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_wiki t join mdl_modules m on m.name = 'wiki' union select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from mdl_workshop t join mdl_modules m on m.name = 'workshop' |