FiLMS Custom Reports catalog

FiLMS Custom Reports are SQL queries that output as a CSV for FiLMS clients.

List of common Custom Reports

Admin: Course education hierarchy review

/* mjohnson 2013-07-18 -------------------------------------- Review of course, structure, curriculum, class, enrollment count */ SELECT c.id AS c_id , c.name AS 'c name' , c.is_active AS 'c active' , s.id AS 's id' , s.name AS 's name' , s.is_active AS 's active' , cu.id AS 'cu id' , cu.name AS 'cu name' , cu.is_visible AS 'cu visible' , fc.id AS 'cl id' , fc.name AS 'cl name' , fc.is_active AS 'cl active' , ( SELECT COUNT(*) FROM enrollments AS e WHERE e.curriculum_id = cu.id AND e.is_active = 1 ) AS 'e cnt' , ( SELECT COUNT( * ) FROM enrollments AS e WHERE e.curriculum_id = cu.id AND e.is_complete =1 ) AS 'c cnt' FROM courses AS c LEFT JOIN structures AS s ON c.id = s.course_id LEFT JOIN curriculums AS cu ON s.id = cu.structure_id LEFT JOIN films_classes AS fc ON cu.films_class_id = fc.id ORDER BY c.id, s.id, cu.id, fc.id

/* For Shared DB */ SELECT c.id AS course_id , c.name AS course_name , c.is_active AS course_active , s.id AS str_id , s.name AS str_name , s.is_active AS str_active , cu.id AS curr_id , cu.name AS curr_name , cu.is_visible AS curr_visible , fc.id AS class_id , fc.name AS class_name , fc.is_active AS class_active , ( SELECT COUNT(*) FROM enrollments AS e WHERE e.curriculum_id = cu.id AND e.is_active = 1 ) AS e_cnt FROM courses AS c LEFT JOIN structures AS s ON c.id = s.course_id LEFT JOIN curriculums AS cu ON s.id = cu.structure_id LEFT JOIN films_classes AS fc ON cu.films_class_id = fc.id WHERE c.id IN ( SELECT course_id FROM organization_course_catalogs WHERE organization_id = 34 AND is_enabled = 1 ) ORDER BY c.id, s.id, cu.id, fc.id

Admin: Course structure hierarchy review

/* mjohnson 2013-10-25 ----------------------------- Dump of Course structure hierarchies listed in structure section & structure object order */ SELECT c.id AS 'Course ID' , c.name AS 'Course Name' , s.id AS 'Structure ID' , s.name AS 'Structure Name' , ss.id AS 'Section ID' , ss.name AS 'Section Name' , ss.order_number AS 'Section Order' , so.id AS 'SO ID' , so.filename AS 'SO Filename' , so.order_number AS 'SO Order' , so.is_required AS 'SO Required' , lo.id AS 'LO ID' , lo.name AS 'LO Name' , lo.description AS 'LO Desc' FROM structures AS s LEFT JOIN structure_sections AS ss ON s.id = ss.structure_id LEFT JOIN structure_objects AS so ON ss.id = so.structure_section_id LEFT JOIN learning_objects AS lo ON lo.id = so.learning_object_id LEFT JOIN courses AS c ON c.id = s.course_id ORDER BY c.id, s.id, ss.order_number, so.order_number

Admin: Topic course review

/* mjohnson 2013-08-30 ----------------------------- Dump of instance topics and their course and org assns */ SELECT t.id AS topic_id , t.name AS topic_name , c.id AS course_id , c.name AS course_name , o.name AS organization_name FROM topics AS t LEFT JOIN course_topic_assn AS cta ON cta.topic_id = t.id LEFT JOIN courses AS c ON c.id = cta.course_id LEFT JOIN organization_course_catalogs AS occ ON occ.course_id = c.id LEFT JOIN organizations AS o ON o.id = occ.organization_id ORDER BY t.name, c.name, o.name

Admin: Role review

/* mjohnson 2013-08-27 -------------------------------- A dump of enabled Security Functions used by FiLMS roles */ SELECT r.id , r.name AS Role_name , lt.name AS level_type , sf.name AS Security_function FROM roles AS r LEFT JOIN level_types AS lt ON lt.id = r.level_type LEFT JOIN role_security_function_assn AS rsfa ON rsfa.role_id = r.id LEFT JOIN security_functions AS sf ON rsfa.security_function_id = sf.id ORDER BY r.id, sf.name

Admin: Auto-enrollment review

/* mjohnson 2013-09-16 -------------------------------------- List of active auto-enrollments */ SELECT g.id AS GroupId , g.name AS GroupName , cu.id AS CurriclumId , cu.name AS CurriculumName FROM fm_autoenroll_assn AS aa LEFT JOIN groups AS g ON aa.group_id = g.id LEFT JOIN curriculums AS cu ON cu.id = aa.curriculum_id LEFT JOIN structures AS s ON s.id = cu.structure_id LEFT JOIN courses AS c ON c.id = s.course_id WHERE c.is_active = 1 AND s.is_active = 1 AND cu.is_visible = 1 ORDER BY 1, 2

With orgs:

SELECT o.id AS OrgId , o.name AS OrgName , g.id AS GroupId , g.name AS GroupName , cu.id AS CurriclumId , cu.name AS CurriculumName FROM fm_autoenroll_assn AS aa LEFT JOIN groups AS g ON aa.group_id = g.id LEFT JOIN organizations AS o ON o.id = g.organization_id LEFT JOIN curriculums AS cu ON cu.id = aa.curriculum_id LEFT JOIN structures AS s ON s.id = cu.structure_id LEFT JOIN courses AS c ON c.id = s.course_id WHERE c.is_active = 1 AND s.is_active = 1 AND cu.is_visible = 1 ORDER BY 2,4,6

Admin: Mandatory Group Structure review

/* mjohnson 2016-04-29 ----------------------------- List of is_required Group structures */ SELECT g.name , s.name FROM `group_structure_catalogs` AS gsc LEFT JOIN groups AS g ON g.id = gsc.group_id LEFT JOIN structures AS s ON s.id = gsc.structure_id WHERE is_required = 1 ORDER BY 1 , 2

Admin: Role permission comparison

/* mjohnson 2016-05-18 ~~~~~~~~~~~~~~~~~~~ */ SELECT e.sf_name AS 'Security Function' , COALESCE(SUM(r1), 0) AS 'System Admin' , COALESCE(SUM(r6), 0) AS 'Limited System Admin' , COALESCE(SUM(r2), 0) AS 'Organization Admin' , COALESCE(SUM(r3), 0) AS 'Group Admin' , COALESCE(SUM(r7), 0) AS 'Reporter Only' , COALESCE(SUM(r4), 0) AS 'Instructor' , COALESCE(SUM(r5), 0) AS 'Learner' FROM (SELECT r.id AS role_id , r.name AS role_name , sf.name AS sf_name , CASE WHEN role_id = 1 THEN 1 END AS 'r1' , CASE WHEN role_id = 2 THEN 1 END AS 'r2' , CASE WHEN role_id = 3 THEN 1 END AS 'r3' , CASE WHEN role_id = 4 THEN 1 END AS 'r4' , CASE WHEN role_id = 5 THEN 1 END AS 'r5' , CASE WHEN role_id = 6 THEN 1 END AS 'r6' , CASE WHEN role_id = 7 THEN 1 END AS 'r7' FROM `role_security_function_assn` AS rsfa LEFT JOIN security_functions AS sf ON sf.id = rsfa.security_function_id LEFT JOIN roles AS r ON r.id = rsfa.role_id ) AS e GROUP BY sf_name

Admin: Single quiz response dump

/* mjohnson 2016-11-29 ~~~~~~~~~~~~~~~~~~~ Report will request Curriculum ID. Output to be responses by users who have taken course */ SELECT u.login , u.name AS display_name , lo.id AS LoId , lo.name AS LoName , lo.description AS LessonDesc , q.text AS QuestionText , ch.text AS ChoiceText , qr.answered_at AS question_answered_at FROM enrollments AS e LEFT JOIN users AS u ON e.user_id = u.id LEFT JOIN results AS r ON e.id = r.enrollment_id LEFT JOIN learning_objects AS lo ON lo.id = r.learning_object_id LEFT JOIN attempts AS a ON a.result_id = r.id LEFT JOIN question_bank_results AS qbr ON qbr.attempt_id = a.id LEFT JOIN question_results AS qr ON qr.question_bank_result_id = qbr.id LEFT JOIN questions AS q ON qr.question_id = q.id LEFT JOIN choice_question_result_assn AS cqra ON cqra.question_result_id = qr.id LEFT JOIN choices AS ch ON ch.id = cqra.choice_id WHERE e.curriculum_id = {CurriculumId:Number} AND ch.question_id IS NOT NULL ORDER BY u.login, qr.id

FiLMS Review Custom Reports

These can be run and provided to Sales to review a FiLMS client's usage. Merge all 4 as tabs then tack on Bandwidth usage.

Admin: FiLMS Review - Overview

/* mjohnson 2017-10-19 ~~~~~~~~~~~~~~~~~~~ FiLMS Review package */ SELECT (SELECT name FROM `organizations` ORDER BY id LIMIT 1) AS Client ,(SELECT value FROM `application_info` WHERE setting = 'FILMS_DB_DATE_CREATED') AS Created ,(SELECT COUNT(*) FROM organizations) AS 'Organization count' ,(SELECT COUNT(*) FROM users WHERE is_active=1) AS 'User count' ,(SELECT COUNT(*) FROM courses WHERE is_active=1) AS 'Course count' ,(SELECT COUNT(*) FROM enrollments WHERE is_active=1) AS 'Enrollment count' ,(SELECT MAX(completed_at) FROM results) AS 'Latest activity' FROM languages LIMIT 1

Admin: FiLMS Review - Organizations

/* mjohnson 2017-10-19 ~~~~~~~~~~~~~~~~~~~ FiLMS Review package */ SELECT o.name AS Organization , COUNT(u.id) AS 'User count' ,(SELECT COUNT(*) FROM organization_course_catalogs WHERE organization_id = o.id) AS 'Course count' FROM users AS u LEFT JOIN organizations AS o ON o.id = u.organization_id WHERE u.is_active = 1 GROUP BY 1

Admin: FiLMS Review - Groups

/* mjohnson 2017-10-19 ~~~~~~~~~~~~~~~~~~~ FiLMS Review package */ SELECT o.name AS Organization , g.name AS 'Group' , (SELECT COUNT(*) FROM group_member_assn AS gma LEFT JOIN users AS u ON u.id = gma.user_id WHERE group_id = g.id AND u.is_active = 1) AS Members , (SELECT COUNT(*) FROM group_structure_catalogs AS gsc LEFT JOIN structures AS s ON s.id = gsc.structure_id LEFT JOIN courses AS c ON c.id = s.course_id WHERE group_id = 1 AND is_required = 1 AND s.is_active = 1 AND c.is_active = 1) AS 'Mandatory course structures' FROM groups AS g LEFT JOIN organizations AS o ON o.id = g.organization_id ORDER BY 1, 2

Admin: FiLMS Review - Courses

/* mjohnson 2017-10-19 ~~~~~~~~~~~~~~~~~~~ FiLMS Review package */ SELECT c.name AS 'Course' , COUNT(*) AS 'Enrollments' , SUM(e.is_complete) AS 'Completions' FROM enrollments AS e LEFT JOIN curriculums AS cu ON cu.id = e.curriculum_id LEFT JOIN structures AS s ON s.id = cu.structure_id LEFT JOIN courses AS c ON c.id = s.course_id WHERE e.is_active = 1 AND s.is_active = 1 AND c.is_active = 1 GROUP BY 1

Search Custom Reports

A stored procedure named pQueryCustomReports allows us to search all FiLMS/eco instances on AWS.

Use:

• Edit sSearchTerm
• Save
• Run routine(s)