[sims] Sims 'On roll' report showing students who are not on roll?

[iceman]

I have several SIMS reports that export data to xml files every night for further processing externally.

All of them are based on:

 

Data area: Student - Population: On Roll

 

They have all been running fine for a year or so.

 

On 9th May the external processing reported an error which I traced to the reports exporting four students who were not on roll and did not have an admission number or any attendance record. They had simply been entered into SIMS by the data team as being accepted for a September 2016 intake.

 

As a test I added the student status field to one of the reports and you can see that the status is not shown as 'on roll', so they shouldn't have been exported.

 

We are running SIMS Build 50 ver 7.168. Is this a bug introduced by the latest upgrade?

 

 

Below is the design of one of my reports and the preview showing the four students who should not be there

 

 

 

I think I may be able to do a workaround by explicitly adding a filter where student status is 'on roll', but I've never had to do that before. Any other suggestions?

[bobsmith]

Can you add their school start date to the report and have another look at the output?

 

I have a feeling that maybe that could be an issue here.

[jinnantonnixx]

Behind the scenes in the SQL tables, pupils on roll have a status flag of "OnRoll", "Main" or "Subsid". If they are not on roll, their status should be NULL (a real DBNULL, not the text 'NULL').

 

Just guessing, but perhaps the SIMS report checks for 'IS NOT NULL' and somehow those four pupils are not set with a NULL, neither are they set with a valid OnRoll flag. Just guessing, but it'll be the first thing I'd check.

Edited May 11, 2016 by jinnantonnixx

[iceman]

Hello Bob, glad you picked up my post.

 

It gets worse, when I added the admission date and leaving date I got some really strange dates - see below.

That would seem to indicate that the data team is wrong, they are not pre-admissions for sept 2016 but from much earlier.

But those students have never been exported before now and the report hasn't changed for ages.

 

I might add that I don't deal with the data entry side of sims at all, the data team do that.

My role is the analytical processing the data that is already in sims

 

[bobsmith]

they smell like guest pupils to me, can you find their individual records in SIMS and look at their enrollment status?

[iceman]

@jinnantonnixx Yes they look like they have a status of null, and a null admin number - that's what is causing the null conversion error in the external program.

How would I check what is actually in that field? After all an empty string could look like null on the screen. Come to think of it how could an empty string get in there anyway and why would it suddenly appear now?

[iceman]

No, that's also odd. They don't appear in the student search with status selected as , current, guest or any of the other possible values. They only appear in reports.

[bobsmith]

Can you find them in Applicants?

 

If not then you've got Ghosts in your database.

[jinnantonnixx]

@jinnantonnixx Yes they look like they have a status of null, and a null admin number - that's what is causing the null conversion error in the external program.

How would I check what is actually in that field? After all an empty string could look like null on the screen. Come to think of it how could an empty string get in there anyway and why would it suddenly appear now?

 

 

I don't know much about the SIMS report generator, but I know a bit about the back-end. This SQL will get you a similar list when run from query analyser in SQL Studio.

 

I've added an 'IS NOT NULL' clause to show you any blank, non-null fields as well (which you should never see)

 

Usual caveats, don't proceed if your not 100% confident with any of the steps you need to use SQL Query Analyser.

 

[Edit - SQL code removed]

Edited May 11, 2016 by jinnantonnixx

[iceman]

Thanks for that. I know a lot about SQL but don't know anything about the SQL Query Analyser tool - don't even know if we have it or where to get it.

However if that allows me to run SQL queries against the sims database that would greatly simplify my development work!

[jinnantonnixx]

Thanks for that. I know a lot about SQL but don't know anything about the SQL Query Analyser tool - don't even know if we have it or where to get it.

However if that allows me to run SQL queries against the sims database that would greatly simplify my development work!

 

Sorry, it's been a busy day. I typed 'SQL Analyser tool', but meant the 'SQL Query tool' (part of SQL studio). Apologies.

 

As ever, the recommendation is to run the SIMS report generator for data extraction. I'd advise raw SQL only for investigative work if you're totally confident with your abilities and the potential damage that can be done.

Edited May 11, 2016 by jinnantonnixx

[iceman]

Can you find them in Applicants?

 

If not then you've got Ghosts in your database.

No, the only place is in my reports and also they appear in the sims 'general student list report' We have contacted the ICT services for our county and they suggest marking those four as withdrawn in order to explicitly set the status flag. However that doesn't explain why they are suddenly showing up now, after an upgrade. I also notice they are now showing up in many other reports using population students on roll that are nothing to do with me eg ParentPay and GroupCall exporter to various external bodies like go4schools and sisra. I think we shall have to delete them.

[iceman]

Sorry, it's been a busy day. I typed 'SQL Analyser tool', but meant the 'SQL Query tool' (part of SQL studio). Apologies.

 

As ever, the recommendation is to run the SIMS report generator for data extraction. I'd advise raw SQL only for investigative work if you're totally confident with your abilities and the potential damage that can be done.

 

Ah, yes I understand. I'll have a go with that as it would be interesting to know what is really in that status field. Might have to be later though as I shall need to go into the school to use that (I work from home using remote access into the school). I'll stick with the exports for data extraction on the grounds that 'if it ain't broke'. Trouble is it looks like something in sims is broke!

[bobsmith]

So how are you planning on deleting them if you can't find them in the first place?

 

While SQL is fun and very powerful, with SIMS in particular it's a really bad idea to do anything other than look. (and even looking seems to upset the Capita bods.)

 

If the problem was caused by a user messing around then the solution will be through SIMS, if the database has been messed around with (or has gone wrong all by itself) then Capita may well start charging.

[iceman]

So how are you planning on deleting them if you can't find them in the first place?

 

I'll tell the school to find out how to delete them, or at least mark them as withdrawn - it's their problem!

 

While SQL is fun and very powerful, with SIMS in particular it's a really bad idea to do anything other than look.

 

I quite agree. When it was all a huge set of DB4 files it was relatively straight forward to create a synchronised 'replicated' version of the data in a proper SQL database and then use that copy for analysis. But as soon as they ported it to SQL I had a quick browse to see what was going on and decided it was better to use the tools they provide to extract data for the very reason you suggest - if it goes wrong then its their fault not mine.

 

Thanks to all for your help. At least I now fully understand the problem and I have explained it all to the school. Now its up to them, together with their 'paid for' County ICT support unit, to implement the solution!

 

Regards

[iceman]

I thought just for reference I would detail here the steps that created the problem in case anyone else experiences it.

 

It appeared to surface after the last SIMS update prior to 9th May 2016 as it didn't happen in earlier years.

 

1. In spring of 2011 one student was entered as an applicant for Sept 2011 entry but never took up their place
   
   
2. 
   
3. In spring of 2013 three students were entered as an applicants for Sept 2013 entry but never took up their places
   
   
4. 
   
5. For some reason the data team never marked these four as withdrawn or deleted them following their decision not to accept the place
   
   
6. 
   
7. In the spring of 2016 (on 9th May) the data team uploaded into SIMS the new list of applicants for Sept 2016 entry
   
   
    
   
   
   This is where it went wrong

   
   

    
   
   
    
   
   

8. Without notifying the user, when the 2016 applicants were entered into SIMS, SIMS also included those four historical applicants from 2011 and 2013 in the 2016 applicant list (it didn't do this in 2014 or 2015)
   
   
9. 
   
10. At the same time it assigned a status to just those four such that any report based upon Population: On Roll considered them to be currently on roll
    
    

 

 

Hence, at least in my case, any report based upon Population:On Roll included those four non existent students who were not on roll, had no admission number, no attendance and in one case no UPN.

 

 

The data team at the school have been able to delete them from the Sept 2016 applicant list, which solves my problem, but I have advised them to report this to Capita as a bug.