Example of filter use on custom sql report

Example of filter use on custom sql report

بواسطة - Roger J
عدد الردود: 3

Hi,

I'm exploring using your terrific configurable report plug-in for a Moodle project.  My main interest in custom SQL reports. What I can not figure out is how to integrate filters with my custom SQL?

Let's say I'm writing a custom report that reads data from a table I've added to Moodle. This isn't a built-in Moodle table, but it is added to the same database as our Moodle instance. The report works fine and reads the new table's data.

For example, the new table has a field called "Variable".

How do I add a filter to my SQL to query this field?

Thanks!

Roger J.

متوسط التقييمات: -
رداً على Roger J

Re: Example of filter use on custom sql report

بواسطة - José ignacio Pernía

Hello Roger.


I mainly use the plugin with the user reports and course reports, but this maybe will help you configuring filters: https://docs.moodle.org/27/en/Configurable_reports#Creating_a_SQL_Report  this moodle doc helped me to configure once a filter in a SQL report, and this one have the list of variables: http://www.moodleworld.com/list-of-all-variables-and-filters-for-moodles-configurable-reports-plugin/


Hope it helps,

Kindly Regards.

متوسط التقييمات:Useful (2)
رداً على José ignacio Pernía

Re: Example of filter use on custom sql report

بواسطة - Roger J

Jose,

Thank you for your comments and links! Through additional testing, I found a syntax that works.

Here's an example in case it is helpful to others (this SQL references a custom, non-moodle, table):

SELECT id, name, value,
  FROM dbo.mydata
 WHERE courseid = %%COURSEID%%
       %%FILTER_SEARCHTEXT:name:~%%
ORDER BY id

A new limitation I'm running into now though is that you can only select a filter field of a certain type once in a report. So for example I can not have two FILTER_SEARCHTEXT filter fields. I wonder if there is a work-around for this?

Thanks again!

Roger J.

متوسط التقييمات:Useful (1)