MattMitchell

As far as reforming the CR system goes, I'd suggest that someone from Capita checks CRs and flags them as - Already Implemented or - Planned for XYZ release, as there a quite a few in there that have been implemented already. Maybe that would help, as would an overall list of the most and least popular CRs, across all modules, in order to see what's most/least likely to get done...

The original intent seemed to be driven more in terms of "can we prove they did it?", so although proving that someone hadn't deleted the records would prove they're innocent, I'd bet money the school wanted to gather evidence to support a dismissal for gross misconduct. Personally, in order to prove it either way, I'd want to see the records that were deleted, as, without full docs on how SIMS interacts with the database, deleting records per se may not be evidence of anything inappropriate anyway.

It would, but I've not checked into the implications of backing up and restoring the transaction logs elsewhere. I think (=don't know) that transaction log entries reference physical points within the file structure, which might be changed by an operation like that. Best way is to test it I suppose - any volunteers?

Interestingly, this tool Apex SQL Log looks like it'll show the info you want. It does install custom procedures into your master database, so it's one to run past whoever supports your DB server, and also to check with Capita/your LA as far as whether this is acceptable to do on a live SIMS server.

Capita Supportnet - once you've logged in, click on "Support" then "Change Requests" and search by that number

Anything can do it - you could even rewrite all the tables in the SIMS db as temporal tables with the relevant triggers, and create views over the top of these to expose the tables to the Capita stuff (obviously, not a recommendation before someone goes and does it!). But how much information is available - do you get a "before and after" view of any record that has been changed, added or deleted? Do you just log which columns have changed, but not the data? There are lots of applications that store partial or summary audit logs (e.g. failed login attempts, "record changed" info, etc), and it's not difficult at all, no. But a full, historical database isn't quite as common in software. Normally, I'd agree, but considering that we're in an industry where people get upset because they've just upgraded to five-year-old version of MSSQL, and will now have to upgrade to a two-year-old version, is it likely that customers will need to upgrade their hardware if the server part of the application is more resource-hungry? There are often complaints about SIMS's being slow, implementing something like this would slow it down more. Having an audit trail is essential in order to prove or disprove) foul play. But, since we're talking about having enough concrete, conclusive evidence to justify dismissing a staff member on the basis of the data alone, you would need a full change record of all the data. You'd need to prove What data was there previously, including all the field values Who entered the data, and why What data was deleted or changed by the staff member involved What data is deleted by other staff, and how often What data is changed by other staff, and how long after the original event is recorded Does Facility log information to that kind of level? There are applications that do it, but I'd be surprised if a school MIS did.

As far as the SQL goes, and assuming that you're working on a copy of your SIMS database, not the real thing, the query for finding how many behaviour events a student has this year (in a very basic form) looks like this: USE GO SELECT SP.person_id, COUNT(*) AS entries FROM sims.stud_behaviour B JOIN sims.stud_behaviour_student_link BSTU ON B.behaviour_id = BSTU.behaviour_id JOIN sims.sims_person SP ON BSTU.student_id = SP.person_id WHERE SP.forename = N'Enter Forename Here' AND SP.surname = N'Enter Surname Here' AND B.event_date >= 2009-09-02 GROUP BY SP.person_id However, as has been noted, there is not an audit trail in the database for behaviour logs. This isn't really a surprise - the kind of logging people are suggesting can be very expensive in terms of processor time, developer time, RAM, disk space, etc, etc. Basically, every time a record is inserted, updated, or deleted, the previous record (for updates/deletes) needs to be stamped with the user carrying out the action, and a timestamp. Then, a new record has to be inserted (for inserts/updates) with the new data. Oh, and then every time you retrieve anything from the database, you have to check for the newest copy of everything, before you can start to do the work of the normal query processing. We have 1500 on roll, and six periods per day, so session and lesson marks combined make 12000 records per day; about 1 million assessment results per year (OK, it's a lot, but that's another story and a lot of them are calculated rather than entered) so on average 5000 per day; throw in a hundred for achievement and behaviour incidents, and you get something in the region of 13,000 to 20,000 new records created every day - and that's without counting the deletions and changes that people are talking about recording! It's just too much storage and processing for most people, and although it's easy to say "SIMS should have an audit trail", and I do share that opinion up to a point, it's not really practical or commercially viable. Schools would have to upgrade their SQL servers massively in terms of RAM, CPUs and disk space, and probably add a second server just to record the audit data (which is generally good practice in an ultra-secure tracking environment). It may be that there's a way of querying the transaction logs on the server, but my gut feeling is that you can't get the info you're after. There is a way to track changes to data in tables built in to MS-SQL from 2008 on (Using the "Change tracking" functions/statements), but 1) it won't track who did it or when (just in terms of in what order) 2) it's turned off by default, so you'd have to turn it on BEFORE you can start tracking changes 3) you have to change database and table properties, which probably comes under the heading of "things that are unsupported and chargeable to fix by Capita" 4) it won't tell you who did it anyway! The DBCC LOG('', ) command will give you the contents of the transaction logs, but on its own it won't help (try it in SQL Management Studio and see!). You can probably buy some software to let you view the logs, but it's probably not cheap. Alternatively, you could run a command line report every hour that outputs behaviour events, and linked students, so that you could then look at deletions over time. If you're doing that, the best way is to create a user with 3rd party reporting rights in System Manager, and make sure you have the behaviour_id field in the output (this is a primary key to the table of events). The report option will avoid all those nasty licensing and database corruption risk issues, and will basically give you the same information (albeit needing a little more work). Is disabling the staff member's access to behaviour management an option? It may be that it's not appropriate to their role to be entering these events, which might be a way of doing things. I agree entirely that deleting data entered by other staff would qualify as gross misconduct, but I don't think you could prove it through the data on the system. Stepping away from the technical side of it all, though, what evidence do you have that the staff member has been deleting the records? You may not be able to show it through SIMS, but if you have written evidence from several members of staff, on several occasions, that they entered behaviour codes in and those for a particular student vanished, you'd probably have cause to investigate further, maybe using some kind of logging/monitoring software on the relevant computer. As it stands, the event sounds equivalent to a "no witnesses" scenario.

If you re-imported CTFs with different leaving dates would they change in SIMS? If so, lemme know - I'd probably knock you up something to transform the ctfs as a freebie...

You'll be fine on the formula bits - those always reference other columns by position, so changing the aspects will work fine!

If you're just after the answers, a pivot table's the quickest way to do it: use the abcd column as row headings, and a count of the x column for the data area. Takes about 15 seconds!

To be fair, it's harder to find good PGSQL engineers than it is to find good MS-SQL ones - and refactoring SIMS to work with a different DB engine would be a mammoth task.

Looks nice, wouldn't mind an iphone/mobile-friendly version like there used to be though...

That occurred to me too, Phil. Certainly, the one area where most MIS software is lacking is in providing ready-made analyses of the kind that schools actually want; marketing materials from the various vendors (Bromcom and Capita included!), alongside blanket questionnaires like this, suggest that this is unlikely to change in the immediate future. I don't really want to write a full analysis suite spec for free, but I will give some tips: Have a read through the literature - there are many guides online to analysing assessment data available, which will give you an idea of typical foci of interest. The national strategies sites, forums such as this one, LA support sites, etc, have guidance on how to measure performance, and what to look for. Talk to some schools, i.e. go and visit them and see what they do - then you can think about how to solve the problems they're having to solve themselves at the moment Look through the feature requests for products like TeachersWebFolder - would any of these fit your brief? Have a read through DCSF and Ofsted guidance on how to assess the effectiveness of schools and LAs - this will give you an idea of the kind of questions schools will need to answer It's going to be difficult to make anything easy for schools for as long as MIS software allows users to store assessment data the way they want. A lot of the time, schools tend to be very focussed on colour-coded lists of students: a quick search online will show you what they're asking about! If this is a sanctioned project at Bromcom, you may want to look at getting in a few consultants (some who are "data-focussed" and some who are "anti-data") to discuss what they think would be helpful. Is this a Bromcom thing? I would imagine the firm is big enough to pay a team to develop a solution, and not just to let a new analyst "have a go" based on what they can find in a discussion forum... Overall, the kind of questions you need to answer are: What's going wrong, and for which subject/students/groups of students? What's working, and (again) where is it working? How did we do in the past? How will we do in the future? Where do we need to improve in order to hit XYZ threshold target?

Press f9 to recalculate will sometimes fix it

I don't think it's directly possible, but what you could do is script a handler for adding new items to the calendar, or creat a scripted custom "new event/task" button that manages the input and selects the right timeslot for you.

Who are the speakers, and what are the timings like?

I think you're five hours ahead though!

You imagine parsing a full set of data on 1500 students, once it's in the SIF message format? It'd be gigabytes!

Is it sad that I'm pleased about this?

I've not seen what the new reporting system will do, but from the phrase "using SQL Server's Reporting Engine", I'd assume that it's a step towards the ability to create more complex/advanced reports, and possibly hook in business reporting tools to do them. The reports that exist at the moment are based around the concept of exporting data returned by a view in the database to a text/xml/other file; there wouldn't be a need to change or remove this, as the new stuff will not need to be linked to it! To be honest, I think the new functionality is more a question of things like better analysis (cross-tabulation) reports. I read the "NT4-style" reports as "NT6 won't give you the reports you're used to getting from NT4, so we'll make these available as a view in the main reporting section" rather than a change in actual functionality, but it's hard to tell without the new release!

Except that $opt is an array containing one object, which is also an array, so the return will be an array called $field_1.

How about having a directory for each client setup? It's likely that commandreporter will use connect.ini from the current directory.

Have you tried just outputting the values of your variables in a response page to see what's coming out? I'm with webman, I think that fault will either be set to the string "True", or to a numeric value equivalent to True (normally -1, 0 or 1 depending on the language).

It's because the array contains all the records returned by the query, not just "a" record. You need $opt[1]["subjectid"].

You need to change it round a little bit... First off, on the aggregate part, yes, anything that's not an aggregate function, i.e. COUNT(), FIRST(), MAX(), MIN(), SUM(), needs to be listed in the GROUP BY clause. Achievements.ADate and Behaviour.BDate also come under this category. But secondly, you'll get too many points! The trick is to think about what rows you would see before you do any grouping. If you try this query: SELECT S.Forename, S.Surname, A.ADate, B.BDate FROM (Students AS S INNER JOIN Achievements AS A ON S.Admission = A.Admission) INNER JOIN Behaviour AS B ON S.Admission = B.Admission; then you'll see what Access is grouping for you: it's all records from Students, with all records from Behaviour that match, PLUS all records that match from Achievements, in all possible combinations! Say, for a given student, there are 12 behaviour entries, and 20 achievements entries, you'll actually get 12*20 = 240 lines back! One way to do it is with a few queries. One query calculates the numbers behaviour points for each student, one does the same for achievement, and then a third joins these onto the students table to show the numbers. (You can skip a step and make it two queries, but it's not as "clean"). query BehaviourCounts SELECT Admission, COUNT(*) AS BehaviourPoints FROM Behaviour WHERE BDate BETWEEN [Forms].[ReportFilter]![txtStartDate] AND [Forms].[ReportFilter]![txtEndDate] GROUP BY Admission; query AchievementCounts SELECT Admission, COUNT(*) AS AchievementPoints FROM Achievements WHERE ADate BETWEEN [Forms].[ReportFilter]![txtStartDate] AND [Forms].[ReportFilter]![txtEndDate] GROUP BY Admission; query SummaryInfo SELECT S.Admission, S.Forename, S.Surname, S.Reg, Nz(A.AchievementPoints, 0) AS AchevementTotal Nz(B.BehaviourPoints, 0) AS BehaviourTotal FROM (Students AS S LEFT OUTER JOIN BehaviourCounts AS B ON S.Admission = B.Admission) LEFT OUTER JOIN AchievementCounts AS A ON S.Admission=A.Admission; That way, you can add as many fields from Students as you want, without the grouping issue. You need the left outer join so that students with no behaviour codes, or with no achievement codes, will still appear in the query. If you do it all with an INNER JOIN, then you'll only see students with an entry in all three tables. The Nz() converts a NULL to a zero, i.e. any student who has no negative behaviour points will come up as having a 0, rather than a blank value. Another way to do it is with subqueries, which has the benefit of only needing one query, but makes it a bit less readable. I suspect, although I've not checked, that Access doesn't optimise these all that well either, but try it and see how it works for you: SELECT S.Admission, S.Forename, S.Surname, S.Reg, (SELECT COUNT(*) FROM Behaviour AS B WHERE B.Admission=S.Admission AND BDate BETWEEN [Forms].[ReportFilter]![txtStartDate] AND [Forms].[ReportFilter]![txtEndDate]) AS BehaviourTotal, (SELECT COUNT(*) FROM Achievements AS A WHERE A.Admission=S.Admission AND ADate BETWEEN [Forms].[ReportFilter]![txtStartDate] AND [Forms].[ReportFilter]![txtEndDate]) AS AchievementTotal FROM Students AS S; One advantage to this approach, is that you could run it as a parameter query. But it does look nasty (especially in Access which reformats your SQL). In terms of handling the date thing, if you start running multiple queries as part of a reporting procedure, you may like to store the date range in a separate table, and have "views" (really just saved queries in Access) that only show behaviour/achievement points that fit in the date range, but that's something for another time! The BETWEEN operator can work differently in different SQL engines; "X BETWEEN A AND B" means "A<= X AND X <= B" in some engines, and "A < X AND X < B" in others, so check it's giving you the results you expect. Also bear in mind that dates are funny, and "24/02/2010" on its own tends to mean "24/02/2010 00:00" which is earlier than "24/02/2010 09:30". Play with some queries and check that you get the records returned that you're expecting to.