Adding visual representations to plain quantitative SQL reports

Adding visual representations to plain quantitative SQL reports

by Nadav Kavalerchik -
Number of replies: 8
Picture of Core developers Picture of Plugin developers Picture of Testers Picture of Translators

Dear fellow reporters,

I would like to share two new visual hacks I added recently to some useful reports we are using for some time now (originally developed and shared by Elizabeth Dalton's on the ad-hoc SQL reports wiki) 

(1) Student activity over the course weeks:

Display a resizable Font Awesome Icon to represent the quantitative hits of student's activity.

student activity over the course weeks

And the SQL code:

(The magic is mark in bold)

SELECT 

u.lastname AS 'Last Name'

, u.firstname AS 'First Name'

,  COUNT(l.id) AS 'Edits' 

, COUNT(DISTINCT IF((l.timecreated-c.startdate)<0,l.id,NULL)) AS 'Before Term'

 

 # 60*60*24*7 = 604800

#,FLOOR((l.timecreated - c.startdate)/604800) as 'timediff'

#,DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%Y-%m-%d') AS 'l_timecreated'

#,DATE_FORMAT(FROM_UNIXTIME(c.startdate),'%Y-%m-%d') AS 'c_startdate'


#, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=0,l.id,NULL)) AS 'Week 1'

,CONCAT('<i class="fa fa-user" style="font-size: ', LOG(1 + COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=0,l.id,NULL))) ,'em;"></i>') AS 'Week 1'

#, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=1,l.id,NULL)) AS 'Week 2'

,CONCAT('<i class="fa fa-user" style="font-size: ', LOG(1 + COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=1,l.id,NULL))) ,'em;"></i>') AS 'Week 2'

#, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=2,l.id,NULL)) AS 'Week 3'

,CONCAT('<i class="fa fa-user" style="font-size: ', LOG(1 + COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=2,l.id,NULL))) ,'em;"></i>') AS 'Week 3'

#, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=3,l.id,NULL)) AS 'Week 4'

,CONCAT('<i class="fa fa-user" style="font-size: ', LOG(1 + COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=3,l.id,NULL))) ,'em;"></i>') AS 'Week 4'

#, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=4,l.id,NULL)) AS 'Week 5'

,CONCAT('<i class="fa fa-user" style="font-size: ', LOG(1 + COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=4,l.id,NULL))) ,'em;"></i>') AS 'Week 5'

#, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=5,l.id,NULL)) AS 'Week 6'

,CONCAT('<i class="fa fa-user" style="font-size: ', LOG(1 + COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=5,l.id,NULL))) ,'em;"></i>') AS 'Week 6'

#, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=6,l.id,NULL)) AS 'Week 7'

,CONCAT('<i class="fa fa-user" style="font-size: ', LOG(1 + COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=6,l.id,NULL))) ,'em;"></i>') AS 'Week 7'

#, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=7,l.id,NULL)) AS 'Week 8'

,CONCAT('<i class="fa fa-user" style="font-size: ', LOG(1 + COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=7,l.id,NULL))) ,'em;"></i>') AS 'Week 8'

#, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=8,l.id,NULL)) AS 'Week 9'

,CONCAT('<i class="fa fa-user" style="font-size: ', LOG(1 + COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=8,l.id,NULL))) ,'em;"></i>') AS 'Week 9'

#, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=9,l.id,NULL)) AS 'Week 10'

,CONCAT('<i class="fa fa-user" style="font-size: ', LOG(1 + COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=9,l.id,NULL))) ,'em;"></i>') AS 'Week 10'

#, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=10,l.id,NULL)) AS 'Week 11'

,CONCAT('<i class="fa fa-user" style="font-size: ', LOG(1 + COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=10,l.id,NULL))) ,'em;"></i>') AS 'Week 11'

#, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=11,l.id,NULL)) AS 'Week 12'

,CONCAT('<i class="fa fa-user" style="font-size: ', LOG(1 + COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=11,l.id,NULL))) ,'em;"></i>') AS 'Week 12'

 

, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))>=12,l.id,NULL)) AS 'After Term'

 

# Our institution stores academic advisor names AND emails IN custom profile FIELDS

#, CONCAT('<a href="mailto:',uce.DATA,'">',uid.DATA, '</a>')  AS 'Academic Advisor'

 

, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/log/index.php',CHAR(63),'chooselog=1&showusers=1&showcourses=0&id=',c.id,'&user=',u.id,'&date=0&modid=&modaction=&logformat=showashtml','">','Logs','</a>') AS 'Logs'

 , CONCAT('<a target="_blank" href="%%WWWROOT%%/report/outline/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'&mode=outline">','Outline','</a>') AS 'Activity Outline'

 , CONCAT('<a target="_blank" href="%%WWWROOT%%/report/outline/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'&mode=complete">','Activity','</a>') AS 'Consolidated Activity'

 #, CONCAT('<a target="_blank" href="%%WWWROOT%%/mod/forum/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'">','Posts','</a>') AS 'Posts'

 

FROM prefix_user AS u

JOIN prefix_role_assignments AS ra ON u.id = ra.userid

JOIN prefix_context AS ctx ON ra.contextid = ctx.id

JOIN prefix_course AS c ON c.id = ctx.instanceid

JOIN prefix_course_categories AS cc ON c.category = cc.id

 

# student academic coach - you can include custom profile FIELD DATA WITH these methods

# LEFT JOIN prefix_user_info_data AS uid ON u.id = uid.userid AND uid.fieldid = '2'

# student academic coach email

# LEFT JOIN prefix_user_info_data AS uce ON u.id = uce.userid AND uce.fieldid = '6'

 

LEFT JOIN prefix_logstore_standard_log AS l ON l.userid = u.id AND l.courseid = c.id  AND l.crud IN ('c','u')

 

WHERE ra.roleid =5

AND ctx.instanceid = c.id

 AND c.id = %%COURSEID%%

 GROUP BY u.idnumber

 ORDER BY u.lastname, u.firstname


(2) Student's assignment's completion status and grades (in current course, and other courses too)

Use Bootstrap progress (simple & colorful) classes to represent course grade (percentage) and also HTML5 progress tag, in case you are not using a bootstrap based theme.

Student assignment completion progress

And the SQL magic for displaying various progress bars is marked in bold:


SELECT 

#course.fullname as CourseName, ra.userid as CodeStudent

,(SELECT CONCAT('<a target="_new" href="%%WWWROOT%%/report/outline/user.php?id=', 

  u.id, '&course=', course.id, '&mode=complete">', u.firstname, ' ', u.lastname, '</a>') 

    FROM mdl_user AS u WHERE u.id = ra.userid) AS "סטודנט"

  

,(SELECT count(*)

from mdl_assign where course=course.id ) as "מטלות - בקורס זה"


,(SELECT count(*) 

from mdl_assign_submission as mas

join mdl_assign as ma on ma.id=mas.assignment

where ra.userid=mas.userid and ma.course=course.id) as "הגשות - בקורס זה"


,(SELECT count(*) 

from mdl_assign_submission as mas

join mdl_assign as ma on ma.id=mas.assignment

where ra.userid=mas.userid) as "הגשות - בכל הקורסים"


,(SELECT count(*) from mdl_assign where course=course.id and id not in

(SELECT mas.assignment

from mdl_assign_submission as mas

join mdl_assign as ma on ma.id=mas.assignment

where  ra.userid=mas.userid and ma.course=course.id)) as "טרם הוגש - בקורס זה"


#,(SELECT count(*) from mdl_assign where course in (select con.instanceid

#from mdl_role_assignments as mra join 

#mdl_context as con on mra.contextid=con.id and con.contextlevel=50

#where roleid=5 and userid= ra.userid)

#and id not in

#(select mas.assignment

#from mdl_assign_submission as mas

#join mdl_assign as ma on ma.id=mas.assignment

#where  ra.userid=mas.userid and ma.course=course.id)) as "טרם הוגש - בכל הקורסים"


,(SELECT COUNT( * ) FROM mdl_assign_grades AS ag

JOIN mdl_assign AS a ON a.id = ag.assignment

WHERE ag.userid = ra.userid and a.course= course.id

and ag.grade not like 'NULL') AS "מטלות בהן התקבל ציון - בקורס זה"


#,(SELECT COUNT( * ) FROM mdl_assign_grades AS ag

#JOIN mdl_assign AS a ON a.id = ag.assignment

#WHERE ag.userid = ra.userid and 

# ag.grade not like 'NULL') AS "Number of assign got grade In All Courses"


,CONCAT(

  '<progress max="100" value="'

,(SELECT ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2)

  FROM mdl_grade_grades AS gg 

  JOIN mdl_grade_items AS gi ON gi.id = gg.itemid 

  WHERE gg.userid = ra.userid AND gi.itemtype = 'Course'

  ) 

  ,'">50%</progress>') AS "Course grade in percentage"

  

  ,CONCAT('<div class="progress">

  <div class="bar" style="width: ',

 (SELECT ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2)

  FROM mdl_grade_grades AS gg 

  JOIN mdl_grade_items AS gi ON gi.id = gg.itemid 

  WHERE gg.userid = ra.userid AND gi.itemtype = 'Course')

  ,'%;"></div></div>') AS "Course grade in percentage"


,CASE

WHEN 

(SELECT ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2)

 FROM mdl_grade_grades AS gg 

 JOIN mdl_grade_items AS gi ON gi.id = gg.itemid 

 WHERE gg.userid = ra.userid AND gi.itemtype = 'Course') < 60 

 THEN

CONCAT('<div class="progress">

  <div class="bar bar-danger" style="width: ',

(SELECT ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2)

 FROM mdl_grade_grades AS gg 

 JOIN mdl_grade_items AS gi ON gi.id = gg.itemid 

 WHERE gg.userid = ra.userid AND gi.itemtype = 'Course')   

  ,'%;"></div>

</div>')


WHEN 

(SELECT ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2)

 FROM mdl_grade_grades AS gg 

 JOIN mdl_grade_items AS gi ON gi.id = gg.itemid 

 WHERE gg.userid = ra.userid AND gi.itemtype = 'Course') >= 60 

 THEN

CONCAT('<div class="progress">

  <div class="bar bar-warning" style="width: ',

(SELECT ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2)

 FROM mdl_grade_grades AS gg 

 JOIN mdl_grade_items AS gi ON gi.id = gg.itemid 

 WHERE gg.userid = ra.userid AND gi.itemtype = 'Course')   

  ,'%;"></div>

</div>')


WHEN (SELECT ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2)

 FROM mdl_grade_grades AS gg 

 JOIN mdl_grade_items AS gi ON gi.id = gg.itemid 

 WHERE gg.userid = ra.userid AND gi.itemtype = 'Course') >= 85 

 THEN

CONCAT('<div class="progress">

  <div class="bar bar-success" style="width: ',

(SELECT ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2)

 FROM mdl_grade_grades AS gg 

 JOIN mdl_grade_items AS gi ON gi.id = gg.itemid 

 WHERE gg.userid = ra.userid AND gi.itemtype = 'Course')   

  ,'%;"></div>

</div>')


END AS "colorful percentage"


#<div class="progress">

#  <div class="bar bar-success" style="width: 35%;"></div>

#  <div class="bar bar-warning" style="width: 20%;"></div>

#  <div class="bar bar-danger" style="width: 10%;"></div>

#</div>


FROM mdl_role_assignments AS ra

JOIN mdl_context AS context ON ra.contextid = context.id

AND context.contextlevel =50

JOIN mdl_course AS course ON course.id = context.instanceid

WHERE ra.roleid=5 

AND course.id='%%COURSEID%%'

#and ra.userid=339 



----


Hope you like it and it get you into creating some more inspiring visual representations for all those number-full hard to read reports.

Nadav

Average of ratings: Useful (2)
In reply to Nadav Kavalerchik

Re: Adding visual representations to plain quantitative SQL reports

by Randy Thornton -
Picture of Documentation writers

Nadav,

Some excellent magic there!

I hadn't thought of using the font awesome icons in reports, though I use them elsewhere in courses. What an inspired idea.

Thanks for sharing this.

Randy

Average of ratings: -
In reply to Randy Thornton

Re: Adding visual representations to plain quantitative SQL reports

by Nadav Kavalerchik -
Picture of Core developers Picture of Plugin developers Picture of Testers Picture of Translators

Happy to read that you like it smile

I am already thinking about using that magic with the generico filter (update will follow... soon)

Average of ratings: -
In reply to Nadav Kavalerchik

Re: Adding visual representations to plain quantitative SQL reports

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers

I tried both reports out on my site and keep getting a no semicolon error?  Any thoughts?

Average of ratings: -
In reply to Emma Richardson

Re: Adding visual representations to plain quantitative SQL reports

by Nadav Kavalerchik -
Picture of Core developers Picture of Plugin developers Picture of Testers Picture of Translators

Please turn debugging on, and attach a screen capture of what you get. or just copy and paste the detailed error message here.

Average of ratings: -
In reply to Nadav Kavalerchik

Re: Adding visual representations to plain quantitative SQL reports

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers
Invalid get_string() identifier: 'nosemicolon' or component 'report_customsql'. Perhaps you are missing $string['nosemicolon'] = ''; in /lang/en/report_customsql.php?
  • line 349 of /lib/classes/string_manager_standard.php: call to debugging()
  • line 6876 of /lib/moodlelib.php: call to core_string_manager_standard->get_string()
  • line 105 of /blocks/configurable_reports/components/customsql/form.php: call to get_string()
  • line 84 of /blocks/configurable_reports/components/customsql/form.php: call to customsql_form->validation_high_security()
  • line 571 of /lib/formslib.php: call to customsql_form->validation()
  • line 520 of /lib/formslib.php: call to moodleform->validate_defined_fields()
  • line 617 of /lib/formslib.php: call to moodleform->is_validated()
  • line 100 of /blocks/configurable_reports/editcomp.php: call to moodleform->get_data()
Tried with adaptable and clean to see if it was maybe a theme issue.  Using latest version of config report on Moodle 3.1.2
Average of ratings: -
In reply to Emma Richardson

Re: Adding visual representations to plain quantitative SQL reports

by Nadav Kavalerchik -
Picture of Core developers Picture of Plugin developers Picture of Testers Picture of Translators

Ok. now I understand what is happening...

The above warning, is just a missing translation string. not something that suppose to prevent you from running the report.

BUT the main cause is that I was using CSS ";" inside my CONCAT strings, and the SQL parser was "upset" about it.

Please remove the ";" from "em;", as it is not that important for the browser's CSS parser.


I am not getting this error, since I use a different ("forked") version of block_configurable_reports. which is less secure and allow me to use ";" inside SQL queries. It has some different features which not all of them got into the main version Juan is maintaining. maybe one day smile

Average of ratings: -
In reply to Nadav Kavalerchik

Re: Adding visual representations to plain quantitative SQL reports

by Nadav Kavalerchik -
Picture of Core developers Picture of Plugin developers Picture of Testers Picture of Translators

Ok. pushed it a little bit further...

Chartlets small charts inside SQL reports


Added the JS library chartlets.com to the configurable reports block, and tweaked the SQL query to:

,CONCAT('<canvas class="chartlet" 
		data-type="line"  
		data-colors="#ffcc00 #ff66cc" 
		data-range="0 10" 
		data-sets="[',
		COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=0,l.id,NULL)),' ',
		COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=1,l.id,NULL)),' ',
		COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=2,l.id,NULL)),' ',
		COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=3,l.id,NULL)),' ',
		COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=4,l.id,NULL)),' ',
		COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=5,l.id,NULL)),' ',
		COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=6,l.id,NULL)),' ',
		COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=7,l.id,NULL)),' ',
		COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=8,l.id,NULL)),' ',
		COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=9,l.id,NULL)),' ',
		COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=10,l.id,NULL)),' ',
		COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/604800)=11,l.id,NULL)),' ',
		'] [0 0 0 0 0]" 
		width="100" height="35" 
		data-opts="stroke: 2 shape: smooth cap: circle"></canvas>') as "Chartlet"

You can also add the Chartlets.js library via "Site administration > Appearance > Additional HTML" (In case you do not want to patch just a few lines in the CR block) 

Average of ratings: Useful (1)