Help with a report in SIMS

[dezt]

Hi everyone, I've been tasked with trying to create a report for our Assistant Headteacher, which i'm struggling with, hope someone can point me in the right direction.

 

What she wants is a list of Pupils names, their UPNs and then each Actual Mark for each subject that has been entered in to assessment manager through the marksheet entry screen by the teachers.

 

Is this possible? If anyone has any ideas i'd really like to hear them.

 

Thanks.

[bobsmith]

yes, create a template and marksheet for it, don't bother trying to do it through reports.

 

You may have to do a vlookup in excel to bring the UPN into it, as I don't think UPN is available through the marksheet additional columns.

[dezt]

Sorry for being thick, will that then bring all the marks already entered into SIMS for this summer? I hope that makes sense, the teachers have already filled in the marksheets for this summer.

[bobsmith]

Yes, if the columns you add to the template are the same ones that the teachers have completed already.

[dezt]

Thanks for your help, I think i've got the marksheet up now. Just got to pull through the UPN's.

[bobsmith]

your easiest and more importantly most reliable bet will be to use admission number in the template - and then run a report to dump out admission number and UPN, makes for error free vlookups.

[dezt]

thanks, all sorted now.

 

Happy Assistant Head.

[bobsmith]

a happy assistant head is the best kind Edited July 20, 2015 by bobsmith
spelling!

[PhilNeal]

You won't have a happy assistant head if the ICO learns that the UPN is being used inappropriately! You need to be really careful about outputting the UPN as the ICO is very sensitive about it being used for any purpose other than sending data to DfE. They caution against it being used to link systems or identify pupils internally.

[bobsmith]

oh yeah, forgot to mention, I would normally delete the UPN from my spreadsheet after the vlookup has been run. A quick "copy and paste special" to fix the data first of course!

 

the UPN is invaluable for getting a decent match on internal spreadsheets, but don't leave it visible!

[psydii]

You won't have a happy assistant head if the ICO learns that the UPN is being used inappropriately! You need to be really careful about outputting the UPN as the ICO is very sensitive about it being used for any purpose other than sending data to DfE. They caution against it being used to link systems or identify pupils internally.

 

::wanders around kicking the furniture::

 

.... Ok so what should we use as a unique identifier for linking data sets?

 

Also where has the DfE published restrictions on the use of UPN?

 

Finally and possible off topic, what ever happen with ULNs? (

[PhilNeal]

I just searched "DfE UPN" and "ICO UPN" and there's lots of guidance available. DfE have advised MIS suppliers that this is a blind number that should not appear on reports.

ULNs apply from 16 years old I believe, as the UPN can't be used after that.

Please ask ICO what you can use instead of UPN!

[GrumbleDook]

Where appropriate please use admission number .... Which should be unique in most MIS. Also be warned that's asking the ICO that question will get them pointing you at the DfE ... Who will point you at the ICO and MIS providers ... You *will* get dizzy going in circles.

[minimoo]

when I've exported data from sims and wanted a unique id for a student, i've often used the sims database person_id that used to be part of their 3rd party reporting toolkit, but i think is now built into sims if you have the right roles within system manager - on the basis that's more likely to be a auto-number field in sql and stay unique [both upn's/uln's and admission numbers can potentially change in the case of an error - less so with admission numbers, but capita did use to have a patch to let you remove an incorrect admission number about 5 years ago]

 

I've taken the view that the person_id in sims is least likely to get changed, most likely to identify a unique student|staff|contact record - albeit, can only be used with the report designer, and as it's not visible anywhere in the UI or externally to sims, is semi-sensible to use

@PhilNeal - assuming MSSQL does not do something silly - what's your view on using this field to link data sets?

[PhilNeal]

I'd say it's safe but we aren't "protecting" this ID. AdNo is what I'd use but this isn't available to pre admission pupils

[minimoo]

The pre-admission pupils case was why we stole that ID

[Cache]

@PhilNeal just curious if you're aware then that exporting a marksheet from Assessment Manager has the UPN hidden in Column A?

 

Edit: Actually, that answer's the OP's question about getting the UPN out with the data you want, but you'll need to heed the guidance above against using the UPN for anything.

Edited July 20, 2015 by Cache

[bobsmith]

I can't see the ICO having an issue with you using a UPN to match up data on an internal spreadsheet, as long as it is cleansed before it's published to anyone, it's more of a matter of not sharing it willy nilly (and by that we mean to any numpty to who might accidentally release it to someone outside the organisation).

 

The ULN is issued at age 14 (year 8) and is supposed to supplant the UPN for matching up students across institutions and with exam boards etc. It only exists because they didn't safeguard the UPN properly and it's possible to generate duplicate UPNs as well as change them. Unlike the UPN the ULN is given to students so that they can pass use it throughout their education to preserve continuity of their educational records.

 

The issue for me with admission number (as mentioned by a previous poster) is that not only do pre-admission pupils not get one, but guest students don't get one either, and as part of a sixth form consortium we have a load of guest students each year.

 

A proper primary key field available on the marksheet export would be worth it's weight in gold @PhilNeal

[dezt]

Sounds like it was a good job the Assistant Head was happy without the UPN on the sheet she was using.

 

Thanks again for your help @bobsmith I didn't use a vlookup in the end as I asked her if she really needed the UPN and she said she could do without.

[vikpaw]

They probably just think they want/need the UPN, but if you offer an equally useful ID number such as AdNo or personID i'm sure they'd be as glad. In fact, as in this instance, they probably don't really need it anyway.

We made AdNo our unique code for all systems to link things internally, so it became the library barcode, etc.

 

PersonID is really good too, and if SIMS is used correctly so if students return, the old record is re-used, it should stay unique on each system, the same way the AdNo stays the same. This should work in theory if the UPN changes, but i suspect when you import students it searches/matches by UPN...? Can't remember it's been a while since i used that system. Though i've just found i've got a number of dupes on my system due to careless data entry, and so AdNos i was expecting to be the same aren't, and now it's tied into Fees, it's a real pain to fix.