MoodleDocs - User contributions [en]

Custom SQL queries report

2012-06-21T22:45:47Z

Rmeske: Correct link to the plugins directory

{{Sitewide reports}}
Created by The Open University

This admin report plugin allows Administrators to set up arbitrary database queries to act as ad-hoc reports. Reports can be of two types: either run on demand, or scheduled to run automatically. Other users with the right capability can go in and see a list of queries that they have access to. Results can be viewed on-screen or downloaded as CSV.

==Installing this report==

Follow the generic [[Installing plugins]] documentation.

==Screen shots==

Here are two example screen shots, showing the two main screens.

[[Image:Custom_report_list.png|thumb|none|600px|The list of available reports]]
[[Image:Custom_report.png|thumb|none|600px|The results of running one of the reports]]

(Note, these screen shots are in the OU theme, not the standard Moodle theme, and iCMA is OU-jargon for quiz.)

==Interface for normal users==

===List of available queries===

Users with the report/customsql:view capability can access the list of reports in the admin block. Each query is accessible only to a certain people. There are three levels of access:
* Available to any one who can access the report at all (those with report/customsql:view).
* Available to people who are able to see other system reports (those with moodle/site:viewreports)
* Available to administrators only (those with moodle/site:config)

When you go to the list, it will only show the queries you have access to. There is a note beside each query saying when it was last run, and how long it took to generate.

The list shows on-demand and scheduled queries separately.

===Running an on-demand query===

To run an on-demand query, click on its name in the list of queries.

The query will be run, and the results will be displayed as a table. Any URLs in the table will automatically be made into hyperlinks.

A description of the query may appear above the table.

The summary of when the query was run and how long it took is shown at the bottom, along with a link to download the CSV file (for example to get the data into Excel) and a link back to the list of all available queries.

===Viewing the results of scheduled queries===

Scheduled queries can work in one of two ways. Either each run of the report generates and entire table of reasults, or each run just creates one line of results, and the report builds up a row at a time.

When you click the name in the list of queries, you get taken to a display of the latest results, just like in the on-demand case.

However, if each scheduled run generates a complete report, then at the bottom of a page there will be a list of all the previous runs of the report, so that you can go and see how the report changed over time.

==Interface for administrators==

Administrators (that is, users with report/customsql:definequeries) see everything that other users see, but with some additions.

===Additional controls in the staff interface===

Administrators get shown who each report in the list is available to.

They also get an edit and a delete icon next to each query.

There is an '''Add new query button''' at the end of the list of queries.

When viewing a particular query, Administrators get an edit and a delete link underneath the table of results.

===Adding or editing a query===

When you click the '''Add new query button''', you get taken to an editing form that lets you define the query.

You must give the query a name.

You can optionally enter a description that is displayed above the results table. You should use this to explain what the results of the query mean.

You must enter the SQL to generate the results you want displayed. This must be an SQL select statement. You must use the prefix prefix_ for table names. It should not be possible to enter any SQL that would alter the contents of the database.

You choose who you want the query to be accessible to.

You choose whether the query should be run on-demand or scheduled weekly or monthly. If the report is scheduled, you can say whether the each run returns one row to be added to a single table, or whether each run generates a separate table.

When you save the new query, the SQL is checked to make sure that it will execute without errors. If you have said that the report will only return a single row, this is also checked.
After saving the query, if this was a manual query, you are taken to the query results page, so you can see what the results look like. If it was an automatic query or if you cancel the form, you are taken to the list of available queries.

Editing an existing query uses the same form as for adding a new query, but to change the properties of an existing query.

Note that at the OU, weeks start on Saturday. If you don't like that, there is a fairly obvious constant to hack at the top of locallib.php.

===Deleting a query===

When you click the delete icon or link for a query, you are taken to a confirmation page that shows you SQL of the query you are about to delete. The query is only deleted if you click Yes on the confirmation page.

After deleting a query, you are taken back to the list of queries.

==Share your interesting queries here==

If you come up with any interesting SQL to custom reports, you can share it here.

===Quiz attempts in the last week/month===

Set this up as a scheduled report:

<code sql>
SELECT COUNT(*)
FROM prefix_quiz_attempts
WHERE timefinish > %%STARTTIME%%
AND timefinish <= %%ENDTIME%%
AND preview = 0
</code>

This '''must''' be set up as a '''Scheduled, on the first day of each week''' or '''Scheduled, on the first day of each month''' report, or it will not work.

===Usage summary===

This report shows roughly the same usage statistics that are sent to moodle.org when you register your site. The ones that are aggregated at http://moodle.org/stats/. (The only difference is that the registration form does not do AND confirmed = 1 for some reason.

<code sql>
SELECT
(SELECT COUNT(id) FROM prefix_course) - 1 AS courses,
(SELECT COUNT(id) FROM prefix_user WHERE deleted = 0 AND confirmed = 1) AS users,
(SELECT COUNT(DISTINCT ra.userid)
FROM prefix_role_capabilities rc
JOIN prefix_role_assignments ra ON ra.roleid = rc.roleid
WHERE rc.capability IN ('moodle/course:upd' || 'ate', 'moodle/site:doanything')) AS teachers,
(SELECT COUNT(id) FROM prefix_role_assignments) AS enrolments,
(SELECT COUNT(id) FROM prefix_forum_posts) AS forum_posts,
(SELECT COUNT(id) FROM prefix_resource) AS resources,
(SELECT COUNT(id) FROM prefix_question) AS questions
</code>

There are two interesting queries in this blog post [http://tjhunt.blogspot.com/2010/03/when-do-students-submit-their-online.html When do students submit their online tests?].

===Monthly Usage by Role===

This report shows a distinct count of users by their role, accessing your site. Each instance of user and role is counted once per month, no matter how many courses they access. We use this to show the total number of students and teachers accessing our site.

<code sql>
SELECT
month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_month,
year(from_unixtime(`prefix_stats_user_monthly`.`timeend`)) AS calendar_year,
prefix_role.name as user_role,
COUNT(DISTINCT prefix_stats_user_monthly.userid) AS total_users
FROM
prefix_stats_user_monthly
Inner Join prefix_role_assignments ON prefix_stats_user_monthly.userid = prefix_role_assignments.userid
Inner Join prefix_context ON prefix_role_assignments.contextid = prefix_context.id
Inner Join prefix_role ON prefix_role_assignments.roleid = prefix_role.id
WHERE prefix_context.contextlevel = 50
AND `prefix_stats_user_monthly`.`stattype` = 'activity'
AND prefix_stats_user_monthly.courseid <>1
GROUP BY month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)),
year(from_unixtime(`prefix_stats_user_monthly`.`timeend`)),
prefix_stats_user_monthly.stattype,
prefix_role.name
ORDER BY
year(from_unixtime(`prefix_stats_user_monthly`.`timeend`)), month(from_unixtime(`prefix_stats_user_monthly`.`timeend`)),
prefix_role.name
</code>

You must have Statistics turned on to be able to generate any data with this report.

===Show all Quiz results across a site===
I'm sure someone will be able to improve upon this, but this is a query that will show Quiz results across a site. --[[User:Stuart Mealor|Stuart Mealor]] 22:07, 9 April 2010 (UTC)

<code sql>
SELECT
prefix_grade_items.itemname,
prefix_grade_items.grademax,
ROUND(prefix_grade_grades.finalgrade, 0) AS finalgrade,
prefix_user.firstname,
prefix_user.lastname,
prefix_user.username
FROM
prefix_grade_grades
INNER JOIN prefix_user ON prefix_grade_grades.userid = prefix_user.id
INNER JOIN prefix_grade_items ON prefix_grade_grades.itemid = prefix_grade_items.id
WHERE (prefix_grade_items.itemname IS NOT NULL)
AND (prefix_grade_items.itemtype = 'mod' OR prefix_grade_items.itemtype = 'manual')
AND (prefix_grade_items.itemmodule = 'quiz' OR prefix_grade_items.itemmodule IS NULL)
AND (prefix_grade_grades.timemodified IS NOT NULL)
AND (prefix_grade_grades.finalgrade > 0)
AND (prefix_user.deleted = 0)
</code>

==See also==

* [http://moodle.org/mod/forum/discuss.php?d=136484 Custom SQL queries report] forum announcement
* list of [[ad-hoc contributed reports]]
* [http://moodle.org/mod/forum/discuss.php?d=153059 Forum post] with some more useful queries.
* [http://moodle.org/plugins/view.php?plugin=report_customsql Modules and plugins database entry]

[[Category:Contributed_code]]

[[fr:admin/report/customsql/index]]

SCORM FAQ

2009-02-05T21:07:07Z

Rmeske:

{{Scorm}}==How can I create SCORM content?==

See [[Tools for creating SCORM content]] for a list of options.
==My SCORM Module doesn't function properly==

* Make sure you are running Moodle 1.9.3 or 1.8.7 or higher, a large number of SCORM related bugs have been fixed in these versions. see [http://tracker.moodle.org/browse/MDL/component/10080?selected=com.atlassian.jira.plugin.system.project:component-changelog-panel Changelog here]
* Make sure your SCORM object is SCORM compliant - check it in an external SCORM player like [https://docs.moodle.org/en/Tools_for_creating_SCORM_content#Reload Reload] to see if it works there.
* Post a copy of your SCORM object in the [http://moodle.org/mod/data/view.php?d=50 SCORM Repository] and post a message asking for help in the forums, linking to your SCORM object explaining exactly what you expect to happen, and what is happening instead.

==What version of SCORM is supported?==

* SCORM 1.2 is supported in Moodle 1.9.3 and Moodle 1.8.7 and passes all the tests in the ADL Conformance test suite 1.2..7 for SCORM 1.2. (This is not the same as being certified SCORM 1.2 compliant, but it is the level that must be attained before Moodle can apply for certification.)

* SCORM 2004 is not completely supported in Moodle at this stage. Parts of the API have been implemented, but others such as Navigation and Sequencing have not yet been implemented. It is unlikely at this stage that it will be completed by the time Moodle 2.0 is released, however it is being actively worked on.

* AICC objects are supported in Moodle 1.9.3 or 1.8.7 and higher - unfortunately the availability of publicly accessible AICC objects is limited, so testing has been rather difficult. If you have any AICC objects you are willing to share, please upload them to the [http://moodle.org/mod/data/view.php?d=50 SCORM Repository]

* SCORM 2.0 has not been released yet by ADL, and Moodle does not support any of it at this stage.
==What you can do to help in the SCORM Forum==

When trying to engage the community to help with a problem you are facing, you will get a better response if you follow a few simple guidelines:

* be prepared to provide a SCORM package that illustrates your problem - if you don't then it will be very difficult for anyone offering assistance to recreate your situation - a real barrier to help.

* always start your report with version information - preferably the information displayed on the Admin -> Environment panel eg. http://localhost/moodle/admin/environment.php where http://localhost/moodle is your particular prefix. With this it will be clear how you are running your Moodle instance, on what platform, and at which version. This will quickly expose issues where a simple upgrade will solve your problem.

==My SCORM doesn't transfer grades the way I want it to==
* please see [https://docs.moodle.org/en/SCORM_FAQ#My_SCORM_Module_doesn.27t_function_properly FAQ:My SCORM Module doesn't function properly]
* make sure in the update/edit a SCORM object screen, that the "maximum grade" setting is set to what you would expect (most people use 100)
* make sure in the update/edit a SCORM object screen, that the "grading method" is set to what you would prefer.
* Much of the way SCORM objects are graded is controlled inside the SCORM Authoring process before it is packaged for use in an LMS like Moodle - make sure all your grading settings are set correctly. Moodle 2.0 contains some new controls that allow a teacher to override the behaviour as set by the SCORM object to allow for greater more flexible control. (see MDL-11501 for more information on this)

==slash arguments warning when I add/update SCORM obects in my course==
As of Moodle 1.9.3 SCORM forces what is referred to as "slash arguments" - Unfortunately, some PHP servers don't allow this method and your SCORM objects may not display. This affects IIS 5 and earlier, and some Apache servers. Under IIS 5 and earlier, a workaround using an [http://www.isapirewrite.com/ ISAPI re-write] tool can be used, Apache users should look at this link: https://docs.moodle.org/en/Installation_FAQ#Uploaded_files_give_.22File_not_found.22 - A check on the admin/health.php page is also made to see if slasharguments is supported - visit http://yourmoodlesite/admin/health.php to check to see the status of slasharguments on your server.

==Zlib warning when I add/update SCORM objects in my course==
Zlib is a php compression setting made in a websites PHP configuration - unfortunately some browsers don't handle this well (especially Internet Explorer 6) Some webhosts enable this setting, but it will likely cause issues for your users when they attempt to view/use the SCORM object. You will need to contact your server administrator to turn this off. The setting to change in php configuration is "zlib.output_compression"

==Large SCORM package will upload to Moodle, but will not display==
If you get a blank page after filling in the title, description, and selecting a large SCORM file, it's likely that the file you are trying to upload is too big for the php zip library to handle - you must use external zip/unzip binaries - see here: https://docs.moodle.org/en/System_paths#Path_to_zip

==How do I clear an attempt for a student==
To clear attempts by Student:

1. Go to the SCORM activity and select the link "View reports for x users"
2. Select the attempt or attempts you want to clear using the checkbox
3. Select Delete in the drop-down box at the bottom of the page

==See also==

* Using Moodle [http://moodle.org/mod/forum/view.php?id=1951 SCORM forum]

[[Category:FAQ]]
[[de:Lernpaket FAQ]]

SCORM FAQ

2009-02-05T21:04:56Z

Rmeske:

{{Scorm}}==How can I create SCORM content?==

See [[Tools for creating SCORM content]] for a list of options.
==My SCORM Module doesn't function properly==

* Make sure you are running Moodle 1.9.3 or 1.8.7 or higher, a large number of SCORM related bugs have been fixed in these versions. see [http://tracker.moodle.org/browse/MDL/component/10080?selected=com.atlassian.jira.plugin.system.project:component-changelog-panel Changelog here]
* Make sure your SCORM object is SCORM compliant - check it in an external SCORM player like [https://docs.moodle.org/en/Tools_for_creating_SCORM_content#Reload Reload] to see if it works there.
* Post a copy of your SCORM object in the [http://moodle.org/mod/data/view.php?d=50 SCORM Repository] and post a message asking for help in the forums, linking to your SCORM object explaining exactly what you expect to happen, and what is happening instead.

==What version of SCORM is supported?==

* SCORM 1.2 is supported in Moodle 1.9.3 and Moodle 1.8.7 and passes all the tests in the ADL Conformance test suite 1.2..7 for SCORM 1.2. (This is not the same as being certified SCORM 1.2 compliant, but it is the level that must be attained before Moodle can apply for certification.)

* SCORM 2004 is not completely supported in Moodle at this stage. Parts of the API have been implemented, but others such as Navigation and Sequencing have not yet been implemented. It is unlikely at this stage that it will be completed by the time Moodle 2.0 is released, however it is being actively worked on.

* AICC objects are supported in Moodle 1.9.3 or 1.8.7 and higher - unfortunately the availability of publicly accessible AICC objects is limited, so testing has been rather difficult. If you have any AICC objects you are willing to share, please upload them to the [http://moodle.org/mod/data/view.php?d=50 SCORM Repository]

* SCORM 2.0 has not been released yet by ADL, and Moodle does not support any of it at this stage.
==What you can do to help in the SCORM Forum==

When trying to engage the community to help with a problem you are facing, you will get a better response if you follow a few simple guidelines:

* be prepared to provide a SCORM package that illustrates your problem - if you don't then it will be very difficult for anyone offering assistance to recreate your situation - a real barrier to help.

* always start your report with version information - preferably the information displayed on the Admin -> Environment panel eg. http://localhost/moodle/admin/environment.php where http://localhost/moodle is your particular prefix. With this it will be clear how you are running your Moodle instance, on what platform, and at which version. This will quickly expose issues where a simple upgrade will solve your problem.

==My SCORM doesn't transfer grades the way I want it to==
* please see [https://docs.moodle.org/en/SCORM_FAQ#My_SCORM_Module_doesn.27t_function_properly FAQ:My SCORM Module doesn't function properly]
* make sure in the update/edit a SCORM object screen, that the "maximum grade" setting is set to what you would expect (most people use 100)
* make sure in the update/edit a SCORM object screen, that the "grading method" is set to what you would prefer.
* Much of the way SCORM objects are graded is controlled inside the SCORM Authoring process before it is packaged for use in an LMS like Moodle - make sure all your grading settings are set correctly. Moodle 2.0 contains some new controls that allow a teacher to override the behaviour as set by the SCORM object to allow for greater more flexible control. (see MDL-11501 for more information on this)

==slash arguments warning when I add/update SCORM obects in my course==
As of Moodle 1.9.3 SCORM forces what is referred to as "slash arguments" - Unfortunately, some PHP servers don't allow this method and your SCORM objects may not display. This affects IIS 5 and earlier, and some Apache servers. Under IIS 5 and earlier, a workaround using an [http://www.isapirewrite.com/ ISAPI re-write] tool can be used, Apache users should look at this link: https://docs.moodle.org/en/Installation_FAQ#Uploaded_files_give_.22File_not_found.22 - A check on the admin/health.php page is also made to see if slasharguments is supported - visit http://yourmoodlesite/admin/health.php to check to see the status of slasharguments on your server.

==Zlib warning when I add/update SCORM objects in my course==
Zlib is a php compression setting made in a websites PHP configuration - unfortunately some browsers don't handle this well (especially Internet Explorer 6) Some webhosts enable this setting, but it will likely cause issues for your users when they attempt to view/use the SCORM object. You will need to contact your server administrator to turn this off. The setting to change in php configuration is "zlib.output_compression"

==Large SCORM package will upload to Moodle, but will not display==
If you get a blank page after filling in the title, description, and selecting a large SCORM file, it's likely that the file you are trying to upload is too big for the php zip library to handle - you must use external zip/unzip binaries - see here: https://docs.moodle.org/en/System_paths#Path_to_zip

==How do I clear an attempt for a student==
To clear attempts by Student:

1. Go to the SCORM activity and select the link to View reports for x users.
2. Select the attempt or attempts you want to clear
3. Select Delete in the drop-down box at the bottom of the page.

==See also==

* Using Moodle [http://moodle.org/mod/forum/view.php?id=1951 SCORM forum]

[[Category:FAQ]]
[[de:Lernpaket FAQ]]

Development talk:Progress tracking

2008-07-21T14:40:53Z

Rmeske: New section: Additional information to include in reports

I have some doubts :

Goals :

1. According to the new definition, actually how admins need to assign a competency to user? The Admins need to define the competency for the subject first and then assign it to users, isnt it?

'''Definitions'''
1. Required grade : I think this should be "Required value: The minimum scale value that is needed to deem a competency" on the basis that outcomes use scales.

'''General Architecture'''
'''Competency based progress tracking'''
1. I think users should link with competencies, not with the outcome... Am I correct?
3. Calculating the final grade may be a issue when we have the literal scales
4. I didnt get it / do we need this point
5. should we say outcome or competency?
6. again, should we say outcome or competency?

'''Learning plan'''
1. Do we need to say "Each user can have a set of either courses to be completed, outcomes to be completed, or a mixture of both. These comprise an individual user's learning plan." or "Each user can have a set of either courses to be completed, competencies should be achieved, or a mixture of both. These comprise an individual user's learning plan."

2. "Due dates for course completions and outcome completions can be assigned in an individual user's learning plan." or "Due dates for course completions and competency completions can be assigned in an individual user's learning plan."

Learning plan template :

1. "Each template can contain a set of courses to be completed, outcomes to be completed, or a mixture of both." or "Each template can contain a set of courses to be completed, competencies to be completed, or a mixture of both."

'''I didnt do anything on above.'''''Italic text''

Also need to do a small change in the last example. I will do that

== Additional information to include in reports ==

With the growing use of eLearning activities based on SCORM it would be beneficial to incorporate some additional data columns into reports beyond the Status, Date and Grade. The addition of a start date and the time spent in the activity would provide the information needed to evaluate activities in terms of how long an online activity is taking. In business, an example is a situation in which tax incentives for training employees are provided based on the amount of time spent in training.

A feature to add, would be the ability to customize a report. This would require adding the ability to design a report based on a list of data columns available. This would allow the user to generate a report that includes the data desired. That way, instead of designing different reports to satisfy different needs, the could be just one report engine that is passed the columns to include in report.

--[[User:Ron Meske|Ron Meske]] 09:40, 21 July 2008 (CDT)