SQL Templates

SQL Templates

Note that assistance with writing SQL queries is not within Pluto LMS support scope. Please contact a developer for assistance.
 
That being said, you can use the templates provided below to help you get started. Please review our other guides for more help. 

User Course Completion Progress

SELECT

u.firstname AS 'First Name',
u.lastname AS 'Last Name',
c.fullname AS 'Course Name',
ROUND(e.progress,0) AS 'Progress',
DATE_FORMAT(FROM_UNIXTIME(e.completiontime),'%d-%m-%Y') AS 'Complete Date'

FROM prefix_edwreports_course_progress AS e
JOIN prefix_user AS u ON e.userid = u.id 
JOIN prefix_course AS c ON e.courseid = c.id 

User Segment Assignments

SELECT

u.firstname AS 'First Name',
u.lastname AS 'Last Name',
b.name AS 'Branch'

FROM prefix_cohort_members AS bm 
JOIN prefix_cohort AS b ON bm.cohortid = b.id
JOIN prefix_user AS u ON bm.userid = u.id 

User Course Grades


SELECT

u.firstname AS 'First Name',
u.lastname AS 'Last Name',
ROUND(gg.finalgrade,2) AS 'Grade'

FROM prefix_grade_grades AS gg  
JOIN prefix_grade_items AS gi ON gg.itemid = gi.id
JOIN prefix_user AS u ON gg.userid = u.id
WHERE gi.itemtype = 'course'

Cohort = User Segment/Branch




    • Related Articles

    • SQL Query Basics

      Below is an example of a very basic SQL query: SELECT u.id AS 'id', u.username AS 'username', u.firstname AS 'first name', u.lastname AS 'last name', u.email AS 'email' FROM prefix_user AS u WHERE u.id > 2 The SELECT statement is used to select data ...

    • Adding Filters

      OVERVIEW When creating a custom report, you can add filters that function as a control which produces a subset of data. ADDING A FILTER In the relevant custom report, in the "Filters" tab, select the filter that you require from the "Add" drop-down ...

    • Creating a Custom Report

      OVERVIEW The Custom Reports module allows you to create custom reports to supplement the native Pluto LMS reports. Note: This may require the assistance of a developer. USE CASE EXAMPLE: Improving Onboarding Training Company: XYZ Corporation ...

    • Create a Certificate

      OVERVIEW The Certificate activity allows you to create PDF certificates for learners in a course. This feature allows for official and targeted recognition for completing courses. Use Case Example: Imagine the course you have implemented is on ...

    • Using the Data Table

      Overview Data Tables can be used to create structured tables that contain data in column and row format. Data Tables allow you to define column headings in preparation for a data import. A Data Table has the following structure: Fields = Columns ...