SIMS Report output has different columns to those used in the design?

[iceman]

I have found that when outputting a report as XML the column names are different to those used in the design of the report. Sometimes the output is even seriously ambiguous!

Also any column captions that I might have added for clarity are ignored.

eg

here is a screen shot of my report design

 

Note that I ask for Forename and Surname, but to clearly distingush the legal from the preferred names I changed the captions, additionally placing an underscore instead of the spaces so that the software importing the xml does not need to use field delimiters:

 

Actual column: Legal Surname - Caption: Surname_Legal

Actual column: Legal Forename - Caption: Surname_Legal

Actual column: Surname - Caption: Surname_Preferred

Actual column: Forename - Caption: Forename_Preferred

 

However the XML output not only ignores my captions but uses the column name Forename to mean the Legal Forename whereas in the report design the column Forename refers to the Preferred Forename. It also removes the spaces between words in column names. An example of one record is below

 

11904

T123456789123

Bloggs-Smith

Bloggs

Eleanor

Ellie

Year 10

CPS

 

Outputing the report as csv uses the captions as the column headings and ignores the real column names entirely (as does Excel or Word output) ie

"StudID","UPN","Surname_Legal","Surname_Preferred","Forename_Legal","Forename_Preferred","Year_Group","Reg"

10986,"Z123456789123","Bloggs-Smith","Bloggs","Eleanor","Ellie","Year 11","DNR"

 

 

I find it hard to believe SIMS contains a bug but this seems pretty serious if external software imports Forename expecting it to be the preferred name and actually gets the legal name.

 

Is there a way to force xml to use the same column names, or captions, that the report was designed with?

 

Anyone any idea where the xml is even getting its names from as ChosenName does not even seem to be a valid data field in SIMS?

Edited September 2, 2015 by elsiegee40
anonymise UPNs

[rpwillis]

I can confirm the behaviour, and think that they are the names of the underlying sql columns. It's a 'feature' of how the data is retrieved then saved by the Design Report window.

 

However, if you run it using CommandReporter then the elements have the expected names - as per your captions. External software would normally expect data retrieval via Command Report unless they are using the api, or getting it from csv files.

 

I would classify it as a bug myself, however as someone could be using it and expecting this behaviour, I'm not sure it can be changed now.

[iceman]

Thanks for checking this out for me. (The way SIMS is designed, aka thrown together, never ceases to amaze me)

if you run it using CommandReporter then the elements have the expected names

Just to confirm, do you mean that even running the xml report through CommandReporter the correct field names (ie the captions) are exported ?

 

That is indeed the way this report will eventually be run, as a scheduled report using CR, so if I can assume it will work as expected with CR that's good

 

I need to ask as I can't test using CR myself at the moment because I'm currently developing over a remote connection into the school and County has disabled the DOS prompt and direct access to drive C: - helpful!

[matt40k]

because I'm currently developing over a remote connection into the school and

 

Not quite sure Cambridgeshire County Council will be happy that a third party is exporting UPN numbers

[CAM]

This is a bug which is preventing me from properly experimenting with XML in SIMS, if I set a custom name I've done it for a reason and SIMS should utilize it! A shame as I can export data in quite neatly structured XML files (a similar clean output option for Assessment Manager would be great by the way as XML Spreadsheet mangles the XML file structure by filling it with rubbish and stops me from using it properly!).

 

An option to search multiple report fields with a single supplied variable would be great as well as it currently forces really poor design practices. (Example: My UPN Check report for handling UPN clashes needs the UPN to be entered into two fields. I'd rather enter one variable for $UPN to search UPN and Former UPN at once).

[CambsICT]

As I understand UPN, their intended use was for data matching, so exporting them for that purpose is legitimate. However they are a "blind identifier" so are not to be used in printed reports.

 

Just found the DfE guidance on this:

 

The UPN must be a ‘blind number’ not an automatic adjunct to a pupil’s name. It must be held electronically and only output when required to provide information to the LA, central government or another school/academy to which the pupil is transferring. Under the Data Protection Act 1998, the UPN is designated as a ‘general identifier’ making its use for any purpose unrelated to education illegal. A pupil’s admission number, rather than the UPN, should be used as the general pupil reference number on the admission register or paper files.

 

Full guidance is here. General advice is to use admission numbers to avoid illegal use of UPN.

Edited September 3, 2015 by CambsICT

[iceman]

Not quite sure Cambridgeshire County Council will be happy that a third party is exporting UPN numbers

I work for a school developing bespoke MIS systems for them in reponse to particular requests from the Head. My systems provide SLT with special reports, ones that SIMS cannot produce, regarding their own students.

In my systems I export the UPN to use (electronically) in SQL databases hosted on that school's servers. It might be used, for example, as the PK in a table of students and as a FK in tables of things like assessment results or attendance figures allowing SQL queries to join the students table to other tables.

 

So it would appear that my use of the UPN in this way is perfectly legal. I am also registered independently under the DPA as a Data Controller in my own right, with permission to store and process Sensitive Personal Data, with particular reference to children. So my development databases and the electronic transfer of UPNs into them over a remote connection is also legal.

 

Having said that, note that I export the SIMS ID as well. This PK picked up from SIMS's own tables is going to be used as my own PK instead of the UPN in the system currently under development but I am exporting the UPN as well in this case is so that I can cross check against other tables that the correct students are being selected in queries.

 

The data in my original post was already anonymised - yes, you can use the upn to find out a real county and even a real school but you wouldn't find Ellie Bloggs-Smith there. The UPN D330244305989 is also valid, with a real LA and establishment. I generated it using random data taken from EduBase2.

 

The DfE guidlines do need some interpretation.

Pg 8 says schools should ensure that 'Pupils’ UPNs are not printed or written on any reports or similar document'.

But the guidelines for a SAR, pg 7, say 'Pupils have the right to receive, on request, a copy of any information the school/academy holds about them (including their UPN)' and pg 36 of the ICO's document 'Subject access code of practice' says 'You may comply with this [a SAR] requirement by supplying a photocopy or print-out of the relevant information'.

[matt40k]

Well I can only give my opinion...

 

but I'd wouldn't be giving permissions for my sons data to be use if it was me as parent and if it was my work, I'd be gathering written "are you sure" from SLT to cover myself.

 

because I'm currently developing over a remote connection into the school and County has disabled the DOS prompt and direct access to drive C: - helpful!

 

I appreciate Capita charge £2.5k to get a copy of the training database - but it does show your commitment to it - I'm not saying your developing on whatever county's hardware, but you are working on live data. You could also generate dummy data. At the moment it sounds like you've sold vapourware.

 

RE: UPN - my understanding is if you're not cross ref'ing the data - ie across schools, using the UPN isn't allowed to be used. Period. Your excuse of "its under dev" - goes back to the dummy\training data point.

 

 

SLT with special reports, ones that SIMS cannot produce

 

Sure? Have they seen Discover? Have you seen some of the 100's of products already out that produce special reports? Chances are they have, they just don't want to pay the fees

[CAM]

Discover is free with Assessment Manager, although if I could build my own accurate reports system I'd do it in a heartbeat. Also he's said he is working on phasing out UPNs as the key and implementing Student ID.

[matt40k]

although if I could build my own accurate reports system I'd do it in a heartbeat.

 

Yer, still tempted to build a few SSIS packages to suck the data into our warehouse - one day

 

he's said he is working on phasing out UPNs as the key and implementing Student ID.

 

My beef is this is third party and its live data.