Clean Slate Report
I've been asked to write a 'clean Slate Report' for SIMS. Basically run a report to show which kids have got a present mark for every lesson and no behaviour points for a given week. I've been trying for a while but just cant seem to get it.
Any help much apprecaited.
I can give you my really bad way to do it.
There's probably a more elegant solution, involving pivot tables or something.
The report i've got, takes a long time to run, so it's worth doing it a year at a time, or maybe even smaller groups if you find it's still a chore.
Basically, focus on students, on-roll.
Add Name and Reg.
Add Sub-Table for Behaviour (blue icons at bottom of fields list). It shows up as a count if you add no fields from the sub-table. Use the red icon on the right to show the sub-query table and put in a date between filter. Ignore the greyed out settings, it doesn't matter if it brings back all kids.
Add Sub-Table for Lesson Marks. Again, don't pick any fields and it comes up as a count. Do a sub-query on date between AND mark type is one of '....' and choose all the absence codes, or the ones you want to penalise the kid for, including lates, if you want cleaner than clean.
Both of these count columns will now have filtered next to them, but you can rename them at the top for disambiguity.
Next page in wizard, if you add a filter of reg-group so it doesn't take half an hour to run, and send it to excel.
You just need to sort by both count columns. Those with zero behaviour and zero absence marks are the ones you want.
Not a great solution, i know, but it does the trick. You can probably get the result by focus on another area first, but i just do it this way.
Apologies for the bare basic instructions, not sure if you're familiar with sub-queries, but it's useful for others anyway.
Works a treat :D