Case Study: Identifying Cohort/Audience used for Course Enrolment

Cohorts & audiences are commonly used to enrol users in courses, via the cohort/audience sync enrolment method. To identify the Cohorts/Audiences that have been used to enrol learners in courses you can use the following query to create a derived table.

To add the cohorts/audiences derived table to an existing data source you will need to follow the below steps.

1. From the Zoola landing page, select Data Sources > View Existing

2. Locate the data source you want to modify, right click and select Edit. The Edit Data Source page opens.

3.Underneath the Data Source Design heading, click the Edit with Data Source Designer link. The Data Source Designer opens to the Display tab.

4. Navigate to the Derived Tables tab.

On the Derived Tables tab you will need to add the below SQL statement that retrieves the cohort/audience enrolment information

cohort_audience_enrolments

SELECT c.id as cohortid, cm.userid as userid, e.courseid as courseid, c.name as cohort
FROM mdl_cohort c
INNER JOIN mdl_cohort_members cm ON (cm.cohortid = c.id)
INNER JOIN mdl_enrol e ON (e.customint1 = c.id)

5. In the Query ID field, enter the table name as cohort_audience_enrolments.

6. In the Query field, copy and paste in the above SQL statement.

7. Once you have added the SQL, click the Run Query button to validate the query

Once the table has been created you will need to join the cohort_audience_enrolments table into the other tables used in the data source.

8. Navigate to the Joins tab. The derived table is designed to connect to Course and User information typically found in the mdl_course and mdl_user tables. If you connect the cohort_audience_enrolments to these tables the joins will look like the below screenshot.

After the joins are put in place, the cohort & audience information should be used in any Ad Hoc Views that you build. The final step is to include the Cohort/Audience name.

9. Navigate to the Display tab.

10. In the Resources panel, select to View as: Join Tree.

11. Expand JoinTree_1, then expand the cohort_audience_enrolments table:

12. Select the cohort field and drag it into the appropriate set in the Sets and Items panel.

13. Click the OK button to save your changes in the Data Source Designer and return to the Edit Data Source page.

14. To save your changes to the Data Source, click the Submit button