Customizing output of values?

Customizing output of values?

by Marco Frischknecht -
Number of replies: 8

Good day everyone.

I was wondering if it was possible to change the output of values when making reports not using SQL but rather the UI side of things. We have a report with all the activity within each module of a course per user and as it is, it shows the amount of times each user has seen an activity. What we'd like to do is to change these values to be more understandable for the teachers, meaning 0 = not seen, anything above 1 = seen. So, is it possible? I'm not well versed in SQL but I know it's possible and if not possible, I'll do it through SQL.

Thanks.

Average of ratings: -
In reply to Marco Frischknecht

Re: Customizing output of values?

by Marco Frischknecht -
Bumping this thread because I could really use an answer here, thanks.
Average of ratings: -
In reply to Marco Frischknecht

Re: Customizing output of values?

by David Saylor -
It would be very easy to do in the SQL but there isn't a way to edit the values via the UI. I'd be happy to help with the SQL if you can send me your current SQL.
Average of ratings: Useful (1)
In reply to David Saylor

Re: Customizing output of values?

by Marco Frischknecht -
Hi David, thank you very much the response. Not possible through the UI, I feared as much, though it's good to have a definitive answer. I've been working on the SQL query though I'm afraid I'm not quite as skilled of a coder as others. Basically, what I want to do is display a list of users, one per row, with their info like name, email, etc. and their completion of all activities within a course. Very much like the attached picture, but with SQL. The idea is that 0 is not viewed while anything above 1 is viewed.

Here's the query I've been working on so far. The issues I'm having is that I'm getting unwanted duplicates with things like users or grades showing all grades in the course instead of the highest which is what I need. Furthermore, I can't get the activity name and completion to show under one column using aliases. I think it boils down to issues with my logic for the query. I looked at the list of contributed queries but I haven't had much luck with them.

Regardless, I thank you for the reply and for whatever help you can offer.

Attachment example.png
Average of ratings: -
In reply to Marco Frischknecht

Re: Customizing output of values?

by David Saylor -

For the grades you could do a subquery that pulls grades for an activity but does an ORDER BY finalgrade DESC LIMIT 1 to only take the highest.


For showing activity name and completion state you could change your query to something like:
SELECT CONCAT(r1.name, ' (', cmc1.completionstate, ')') to give an output like "Resource Name (CompletionState)

Average of ratings: Useful (1)
In reply to David Saylor

Re: Customizing output of values?

by Marco Frischknecht -
Thanks for the reply, David. I'll take another shot at the query and let you know what happens.
Average of ratings: -
In reply to David Saylor

Re: Customizing output of values?

by Marco Frischknecht -
Hi David. I managed to get coursecompletions working but grades are still wonky. It's pulling data from one course regardless of what course I choose as a parameter. I'll drop the bit of code for grades and the whole query below.

,IFNULL((LEFT(g.finalgrade,2)),'Incomplete') AS 'Grade'

And the entire query.  Let me know if you find a solution. Regardless, thanks for the help.

Marco
Average of ratings: -
In reply to Marco Frischknecht

Re: Customizing output of values?

by David Saylor -

Hey Marco,

You also need to join your grades table to the grade item and activity (right now, you're only joining on user so the actual grade it pulls in will be somewhat random).

Should add the following lines lower in the list after you add your quiz table (if other activities have grades too you'll need joins for each). This will replace the current join you have for grades.

LEFT JOIN prefix_grade_items gi ON gi.itemtype = 'mod' AND gi.itemmodule = 'quiz' AND gi.iteminstance = q.id
LEFT JOIN prefix_grade_grades gg ON gg.itemid = gi.id AND gg.userid = u.id

I didn't actually test this so I may have a typo or something somewhere.

Average of ratings: Useful (1)
In reply to David Saylor

Re: Customizing output of values?

by Marco Frischknecht -

Thanks, David, I managed to get it to work. All that was missing was a grade_items id, though I feel it might be redundant. Because my report also needs to list anyone who has seen an activity, yet hasn't taken the exam, I added that condition as well. You've been very helpful, you have my thanks. I'll be linking the code below in case anyone else wants to use it or modify it for their needs. You'll have to find the correct ID values for your activities in the database, but that should be straightforward enough.

User - Course Details SQL Query

Average of ratings: -