Case Study: Adding Proper Activity Names to Data Sources

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:

In the Repository, navigate to Public > Moodle/Totara > Data Sources.
Right-click the Activity Completion Data Source and select Copy.
Navigate to the folder in your private organizational tree where you would like to copy the Data Source.
Right-click the folder and select Paste.

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:

Right-click the Activity Completion Data Source and select Edit. The Edit Data Source page opens.
Underneath the Data Source Design heading, click the Edit withData Source Designer link. The Data Source Designer opens to the Display tab.
Navigate to the Derived Tables tab.
In the Query ID field, enter the name "proper_act_name".
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'

Click Run Query.
If there are tables referenced in this query that do not exist in your LMS, you will receive an error similar to that shown in the following image:

If you receive this error, simply remove the offending statement from the query and run it again.
If your query runs successfully, you will need to select from the returned results. Select the entire list by holdingCommand/Shift/CTRL and selecting each field:
Click Save Table. Your new Derived Table will appear in the Available Objects panel with the unique Derived Table / Calculated Field identifier.
Navigate to the Joins tab and create the following joins:
1. 1. 1. From the Left Table, select the course field from the mdl_course_modules table, then from the Right Table select the courseid field from the proper_act_name Derived Table. Click the Left Outer Join button.
    2. From the Left Table, select the module field from the mdl_course_modules table, then from the Right Table select the moduleid field from the proper_act_name Derived Table. Click the Left Outer Join button.
    3. A modal window appears, notifying you that the tables have already been joined, and that a composite key will be created. Click the OK button to complete your second join:
    4. From the Left Table, select the instance field from the mdl_course_modules table, then from the Right Table select the instanceid field from the proper_act_name Derived Table. Click the Left Outer Join button.
    5. Click the OK button in the modal window that appears.
Navigate to the Display tab.
In the Resources panel, select to View as: Join Tree.
Expand JoinTree_1, then expand the table:
Select the instancename field and drag it into the Activity Completions set in the Sets and Items panel.
In the Sets and Items panel, select the newly added instancename item.
In the Properties panel, click the Edit button. The property fields of the instancename item become editable.
In the Label field, rename the instance item to "Proper Activity Name", for presentation purposes.
Click the Save button to save your changes to the Proper Activity Name item.
Click the OK button to save your changes in the Data Source Designer and return to the Edit Data Source page.
Under the Required Information heading, rename the Activity Completions Data Source in the Name field, in order to avoid confusing it with the Public Activity Completions Data Source. We will call this Data Source the "Activity Completions Data Source - Proper Names".
To save your changes to the Data Source, click the Submit button.

Page tree

Case Study: Adding Proper Activity Names to Data Sources