I have been asked to help create a custom report part of which involves aggregating information found in the "value" field of prefix_scorm_scoes_track table, where the "element" is cmi.suspend_data.
I have been asked to help due to my experience with SQL and not Moodle/SCORM, of which my knowledge is limited.
I am extracting the information I need - prior to processing - by explicitly referencing character locations within the suspend data string and this obviously relies on the format and overall length of the string remaining constant.
SELECT prefix_user.username,
........
((CASE(SUBSTRING(value_rep,237,1)) WHEN 'c' THEN 1 ELSE 0 END) +
........
(CASE(SUBSTRING(value_rep,3659,1)) WHEN 'c' THEN 1 ELSE 0 END)) AS "# Videos watched"
........
FROM ((prefix_user LEFT JOIN (SELECT prefix_scorm_scoes_track.userid, Replace(value,"''","'XX'") AS value_rep
FROM prefix_scorm_scoes_track WHERE (((prefix_scorm_scoes_track.element)='cmi.suspend_data'))) AS vid ON prefix_user.id = vid.userid
The issue I am having is that, depending on the progress in the course, I can have multiple occurrences of any of the following
{?:''}, {?:'n'} or {?:'nn'}
Where n is a digit 0-9 and the overall length of the suspend_data string varies according to the occurrences of n
At first it appeared that it could only be {?:''} or {?:'nn'}, which was solved using REPLACE(value," ' ' "," ' XX ' ") NB I've exploded this expression to show where single and double quotes occur, there are actually no spaces within the double quotes.
However with {?:'n'} occurring this is no longer valid. This could be resolved normally using a CLR function on the SQL server.
Is there a way I could achieve this same functionality through the Moodle course reporting interface?
My current proposed solution for the client is to post-process an exported CSV using Excel VBA.