Brightspace course enrollment report: every course in a term

Choose where to run it

• In Rowseta: open Reports > New report and paste the query into the SQL editor on the Query tab. Sync the data sets listed below first.
• In your own setup: load your Data Hub extracts into your own SQL tool, one table per data set, named without spaces (User Enrollments becomes UserEnrollments), after applying differentials to the fulls. Another SQL tool may need small changes to these queries.

A free Rowseta Desktop app is coming soon.

This query needs Organizational Units, Organizational Unit Ancestors, User Enrollments and Role Details. The reference links explain the columns and keys in each data set.

Run the course enrollment report

First list the semesters and their IDs:

-- Lists each semester with its ID. Two semesters can share a name, so use the ID.
SELECT OrgUnitId AS TermId, Name AS TermName, Code, StartDate, EndDate
FROM OrganizationalUnits
WHERE Type = 'Semester'
  AND COALESCE(TRY_CAST(IsDeleted AS BOOLEAN), false) = false
ORDER BY StartDate DESC, OrgUnitId;

In the main query below, replace only the number on line 4, the line containing WHERE OrgUnitId = 10, with the TermId you want to count.

Run the query after making that change. The course list follows Organizational Unit Ancestors, which connects a course to the semester above it. This allows the query to find a semester even when another organizational unit sits between the course and its term. It selects units whose type is Course Offering, rather than counting every organizational unit below the semester.

-- Change 10 below to the TermId of the semester you want to count.
WITH SelectedTerm AS (
    SELECT OrgUnitId AS TermId FROM OrganizationalUnits
    WHERE OrgUnitId = 10
      AND COALESCE(TRY_CAST(IsDeleted AS BOOLEAN), false) = false
), learner_roles AS (
    SELECT DISTINCT RoleId
    FROM RoleDetails
    WHERE RoleId IS NOT NULL
      AND NULLIF(trim(CAST(DeletedBy AS VARCHAR)), '') IS NULL
      AND COALESCE(lower(trim(CAST(ShowInGrades AS VARCHAR))) IN ('true', '1'), false)
), Learners AS (
    SELECT DISTINCT UserEnrollments.OrgUnitId, UserEnrollments.UserId
    FROM UserEnrollments
    JOIN learner_roles ON learner_roles.RoleId = UserEnrollments.RoleId
), CourseCounts AS (
    SELECT Courses.OrgUnitId, COUNT(DISTINCT Learners.UserId) AS LearnerCount
    FROM OrganizationalUnits AS Courses
    LEFT JOIN Learners ON Learners.OrgUnitId = Courses.OrgUnitId
    WHERE Courses.Type = 'Course Offering'
      AND COALESCE(TRY_CAST(Courses.IsDeleted AS BOOLEAN), false) = false
      AND EXISTS (
          SELECT 1 FROM OrganizationalUnitAncestors AS Ancestors
          JOIN SelectedTerm ON SelectedTerm.TermId = Ancestors.AncestorOrgUnitId
          WHERE Ancestors.OrgUnitId = Courses.OrgUnitId
            AND Ancestors.DateDeleted IS NULL
      )
    GROUP BY Courses.OrgUnitId
)
SELECT CourseCounts.OrgUnitId AS CourseId, Courses.Name AS CourseName, CourseCounts.LearnerCount
FROM CourseCounts
JOIN OrganizationalUnits AS Courses ON Courses.OrgUnitId = CourseCounts.OrgUnitId
ORDER BY CourseId;

A role counts as a learner role when Role Details has ShowInGrades true and DeletedBy empty. Each RoleId is judged on its own and role names are never read. Preview and testing roles that show in grades pass this rule too, so to use your own roles, replace the body of learner_roles with your role IDs: SELECT UNNEST([103, 121]) AS RoleId.

The enrollment count includes roles passing this rule. It counts each user only once within a course, even if that user's enrollment appears more than once in the rows being counted. Roles that do not pass the rule are outside this count. This is a learner enrollment total, rather than a count of everyone with access to the course.

Courses remain in the result when their learner count is zero. That can help you separate an empty offering from a course that the selected term did not include. If the whole result is empty, first check that the TermId matches your data. The example TermId is fictional and will not select your institution's term unless you replace it.

Read the students per course counts

Fictional sample data: all five rows. Your own data will give different names, IDs and values.

LearnerCount is the number of distinct matching learners in each course. Two sample courses share a name. CourseId helps distinguish courses with similar names, and a count of zero would still have its own course row.