Filtering to Active Enrollments Only

Problem

The usercourses table records every relationship a learner has ever had with a course — including enrollments that have since been archived, transferred, or deleted. Reports that don't filter these out will silently:

• **Under-count completion rates** (deleted enrollments still contribute to the denominator).
• **Over-count "learners with access"** (archived enrollments no longer grant access).

The platform exposes a single source of truth for record state called **Status Group**. Every BI Connector table that supports it has a statusgroup column whose values are:

Solution

-- All currently active enrollments
SELECT *
FROM usercourses
WHERE statusgroup = 'a';

-- Correct completion rate on active enrollments only
SELECT
  COUNT(*)                                              AS active_enrollments,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
  ROUND(
    100.0 * SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END)
          / NULLIF(COUNT(*), 0),
    2
  )                                                     AS completion_rate_pct
FROM usercourses
WHERE statusgroup = 'a';

How it works

• statusgroup = 'a' is the platform's documented "this record is live" filter. It rolls up table-specific status conventions into a single value.
• The completion-rate variant pairs statusgroup (record liveness) with the table-specific status column (workflow state). Both filters are needed for an accurate rate.

Variations

• **Just active and completed:** WHERE statusgroup = 'a' AND status = 'completed'.
• **Include archived for cohort comparisons:** WHERE statusgroup IN ('a', 'i') — useful when a learner moved orgs and you still want their historical activity.
• **Per-Panorama scoping:** add WHERE companyid = '<your-tenant-id>' to scope to a single instance (BI Connector data is multi-tenant by default).