Activity Completion Report Formatting

Activity Completion Report Formatting

by Alan Yeodal -
Number of replies: 11

Hi,

We are using the Activity Completion report downloads to periodically produce reports for managers of our charity. One really annoying aspect of this report is that (although it is called 'Excel Compatible') the dates are not recognised as such when opened in Excel. We want to be able to sort by date in these reports. 

Currently the reports are outputting as a date/time (e.g. Thursday, 28 May 2020, 3:16 PM) We need to be able to sort by date.

I know this is an issue that has been around since the dawn of Moodle so I'm sure someone in the Moodle community will have come up with a solution! smile 

Many thanks,

Alan.

Average of ratings: -
In reply to Alan Yeodal

Re: Activity Completion Report Formatting

by David Keeler -
Will this help?

DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%m/%d/%Y %T') AS 'Completed Date'
Average of ratings: Useful (1)
In reply to David Keeler

Re: Activity Completion Report Formatting

by Rebecca McAllister -

Please excuse my ignorance but how would that help and where would I put it?  We are pulling the Basic report (report.php) and this incompatible date is an issue. Help?


Average of ratings: -
In reply to Rebecca McAllister

Re: Activity Completion Report Formatting

by David Keeler -
Hya Rebecca,

So we run several reports that will help track the things we need. 
  I underlined and bold the 'date line'  This generated the date and time they completed the course. This prints out to look like this:
     08/10/2020 07:52:04

Our SQL report setup looks like this:

SELECT
c.shortname AS 'Course Name',
u.lastname AS 'Last Name',
u.firstname AS 'First Name',
u.email,
uid.data AS birthday,
uid2.data AS status,
uid3.data AS officercode,

DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%m/%d/%Y %T') AS 'Completed Date'

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
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id
JOIN prefix_user_info_data AS uid2 ON uid2.userid = u.id
JOIN prefix_user_info_field AS uif2 ON uid2.fieldid = uif2.id
JOIN prefix_user_info_data AS uid3 ON uid3.userid = u.id
JOIN prefix_user_info_field AS uif3 ON uid3.fieldid = uif3.id

WHERE
c.enablecompletion = 1
and uif.shortname = 'birthday'
and uif2.shortname = 'status'
and uif3.shortname = 'officercode'
and from_unixtime(p.timecompleted) >
date_sub(now(), interval 1 day)

GROUP BY u.username
ORDER BY c.shortname


From there, I can download it in excel format and manipulate the dates based on sort order.

Dave
Average of ratings: -
In reply to David Keeler

Re: Activity Completion Report Formatting

by Rebecca McAllister -
Thank you for your response. I wondered if it was a SQL. I wish it was fixed in Moodle so this wouldn't be an issue.  What good is a basic report in EXCEL if it can't be manipulated??? 

I will see what I can do for this client by setting up a SQL report that will give him the information he needs. 

Thank you, again!

Average of ratings: -
In reply to Rebecca McAllister

Re: Activity Completion Report Formatting

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
I am jumping into this conversation a little late, so sorry if I am a little off-topic.

I think what Alan mentioned is that the "date" is not correct in Excel. Then, I think David suggested writing some custom SQL. Via custom SQL, the date can be manipulated in any way you wish. Then, Rebecca, I think you might not want to write the custom SQL and again mention Excel's inability to manipulate the date.

But there seems to be two viable solutions: manipulate the date in SQL, or manipulate the date in Excel. In Excel, one just needs to write an equation to manipulate the date. There are many ways to do this (search the Internet for suggestions.)
Average of ratings: -
In reply to Rick Jerz

Re: Activity Completion Report Formatting

by Rebecca McAllister -
Thank you, Rick.

It seems that Excel doesn't recognize the date even when you tell Excel that it's a date so the Excel spreadsheet downloaded from the Basic Report can't be manipulated and you can't create any formulas.

This is what the client and I both ran into. Surprise! I can't believe this issue hasn't been resolved after all these years but oh well! I had to create a custom SQL for the customer which did work and satisfy his needs.
Average of ratings: -
In reply to Rebecca McAllister

Re: Activity Completion Report Formatting

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
You would need a function to extract the date as you desire.

Did you search the Internet for "Excel extract date from text date"?
Average of ratings: Useful (2)
In reply to David Keeler

Re: Activity Completion Report Formatting

by Alan Yeodal -
Thanks for all your responses on this. Much appreciated.

Sadly the suggestions are outside my own skill set. I only have access to the Moodle admin panel and not the server-side databases.

I was hoping someone could suggest a way of converting the dates (e.g. Thursday, 5 December 2019, 6:02 PM) in the 'Excel Compatible' completion reports into something that can be sorted by date in Excel. I have tried the suggestions for conversion in excel but have failed to get them to work.

Failing that, If someone could help giving me an overview of where and how in Moodle (front-end or back-end) we can access the report data for manipulation I may be able to find the right person in our charity team who has the access rights and database/excel skills to help with this.

Moodle was chosen by the charity I work for as the best solution for supporting our volunteers and staff and it meets our needs in almost every way. However, not being able to easily extract timestamped/sortable reports for our mandatory training is starting to become a big issue.

Many thanks once again.

Alan.
Average of ratings: -
In reply to Alan Yeodal

Re: Activity Completion Report Formatting

by Shirley Gregorczyk -
Picture of Particularly helpful Moodlers
Excel 101 -
Insert some columns next to the Date/Time column.
Select the Date/Time Column. Select the Data tab. Select the Text to Columns dropdown list.
Select the Delimited radio button.
Select the Next Button
Select the comma checkbox.
Select the Next button.
Select the Finish button.
Return to your Excel workbook and delete any unwanted columns.
Kind Regards
Average of ratings: -
In reply to Shirley Gregorczyk

Re: Activity Completion Report Formatting

by Rebecca McAllister -
Your solution didn't change all the dates consistently (Lost about 10-20% that didn't convert properly), so in a large spreadsheet, it isn't really a good fix. Definitely, not an answer I can give to a client, manager or instructor. 

Note:  This is hardly Excel 101 but that sarcasm on your part is probably not as important as a solution for the next person looking for a fix or a request for a system fix for an issue that has been mentioned a few times over the years in this forum.  

Notice, it is missing the comma after the day in the Excel download (in the basic report for each course). I am guessing that is at least part of the issue?

This is NOT an issue in the Ad-hoc report which formats the date and time properly. (FROM_UNIXTIME(gg.timemodified) AS TIME). Creating an ad-hoc report is what I had to do to keep the client happy. 

Not all dates converted properly
Average of ratings: -
In reply to Rebecca McAllister

Re: Activity Completion Report Formatting

by Shirley Gregorczyk -
Picture of Particularly helpful Moodlers
Sorry if you took my answer as unhelpful and sarcasm. I used it on a very large workbook, and had no issues, I have been using Text to Columns on various LMS applications for decades.
Average of ratings: -