Configurable Reports

Hello,
Is it possible to get the sql query of Course Completion? I want to create a report that shows a summary of the training program (username and completion status of each course). The report has a "Details" Button that should show additional course information (task completed etc).
Any idea if this can be achieved through custom reports?

@Ken Thanks for sharing this! This was very slick. I removed one parenthesis from your statement and the double quotes and it worked then.
u.id=(if(length(%%FILTER_VAR%%) >=1, if(length(%%FILTER_VAR%%) <6,cast(%%FILTER_VAR%% As Decimal),1), 1))
Thanks again! Cheers. -Ben

@Ben We spent quite some time working out how to pull a variable from the url. It is quite easy to put a filter variable into the url for use in the sql, eg
/blocks/configurable_reports/viewreport.php?id=33&courseid=1&filter_var=360
you can then have a "where" statement that uses it eg
where u.id="%%FILTER_VAR%%" /* dangerous code don't do this!! */
The problem is that this opens up big vulnerabilities for code injection allowing people to access anything in the database. What we did is use the filter variable in the sql in a way that prevents code being injected by limiting what will be read to a number of less than 6 digits eg
where u.id=(if(length("%%FILTER_VAR%%") >=1, if(length("%%FILTER_VAR%%") <6,cast("%%FILTER_VAR%%" As Decimal),1), 1)))
We think this is hack proof, if you can see issues with it then do let me know.
Ken

@Martin Greenway, that's the trick! chmod 755. Thanks for you help.

@Leon, the folder in question is the blocks/configurable_reports folder under your Moodle web directory. Check that the files in there are readable. Also check the directory itself is readable. You might need to change the ownership and permissions of the files/folders in the directory so that they are owned by the same user:group as the rest of your Moodle install.

Guys, when click on the 'Manage Reports' link, I'm getting a 403. I would appreciate some areas to look at. Permissions on files/folders look ok at a high level.
Thanks!

Has anyone used a variable in the URL string of the report in a SQL query? It would be great to pass the userid in a url string from one report to link to another report where that userid is used as a variable in the query. If you have (or could conceive how to do this), please share!
Also, is it possible when using the search text filter to have no results initially before applying a filter? Thanks for any feedback! -Ben

Hello Juan
i keep have a strange error while using your plug (Moodle 2.9.9...):
I'm trying to create a template using the documentation you have provided for your plug, but my editor (atto) keeps adding some additional tags so i can't complete the new template as i would like to. I realise this is not 100% your plugin's fault, but, in short, is there any workaround from the settings of your plug to allow using the template as defined in the tutorial?
Regards

I am using Moodle 3.3 (Build: 20170515).
I am getting this error. Is it because my Moodle version is not supported?
Validating block_configurable_reports ... Error
[Error] Write access check [/opt/bitnami/apps/moodle/htdocs/blocks]
Installation aborted due to validation failure

Im having issues installing it on 3.3, running php 7 and Ubuntu 16 lts. Surprised it is working for you in 3.2. I will have to try then.

Hi Yegor, we are using php 7 on Ubuntu 16 LTS.

Hi Juan Leyva,
what about viewing report in hierarchy(Organisation) basis , whether this scenario is achievable or not ?

Hello Michael, what version of php are you using?

Hi all, I've been using this plugin in Moodle version 3.2 for the past 6 months, and all good so far. In the version.php, it is release 3.1.1 and version 2016020103.

Hi there,
Any news for moodle 3.2?