+ Post New Thread
Results 1 to 10 of 10
MIS Systems Thread, Termly Percentage Attendance Report in Technical; I can virtually hear the sighs rattling around Edugeek as some of you read that topic title... here's what I've ...
  1. #1
    Marci's Avatar
    Join Date
    Jun 2008
    Location
    Wakefield, West Yorkshire
    Posts
    895
    Thank Post
    84
    Thanked 235 Times in 194 Posts
    Rep Power
    82

    Angry Termly Percentage Attendance Report

    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!

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,498
    Thank Post
    1,456
    Thanked 1,201 Times in 814 Posts
    Rep Power
    712
    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?

  3. 2 Thanks to LosOjos:

    Marci (22nd May 2014), simpsonj (19th August 2014)

  4. #3
    Marci's Avatar
    Join Date
    Jun 2008
    Location
    Wakefield, West Yorkshire
    Posts
    895
    Thank Post
    84
    Thanked 235 Times in 194 Posts
    Rep Power
    82
    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.

    Ta muchly fella!

  5. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,498
    Thank Post
    1,456
    Thanked 1,201 Times in 814 Posts
    Rep Power
    712
    No problem, glad you found a solution that works for you

  6. #5
    Marci's Avatar
    Join Date
    Jun 2008
    Location
    Wakefield, West Yorkshire
    Posts
    895
    Thank Post
    84
    Thanked 235 Times in 194 Posts
    Rep Power
    82
    Are you on supportnet fella? I'm involved in a tricky one at the moment you may be able to offer assistance on...

  7. #6

    Join Date
    Jan 2012
    Posts
    679
    Thank Post
    264
    Thanked 98 Times in 81 Posts
    Rep Power
    27
    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!

  8. #7

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,498
    Thank Post
    1,456
    Thanked 1,201 Times in 814 Posts
    Rep Power
    712
    Quote Originally Posted by Marci View Post
    Are you on supportnet fella? I'm involved in a tricky one at the moment you may be able to offer assistance on...
    I am, haven't been on in ages mind! Where's the thread and what's it called? (Assuming links still don't work properly to SupportNet!)

  9. #8
    Marci's Avatar
    Join Date
    Jun 2008
    Location
    Wakefield, West Yorkshire
    Posts
    895
    Thank Post
    84
    Thanked 235 Times in 194 Posts
    Rep Power
    82
    Indeed! Attendance & Lesson monitoring > Lesson by lesson stats (week & year to date)

    Cheers d00d!

  10. #9

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,498
    Thank Post
    1,456
    Thanked 1,201 Times in 814 Posts
    Rep Power
    712
    Quote Originally Posted by Marci View Post
    Indeed! Attendance & Lesson monitoring > Lesson by lesson stats (week & year to date)

    Cheers d00d!


    Certainly looks like a good one! My brain has had it for this week but I'll pick it up Monday morning and have a look for you, couple of ideas trying to form already despite the Friday haze...
    Attached Images Attached Images

  11. #10
    Marci's Avatar
    Join Date
    Jun 2008
    Location
    Wakefield, West Yorkshire
    Posts
    895
    Thank Post
    84
    Thanked 235 Times in 194 Posts
    Rep Power
    82
    Awesome!

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 5
    Last Post: 23rd March 2013, 01:30 PM
  2. [SIMS] Lesson Monitor Percentage Attendance Report
    By saundersmatt in forum MIS Systems
    Replies: 3
    Last Post: 14th May 2012, 10:53 AM
  3. [SIMS] Sims Lesson / Attendance Report
    By IrritableTech in forum MIS Systems
    Replies: 10
    Last Post: 4th May 2012, 01:38 PM
  4. [SIMS] Previous School Attendance Report
    By LosOjos in forum MIS Systems
    Replies: 2
    Last Post: 21st September 2010, 01:04 PM
  5. SIMS Attendence reporting
    By Stuart_C in forum MIS Systems
    Replies: 6
    Last Post: 3rd March 2009, 11:16 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •