Filters not working

Filters not working

by Ross Geller -
Number of replies: 3

Hi, 

I am using the build in report: User course completion with Criteria and it seems to do pretty much what I am looking for.  The goal is to be able to filter by user (student) in this report so that I can see all the courses this person has completed, or has not completed.  Right now it is manageable with a few users, but soon it will be hundreds so the filter would be extremely helpful.

I have tried to add the filter by user filter (from system user list) and I have tried the username (from user field) filter.  They show up on the report, but when I choose a specific user, and apply, it still shows other users in the report.  Please see the screenshots attached.

I know I can just export to excel and work there, but I was hoping to be contained within Moodle for this.

Any help is appreciated, and thank you very much in advance.

Ross

Attachment UsernameExample.png
Attachment UsersExample.png
Average of ratings: -
In reply to Ross Geller

Re: Filters not working

by Peter Bowen -

Did you include the filter code within your SQL?

Each filter also requires a line for the filter to do something within SQL.


Cheers
Peter

Average of ratings: -
In reply to Peter Bowen

Re: Filters not working

by Ross Geller -

Hi Peter,

Thank you so much for the reply.  I am novice at best with SQL, so I think they answer is I have not.  Here is what is in the SQL code:

SELECT u.username, c.shortname,  

DATE_FORMAT(FROM_UNIXTIME(p.timecompleted

),'%Y-%m-%d') 

AS completed

FROM prefix_course_completions AS p

JOIN prefix_course AS c ON p.course = c.id

JOIN prefix_user AS u ON p.userid = u.id

WHERE c.enablecompletion = 1

ORDER BY u.username


Where would I put the filter, and do you have a sample of what it would look like?  Email, username, or user would be the best filters I would like to use.


Thank you so much for the assistance,

Ross

Average of ratings: -
In reply to Ross Geller

Re: Filters not working

by Peter Bowen -

The code depends on the filter. It is normally placed where you would put a WHERE statement (or after one) as essentially that is what the filter does.


%%FILTER_USER:CONCAT(u.username,' ',u.firstname, ' ', u.lastname,' ',u.email)%%


The above filter would be used with the 'search user name details' filter, and you can free text search on username, first name, last name or email address.


Cheers

Peter

Average of ratings: Useful (1)