I can virtually hear the sighs rattling around Edugeek as some of you read that topic title... here's what I've just posted o'er on SupportNet - thought I'd throw the question out on here too.
I'm a bit stuck trying to get some data out of Lesson Monitor. Can anyone suggest the best way, or better yet share a report via UserFiles that displays the following...?
UPN > Name > RegGroup > YearGroup > PPIndicator > Autumn Att% > Spring Att% > Summer Att%
I've tried various methods after realising the %Attendance (Current Year) field was accumulative with no option for date ranges.
First step was via Discover - Percentage Session Attendance by Half Term. Problem? This shows 0 for every half-term. Not a clue why. I've done a transfer of data etc. Makes no difference. Tried as 3 different users on 3 different PCs, all produce the same data. Any other graph has content. This one, just flatline 0 for every bar.
SO, I wrote that idea off for now (pending a support call to Capita to resolve it).
A regular designed report doesn't allow for date ranges unless I use a filtered Attendance Marks SubReport, so that's what I did - added 3x Attendance Mark sub reports, each filtered to the term that I wanted the data for.
That lays all the marks out vertically in rows rather than horizontally alongside the student and means a few days work to get it laid out as desired when we're talking about 2000 students... and my PC took 3 hours trying to compile such a report for Yr7-11 and then eventually just crashed.
Then I discovered what I thought was the answer... the Student Analysis by Attendance Category Report.
This gives us all we need, other than Pupil Premium. Problem? It (Stu Analysis report) lacks a unique identifier per pupil - no admissions number or UPN etc. This prevents us from having a separate worksheet with pupil premium and doing a vlookup once we've gotten everything into Excel.
Thought we could concatenate Name and RegGroup fields to use that as a unique identifier to vlookup against, but SIMS is hugely inconsistent: In a designed report, if reg group contains an underscore, it retains it. In the Lesson Monitor Student Analysis report, the underscores are stripped out of the reggroup... unless that regGroup code begins with a number.
Ultimately, to get the two sheets to a state where they have a matching unique identifier per student involves some horrendously convoluted nested excel formulae.
This is all MASSIVELY infuriating and thus, after a wasted day of chain-smoking and swearing vociferously, I'm opening it up to you guys for suggestions!
This is one of those SIMS problems that make me want to scream; it's such a simple thing to do in SQL that I'm frankly amazed there isn't a predefined report for it.
Moaning aside, there are ways to get what you want, but it's not exactly simple.
What you need to do is design a Student report with all the student details you'll need in it (I tend to put in every type of useful data I can think of to save me having to redesign the report when someone says "it'd be great if their ethnicity was on there too!"). Then, add an "Attendance marks" sub report. You can then filter this sub report on a date range, allowing you to pick out just the half term you want. You can add as much/little detail as you like here, but at a minimum you'll want "Statistical meaning". While you're at it, use the subreport filters to exclude "Attendance not required" marks (it'll make your calculations easier in Excel if these are excluded).
Once you get that data in to Excel, you can use a Pivot Table on "Statistical Meaning", set the display type to "percent of row" and then you'll have the percent attendance for the given date range.
There are a few ways you can go from there; either run the report for each term, then use Vlookups from a final sheet to pull the termly attendance for each student in to a new report. This is the easiest, but involves the most work each time it needs to be done.
The way I'd do it would be to write some macros to manipulate the data for me, and pull all the marks out on the report (i.e. the year to date). Then you have your macro create a list of students from their admission #/UPN/ID (I always recommend ID, as guest students get no admission number and UPNs can change or may be missing, whereas the Person_ID is consistently available and unchanging). Then it's case of populating columns with count/countifs to calculate the percentage attendance for each term based on data range. It'll take a little while to set up and the final report will probably only be manageable when run a year group at a time, but it will be easiest in the long run for whoever has to use it.
I have a couple of reports similar to the macro one I mentioned that I could manipulate for you to deal with attendance if you like?
Did it a bit more basic, but in essence, as you've stated. Add all possible info fields for the student that they'd be likely to need...
Added attendance marks subreport, but with no fields, just (count). Set filter for date range (autumn) & check the prompt box, and filter for Satistical meaning of present+AEA. That gives actual attendance.
Add another attendance marks sub report with no fields, set filter for same date range as above & check prompt box, and filter for statistical meaning of everything other than attendance not required. That gives max possible attendance.
Repeat both the above for Spring date ranges, and Summer date ranges.
When report is run, give it the term date ranges and select the year groups required.
Then in excel, (actual att / max poss att) *100 = term percentage. Just add 3 columns and populate with that referring to figures for each term.
It's crazy given how much we all pay to capita that if you stray slightly off the beaten path you're back to piddling around with vlookup in excel when you have such an incredibly powerful database in SQL. Gah!