Markbook query

Greetings to you, dear friends.

I want to create a ‘master markbook spreadsheet’. What I mean is a spreadsheet that lists all the students in rows and creates columns for all markbook columns.

The headings can be something like this:
Surname Firstname DOB username gender {for each course, Current Year Coursename Attainment Effort Comment}

I would like assistance with the query. Help!


I guess this is doable in some way, but the logic breaks down a little as Markbook columns are specific to a particular class, and so you will end up with very many blank columns.

We do something similar with this for our major assessment data, but it runs through the Formal Data module, which then allows you to export to Excel. The distinction we make is that the Markbook is for ongoing, teacher led assessments, where Formal Assessment is for school-wide assessment. Would this be of any use to you?


Hmmm…The issue encountered with formal assessment was that, unlike markbook, the formal assessments were displayed in a long list. It is quite difficult to distinguish between school-wide assessment 1 and school-wide assessment 2 etc.

The possibility of working with many blank columns is not a major issue, especially if the query allows us to specify the date range. Is it possible?


I think we’ve already addressed this in v14 development, as we have added the course name as the first column. Does this address your concerns?



Hi Ross,

I am just looking for an SQL query that can list all the students and put all the markbook scores across all courses in columns, like a broadsheet of scores.

What I really need is something like ‘Markbook Entries for Year Group (All Historic)’ in query builder, but with each course displayed as a column. Let’s know if it can be done please.

Sample heading:
Surname, Forenames, dob, username, Coursename, MarkbookColumName, AttainmentValue, Effort, Comment…Coursename, MarkbookColumName, AttainmentValue, Effort, Comment…Coursename, MarkbookColumName, AttainmentValue, Effort, Comment …etc

Tayo, this would be difficult to do, as different classes can have different columns. The only way I can think of doing it is if you use the multi column creation function, and then we only return the columns that are common across all the students. Either that, or there will potentially be a lot of empty columns. What do you think? Ross.

I see your point. Okay, is it possible to create have put date ranges in this query?

Markbook Entries for Year Group (All Historic)

In other words, can we list all historic markbook entries for the year group between two dates?


Thinking about this some more, I don’t believe it is possible with SQL, in terms of students in rows and results in columns. This would have to involve some PHP, and is beyond the scope of what I can offer right now in terms of customisation. The Tracking module, under the Data Points option, offers something like this, but only for Internal Assessment.

I have, however, added a new Query Builder query called Markbook Entries for Whole School Over Past Month which returns all results. Sync your queries and take a look if it helps you at all.