Brightspace quiz report across courses with Brightspace Data Sets

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.

Load Quiz Objects, Quiz Attempts, User Enrollments, Role Details and Organizational Units.

Run the quiz report query

Edit the role names on line 5. The query removes duplicate source rows before counting, and matches attempts to learners enrolled in that same course.

-- Edit the role names on the RoleName IN line to choose who counts as a learner.
WITH learner_roles AS (
    SELECT DISTINCT RoleId
    FROM RoleDetails
    WHERE RoleName IN ('Student')
), Courses AS (
    SELECT DISTINCT OrgUnitId, Name
    FROM OrganizationalUnits WHERE Type = 'Course Offering'
), Enrollments AS (
    SELECT DISTINCT UserId, OrgUnitId, RoleId FROM UserEnrollments
), Learners AS (
    SELECT DISTINCT E.UserId, E.OrgUnitId
    FROM Enrollments E
    JOIN learner_roles ON learner_roles.RoleId = E.RoleId
    JOIN Courses ON Courses.OrgUnitId = E.OrgUnitId
), CourseLearners AS (
    SELECT OrgUnitId, COUNT(*) AS Learners FROM Learners GROUP BY OrgUnitId
), Quizzes AS (
    SELECT DISTINCT QuizId, QuizName, OrgUnitId FROM QuizObjects
), Attempts AS (
    SELECT DISTINCT AttemptId, QuizId, UserId, OrgUnitId, TimeCompleted, Score, PossibleScore
    FROM QuizAttempts
    WHERE COALESCE(TRY_CAST(IsDeleted AS BOOLEAN), false) = false
      AND TimeCompleted IS NOT NULL
), Finished AS (
    SELECT A.* FROM Attempts A
    JOIN Learners ON Learners.UserId = A.UserId AND Learners.OrgUnitId = A.OrgUnitId
), Totals AS (
    SELECT QuizId, OrgUnitId, COUNT(DISTINCT UserId) AS LearnersFinished,
           COUNT(*) AS FinishedAttempts,
           ROUND(AVG(CASE WHEN PossibleScore > 0 THEN Score * 100.0 / PossibleScore END), 1) AS AverageScorePercent
    FROM Finished GROUP BY QuizId, OrgUnitId
)
SELECT Q.QuizId, Q.QuizName, Q.OrgUnitId, Courses.Name AS CourseName,
       COALESCE(CourseLearners.Learners, 0) AS Learners,
       COALESCE(Totals.LearnersFinished, 0) AS LearnersFinished,
       COALESCE(Totals.FinishedAttempts, 0) AS FinishedAttempts, Totals.AverageScorePercent
FROM Quizzes Q
JOIN Courses ON Courses.OrgUnitId = Q.OrgUnitId
LEFT JOIN CourseLearners ON CourseLearners.OrgUnitId = Q.OrgUnitId
LEFT JOIN Totals ON Totals.QuizId = Q.QuizId AND Totals.OrgUnitId = Q.OrgUnitId
ORDER BY Q.OrgUnitId, Q.QuizId;

The query counts people whose role is named Student in Role Details. To count other roles, edit the RoleName IN list near the top, for example ('Student', 'Learner'). Names must match exactly. Joins use RoleId, so two roles with the same name both count, and a renamed role follows its current name.

To narrow attempts to one term's dates, append AND CAST(TimeCompleted AS DATE) BETWEEN DATE '2025-09-01' AND DATE '2025-12-31' to line 24, the line containing AND TimeCompleted IS NOT NULL. Both days are included in UTC. This limits attempts, not the course enrollment count.

Read the results per quiz

Fictional sample data: all 6 rows.

Learners counts enrolled learners in the course; LearnersFinished counts those with a finished attempt. FinishedAttempts includes repeats, so one learner can add several attempts. Unfinished and deleted attempts are left out. The average uses finished attempts with a possible score above zero, not each learner's best score or the grade released to the gradebook. A null average means none had a usable score.

Quizzes without attempts still appear. The two Quiz 1 rows in course 100 stay separate because the query groups by QuizId. The same quiz and course names elsewhere also stay separate through their IDs. Use those IDs when following up on an unusually low average.