[SIMS] Reporting Weekly Attendance

[LosOjos]

Hi all,

I've been tasked with producing a report that gives a week by week breakdown of students percentage attendance.

Sounds simple - wish it was!

I'm aware that there are lesson monitor reports that do this, but what I'm aiming for is a spreadsheet with all the pupils listed down the rows, with weeks 1 - 52 (or however many weeks there are in a school year; haven't done that calculation yet!) listed along the columns so you're left with a big table containing all weekly attendance and overall attendance to date.

Exporting the lesson monitor reports is tedious, requires a lot of copy & pasting and throws up problems when you get to a week where a child has left so the rows no longer automatically line up any more...

So my first thought was to pull all the data in to Access and run some reports on it there. Didn't work, just got me frustrated.

My next plan is to develop a simple program that will churn through the data and dump out a CSV when it's done, but before I do that - has anybody already done something similar or know of some obvious, easy way of doing this that I've overlooked?

Don't really want to spend the afternoon coding this if somebody else already has!

[vikpaw]

If you can find a lesson monitor report that can do it, then export to xml and manage that file, might save you some work.

[LosOjos]

If you can find a lesson monitor report that can do it, then export to xml and manage that file, might save you some work.

Thanks Vik, hadn't thought of that. The Lesson Monitor reports don't contain all the info I need, but they do cut down the amount of data that needs to be extracted dramatically. The downside is they can't be run from CLR, which means trying to get staff to run the report each week themselves; which means I'll end up doing it!

One to think about though...

[vikpaw]

Thanks Vik, hadn't thought of that. The Lesson Monitor reports don't contain all the info I need, but they do cut down the amount of data that needs to be extracted dramatically. The downside is they can't be run from CLR, which means trying to get staff to run the report each week themselves; which means I'll end up doing it!

One to think about though...

Yes it's a shame they can't be scheduled or referenced. i feel a change request coming on...

[LosOjos]

Yes it's a shame they can't be scheduled or referenced. i feel a change request coming on...

If they could be run from CLR, that'd be perfect! Although I imagine that would mean major changes to the CLR program and/or the nature of the LM Reports themselves... Still, I can dream!

[Sivadam]

What do you need the data for?
I ask this question because I was asked to do a similar task. When I asked 'what for?' I was told it was needed so that the Tutors could keep a regular check on the members of their group!

My response was that this report was not needed at all! I pointed them to the Attendance Panels on their Homepage. Each Tutor now has 3 Attendance panels:-

• Whole School for whole year
• Reg Group for whole year
• Reg Group for previous week

Some actually have a fourth for Reg Group for previous day!

[LosOjos]

What do you need the data for?

I learnt long ago that "why" usually saved me a ton of work!

In this case though, it's genuine - the SLT in charge of attendance wants a week by week breakdown so he can easily see who's attendance is improving or degrading and intervene as necessary

[PhilNeal]

Discover may be able to assist you here. Dynamic groups can be formed using a variety of critera so for example <93% attendance. You can then ask SIMS to send a message whenever a child enters or leaves that group so you don't have to go looking.

Dynamic groups are available in Assessment Manager and the Report Generator so you can construct really comlplex queries visually and then use the result to filter lists.

[Sivadam]

Hi Phil,

I have mentioned to Chris a couple of times that our LA sets the targets for the school in terms of Attendance. The problem is these targets are usually not integers, which they have to be in Discover. The LA could set a target of (say) 95.6%. It would be brilliant if we could set the targets to decimal places in Discover.

I guess that other LAs do similar???

[PhilNeal]

I still think that this would work in terms of being informed of changing patterns of attendance around a threshold. I'll speak to the team about non integers; I suspect the problem is keeping Discover as simple as possible and as soon as you require the user to type something in you've lost it!

[CAM]

I sort of do this each week with reports and macros. Made a few tweaks to the ReportData.xls macro, recorded the actions to put it's output in a PivotTable. A bit of trial and error later, my heads of year can run the report and instantly get a summary of attendance sorted by Year > Reg > Name. Unfortunately it is only Year to Date attendance.

The thread Vik gave me is here. Macros and PivotTables are definately worth learning if you want to run instant reports on a regular basis though.

[LosOjos]

Discover may be able to assist you here. Dynamic groups can be formed using a variety of critera so for example <93% attendance. You can then ask SIMS to send a message whenever a child enters or leaves that group so you don't have to go looking.

Dynamic groups are available in Assessment Manager and the Report Generator so you can construct really comlplex queries visually and then use the result to filter lists.

Thanks Phil, I hadn't thought of using Discover! That could work here, I'll suggest it to the SLT in question.

I sort of do this each week with reports and macros. Made a few tweaks to the ReportData.xls macro, recorded the actions to put it's output in a PivotTable. A bit of trial and error later, my heads of year can run the report and instantly get a summary of attendance sorted by Year > Reg > Name. Unfortunately it is only Year to Date attendance.

The thread Vik gave me is here. Macros and PivotTables are definately worth learning if you want to run instant reports on a regular basis though.

Cheers @CAM, the problem is that PivotTables only handle numerical values - in order to get weekly attendance in a report it's going to require some analysis on the actual mark (there is a field in third party reporting that gives a numerical representation the week though, so that helps). Unfortunately, I don't think Excel will be able to handle the sheer volume of data that will be pumped in to it...

[LosOjos]

Well the tool is nearly ready, it's working on test data at least. Just want to make it download via CLR now (I'm not in work today) and maybe add a GUI.

Would anybody else be interested in this once it's ready?

EDIT: I've stupidly based it on AM/PM [as opposed to lesson marks] so it's very vague to make matters worse, lesson marks don't have a week ID attached so I'll have to write a routine in to calculate it shouldn't be much longer now...

[vikpaw]

Just noticed this on SupportNet so as an alternative, this change request will hopefully help get lesson monitor reports accessible for scheduling, and hopefully that process will also make them available for CommandReporter
1009-773373

[LosOjos]

Just noticed this on SupportNet so as an alternative, this change request will hopefully help get lesson monitor reports accessible for scheduling, and hopefully that process will also make them available for CommandReporter
1009-773373

Cheers Vik, I've +2'ed it.

Further to my previous message, it turns out AM/PM attendance is fine. Final tweaks I'm adding are to have a cumulative attendance column and filters to set the date range and effective date (just building the GUI side to that now).