Re: New plugin: Custom reports

Re: New plugin: Custom reports

by Barbara Lawrence -
Number of replies: 15

Hello Juan

Thanks for your work on this, I like the idea.  But I am having problems getting conditions and filters to work.

I am not sure if I should include quotes in the value on the condition screen for a text field, but I have tried it with single quotes, double quotes and no quotes - whichever I choose results in 0 records found. Setting a condition on a numeric field also results in 0 records found.

When I add a filter, when I view the report I can see a dropdown box but it does not contain the fieldname of my filter - it just says choose, nothing else.

I suspect that these problems may be due to the fact that my database is PostgreSQL....?  I am using Moodle 1.9.8.

Thanks

Barbara

Average of ratings: -
In reply to Barbara Lawrence

Re: New plugin: Custom reports

by Luis de Vasconcelos -

What is the SQL that you're using?

Make sure you have the latest version of the report builder. Juan has been quite busy patching it today - and he continues to improve it, so check back frequently for updates. (And vote for it too!)

http://tracker.moodle.org/browse/CONTRIB-2386
http://tracker.moodle.org/browse/CONTRIB/component/10753
http://docs.moodle.org/en/blocks/configurable_reports/

Average of ratings: -
In reply to Barbara Lawrence

Re: New plugin: Custom reports

by Juan Leyva -
Picture of Core developers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

Hello Barbara,

yes, i suspect too that the problems may be due to Postgres

Can you tell me what type of report are you trying to create and with what conditions?

I will create the same report with MySQL and try to figure out what is happening

By the way, as Luis says download the last version, "configurable_reports.zip" (I've renamed it to avoid confussion with custom sql report plugin)

Regards

Average of ratings: -
In reply to Juan Leyva

Re: New plugin: Custom reports

by Barbara Lawrence -

HI Juan

I have downloaded the latest version today.

The user report I set up was a copy of your Users from Spain report.
I realised that I didn't need any quotes around the text fields ES and Madrid. The only problem with this one now is that when I view the report and click the drop down filter box no city names are displayed  - it just says Choose.  See the last image in the attached screenshots.

For the sql report I used your example but had to amend it to make it work for postgresql  (adding "as" with aliases, and including the select fields in the group clause), also restricted it to a category because m log table is very large and testing on my local pc:
This is my sql:

SELECT COUNT(l.id)as hits, l.course as courseId, c.fullname as coursename
FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id
where c.category =6
GROUP BY courseId,c.fullname
ORDER BY hits DESC

I added a courses filter and a date time filter.
This time the drop down filter boxes are populated but selecting them does not change the report.  When I look at the sql, the tokens have not been added.

screenshot of filter in configurable report.

Thanks for looking at this.

Barbara

Average of ratings: -
In reply to Barbara Lawrence

Re: New plugin: Custom reports

by Juan Leyva -
Picture of Core developers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

Hi Barbara,

you must edit the SQL query an enter de Tokens for the filters. Adding a filter does not add the tokens automatically.

You SQL query should be this:

SELECT COUNT(l.id)as hits, l.course as courseId, c.fullname as coursename
FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id
where c.category =6
%%FILTER_COURSES:l.course%%
%%FILTER_STARTTIME:l.time:>%% %%FILTER_ENDTIME:l.time:<%%
GROUP BY courseId,c.fullname
ORDER BY hits DESC

I can't open the user screenshots (the file has not extension) Can you upload the screenshots in a new post?

Average of ratings: -
In reply to Juan Leyva

Re: New plugin: Custom reports

by Barbara Lawrence -

Hi Juan

Thanks for such a quick response.

Sorry I misunderstood about the filters. I copied and pasted your sql, then selected a course (Doodle) from the dropdown. But the response now is "no records found".

Not sure why my attachment had no file extension.  This time it is zipped up (pdf).

Average of ratings: -
In reply to Barbara Lawrence

Re: New plugin: Custom reports

by Juan Leyva -
Picture of Core developers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

Hi Barbara,

Is the course in the same category of the query?

Regarding the user report, could you turn on debugging in Moodle, in Developer mode checking the "Display errors" preference? You will see errors is something is wrong

Juan

Average of ratings: -
In reply to Juan Leyva

Re: New plugin: Custom reports

by Barbara Lawrence -

Hi Juan

The course filter on the sql query report is working – I think this was again user error! I forgot to change the dates in the date filter and there were probably no hits in the default period.  When I expanded the date period then the report did show the hits for the selected course.   Sorry for bothering you about that.

I switched on debugging for the user report and the following error was reported:

[20-Oct-2010 08:58:42] PHP Notice:  ERROR:  zero-length delimited identifier at or near """"

LINE 1: ...ISTINCT(city) as ufield FROM m_user WHERE city <> "" ORDER B...

^<br /><br />SELECT DISTINCT(city) as ufield FROM m_user WHERE city &lt;&gt; &quot;&quot; ORDER BY ufield

Average of ratings: -
In reply to Barbara Lawrence

Re: New plugin: Custom reports

by Andreas Gross -

Hi Juan

your contribution to Moodle is well received! Very well done!

I want to use it to print a form about several users with their data and also data from User profile fields. That works very well and I am just finished with designing my first report.

But when I want to print it, the printed pages includes the menu of moodle and also the filter plus filter-button of your Block plus a horizontal orange line at the footer.

I used the ctrl-P function to print the page which I see on the screen.

Is there any other way in which I can print only the things designed in the template and no other header or footer?

I could create a sample image to show what I mean, if necessary. Should I?

yours

Res

Average of ratings: -
In reply to Juan Leyva

Re: New plugin: Custom reports

by Nick Thompson -

This plugin is awesome.

The idea of using Filters is great, but it's not practical to add new filters.  The reason being, every time a new filter is needed, we would have to go in, create the code, commit it to our version control system before we are able to use it.

It would be very nice if filters themsevles had an administrative interface as well, so that we can add them without having to go through the process of adding new code, which depending on the workflow can be very lengthy if the code has to go through test, stage, then prod.

Nick

Average of ratings: -
In reply to Nick Thompson

Re: New plugin: Custom reports

by Juan Leyva -
Picture of Core developers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

Hi Nick,

Creating an interface for filters looks pretty complicated at first glance.

What type of filters are you thinking of?

Juan

Average of ratings: -
In reply to Juan Leyva

Re: New plugin: Custom reports

by Nick Thompson -

We embed course information into the idnumber field.  specifically, the "term" that the course belongs to.

When we run reports we want to report on a subset of courses belonging to a specific term, so instead of creating a whole new report for each term, it would be nice if we could generalize, then just add a filter.  For example, when reporting on 09F, the filter would convert:

where idnumber like %%COURSETERMFILTER%%

to

where idnumber like %09F-%

but the "09F" part would be the filter, or variable parameter.

Average of ratings: -
In reply to Juan Leyva

Re: New plugin: Custom reports

by Nico Potgieter -

This is indeed a good plugin and works very easy. When using SQL report type the only filters available are Categories/Courses and Start & End date Filters.

 

I would like to be able to add filter on users. We need the ability to produce a top level report for our security access department on a specific user. I have tried creating my own filter by adding users to the configurable_reports\components\filters and changing some of the code. I am afraid I am not a PHP developer and had to leave it there.

 

 Any help would be very helpfull

Average of ratings: -
In reply to Nico Potgieter

Re: New plugin: Custom reports

by Nico Potgieter -

I managed to resolve this. I created a new filter called users. This must be uploaded in to the configurable_reports\components\filters\users folder.

It also does a sort on the usernames before giving the list to the user.

Any improvements on this will appreciated.

The SQL statement is

SELECT u.username As EmpNo, u.firstname AS 'Name' , u.lastname AS 'Surname',cc.name AS 'Category', c.fullname AS 'Course',
gi.itemname AS 'Item Name', ROUND(gg.finalgrade,2) AS Score,ROUND(gg.rawgrademax,2) AS Max, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) as '%',
 if (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') as Pass

FROM prefix_course AS c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE  gi.courseid = c.id and gi.itemname != 'Attendance' and u.username != '' %%FILTER_USERS:u.id%%
ORDER BY `Name` ASC

Average of ratings: -
In reply to Juan Leyva

Re: New plugin: Custom reports

by Lavanya Manne -
Picture of Plugin developers

Hi Juan Leyva,

I want to add a new feature to start and end filters as shown in the image, When i give

$mform->addElement('date_selector', 'filter_starttime', get_string('starttime', 'block_configurable_reports'), array('optional'=>true));

in the back end, the start and end date filters aren't working.

or if this doesn't work!!!!

I want to give an option 'Choose' to all the three fields(year,month,date) so that until i choose the date it should get started like how we have for course filter.

Could you please give me the right syntax to go with?

Attachment sample.JPG
Average of ratings: -
In reply to Lavanya Manne

Re: New plugin: Custom reports

by Johnny Zephyr -

Hi

I aam also interested in either having 'choose' or blanking/zero-ing out the three date fields if possible. How do we go about doing this?

Thank you.

Average of ratings: -