SIM's custom report
I have designed the report below to extract result Aspect Name and Grade but would like to attach relevant Subject Code or Class Code to Aspect name, I have tried this multiple ways but either get Class repeated Multiple time or data jumbled so it doesn't relate to the other data I have tried various Filters etc but still no joy, just wondered if anyone had any ideas.
Aspect name of Aspect [Basic Details]
I can't really use a marksheet as I need this in a report to autmatically extract the information, any help would be appreciatted.
The subject / class code aren't linked to the assessment results / aspect so it will always end up giving you a cross product. It will return the student details, and the linked aspect + result for that student, then when you ask for subject it will return all subjects against each of the previous results for that student.
The only way to limit this is either manually request a specific subject to tie with the aspect at run time, or use a pivot table of sorts to split the data back out.
The other option is to specify the group initially by subject code / class, i.e. filter the list of students by class code, so all members of said group, you'll still get back all results that match the criteria though.
What exactly are you after? Why does the subject need to be shown, isn't it obvious from the Aspect Name?
I can see why a class code might be useful, but you will see all classes per student.
Cheers Vikpaw thanks for the clear explanation around the subject /class code linked to assessment results / aspect and this is pretty much what I thought and have found.
Originally Posted by vikpaw
The manually requesting specific subject at runtime I don't think will work as I want really all subject data and I am using Commandreporter to automate the extraction of the report , again I think same issue with Pivot tables as I am trying in essence to automate the whole thing so data is exported and I don't touch the data but the data is automatically imported to my SQL database.I have tried the Subject Code/ class but again issue is I don't want one specific subject code / class against list of students but subject Code /class against all students (Grade Value,Result,Aspect name of Aspect [Basic Details]) which I can't get.
The subject needs to be shown because to be honest it is obvious with most Aspect Names but not all (Historic) which is where the issue lies or we can write code to match most of these to extract after we have exported it as we import it into SQL.
What we are exactly after is a bit of a long explanation but will give this a go, we have set up a few SIM's reports which we are automatically extracting using CommandReporter and then extracting various information from these and importing it into and SQL (few tables) database then have and are working on a web front end (Visual Studio) that displays the information for staff with various calculations so staff click a button and view what information they want, so can be viewed from various perspectives and we can build in what analysis we like and the data is virtually live so if someone moves class etc no manual updating\extracting this is all done seamlessly. Because of the information we can extract ou autmatically we are looking at building intervention into this because we can get Credit\Debit virtually any result and build this into the system automatically email staff about intervention groups etc so Class Teacher, Subject leader and department line manager but other stuff as well again all automated, staff could also enter what intervention had taken place and we could analyse which intervention was most succesful etc.
The issue we have run into is in KS3 and most of KS4 subjects are fine to match as it is easy to link to subject code and extract Classes and Subject codes in diffrent report so we can match these to student ID (Person_ID) it is where we have for example at KS4 Science Subject code is Sc but the code is the same for Single Sciences so Chemistry, Physics, Biology are all Sc1, Sc2-Sc6 are Core and Additional Science and BTEC Science SC7, SC8 and it isn't in these cases as easy to match(Grade Value,Result,Aspect name of Aspect [Basic Details]) we would have to code it I think to match the Class which is possible but a bit messy and I was just wondering if there was a better way of extracting the data out in a report that I was missing.
To be honest other than this issue in a few subjects it works really well and although we haven't built in the higher analysis the Deputy Head here is well happy as he can see the potential.
So it looks like we have two options I think and just let us know what you think design my import into the new SQL database to deal with this I think by building in functionality to deal with this and as it is an issue really with only three subjects at KS4 Science, PE and ICT it isn't to bad but I wanted to make sure I wasn't missing something with reports out of SIM's.
The other thing I thought of was changing the Aspect Names but would this muck up individual reports in SIM's where these Aspects are linked in ?
Sorry about long winded response.
Right, well what you're doing is great, but what you're looking for doesn't really exist.
The classes, which are created for a subject in the timetable can be used against a marksheet to allow data input. However they don't have to be, so it's quite subjective really based on how the creator wants the data input.
It's never really linked to the data. For example, i'm currently sorting assessment out for primary school, the timetable doesn't fit into our cycle which is fixed for the upper school, so i have timetabled some classes, and others i've just created a single class for the band. I'm not scheduling them. Against the marksheet, i just put the single class for the teacher and they see all students and filter by reg group. For some subjects i just allocate to the reg groups as the main teacher teachers it. I could just as easily allocate to the year group, if the head of year was going to fill it in.
So, the only vague link between the aspects and the class would occur when you allocate the marksheet, but there is no link in the data. Which if you allocate the wrong marksheet is evident for example, if you allocate the spanish class to the maths marksheet, the spanish teacher, ignoring the list of names, might copy and paste their pre written grades / comments down the list assuming it's correct. There is nothing to link spanish to maths, nor to prevent it going in if the data is valid for the aspect.
Even if there was a link, it doesn't necessarily stay the same over time, you have to recreate the marksheets each year, and the class codes can change, especially if they are in a different block depending on naming convention.
One way to do what you want is to load in the aspect / result data against the student, then load in the list of current classes, which are presumably the most common thing to filter on even for old data. Then do some form of match, which i guess is what you're doing.
So either run two separate reports, or allow the cross product / duplication and alter your import routine so that it doesn't try to link the data together on import and bypasses the duplicates if that's possible. I'm sure two imports will work best.
However, longer term for ease, renaming the aspects is worth the effort. You don't need to alter the ind. reps. but it pays to so in the future it makes sense, else the text label and the hidden text label will be wrong, but you should find that so long as the external id of the aspect stays the same it will validate and not care about the text labels. So should still work after a rename. also for consistency when you change the name it's useful if you recreate / update the marksheets with the new name. So any future editor doesn't have a real brain ache. In the short term to get your system working, it should be fine. Test it and see.
Again thanks for the excellent explanation.
"One way to do what you want is to load in the aspect / result data against the student, then load in the list of current classes, which are presumably the most common thing to filter on even for old data. Then do some form of match, which i guess is what you're doing." This is the way we are doing it and it gets me nearly their just difficult to match a couple of things but most are fine.
Cheers for the info about renaming the aspects and may give a couple a go and change these on Marksheets and Individual reports to keep it tidy as you say.Will also give creating a second report for the aspects that haold the results and see if I can deal with the cross product and duplication, just to see which works the best.
When we have it working here we are thinking of setting it up for one of our Primary feeders as well.
Thanks again for your help vikpaw.
Just thought of a cheat. If you don't use the description box, then you can add data to that, which your import routine can use as a marker. e.g. put the actual class codes in there if that helps and they are sufficient, or at least the subject name. I'm not sure if the field will come out padded or how easy it will be to work with, but is another way to 'cheat' bearing in mind the link you want does't exist per se.
Vikpaw that is a brilliant cheat and will easily get me round the odd subjects also gives me a flexible way of altering this if class codes changed linked to subjects where subject code is used for multiple subject courses and relies on class and won't cause any cross product.
Thanks again thats Brilliant :).
There is a way to do this, actually...
First off, though, a tip - if you want to extract lots of results in SIMS, do it through the results focus, and not the student focus. If you include studentid (via third-party reporting permissions) it'll give you a simple method to join back to a table of students, without having to include extra tables in the underlying views that support the reporting queries.
You'll need several reports, and it's worth running them as a user with 3rd-party reporting permissions, in order to get at the integer primary key SIMS uses in the tables:
* student focus - list of students (plus any student-specific data e.g. FSM, SEN status, etc) include StudentID
* results focus - list of results. If you're in a position where you can filter these by resultset only, the report will output spectacularly fast, even for hundreds of thousands of records. include ResultID
* template focus - list of templates (include TemplateID) - also show related aspects, and show AspectID for these
* template focus - list of templates again, with TemplateID, and this time list the related Groups for the templates
* student focus - for each student (along with their StudentId), show related classes - I tend to filter this one so that Start Date is *before* the day-after-the-reference-point, and End Date is after the day-before-the-reference-point
* course core properties focus - use the course code and description, and then show related classes (again it's a good idea to filter these by end date)
Between this set of tables, you have enough information to find the relations between templates->aspects, templates->groups (and therefore classes->aspects), then you can join students->classes thus getting students->class+aspects, and the use the courses->classes relation to give students->course description+aspects
From there, it's a question of filtering out all the stuff you don't want, and (ideally) having some kind of consistent(ish) naming that means you can add semantics to the aspects in terms of what they actually represent (e.g. Target data, assessment grade, itnervention information, etc).
Sadly, SIMS doesn't have a direct way of storing subject-based links to groups of aspects, but this method will work if you're wanting to go the route you mention.
Not that I'm doing something similar here, of course ;-)
Hope this helps a bit, anyway
Good plan that man! My only concern is that the marksheets in a template change every year, not sure how that looks in the underlying data, but i guess if you program it right, it will keep itself up to date.
You might as well just clone / link to the main SQL db for SIMS itself, and use a view if there is one to show those links, or make your own.
Here's a good project, turn what you're doing into a fully fledged add-on application, that connects to SIMS and makes the links you need and then does the analysis.
@MattMitchell cheers can sort of see the logic with this just need to work this through. At the moment have four reports that export the data, Students pretty much as you have suggested, except I have admission number and person_id as we have this linked into AD, Staff Report just pulling basic staff info and Class Report which pulls (Adno,Class, Staff Code, Subject, Subject Code and Teacher and finally one that pulls the Assesment info and have it pretty much linked except where subject code is used for multiple subjects for instance Sc is used for Core Science, Additional Science, Biology, Chemistry and Physics and BTEC Science.
Looks like a great idea of using the ResultID,StudentID etc hadn't really thought of trying it like that so will give it ago but sounds feasible, just need to get my head round this and thanks for the explanation.
@vikpaw we did look at first linking it to the SIM's SQL database to extract the info but when we ran the SQL profiler on the SIM's database to see where it was pulling the data from when a Marksheet was opened it looked extremely complex and difficult to link out, the other information looked reasonably possible to get out this way, which is why in the end we looked at reports.
The idea is overnight scripts run to export the data from SIM's automatically import into the new database again a script (all in one app) and then web front end does the analysis, graphing etc of the data this hopefully means the whole thing is automated staff enter data then overnight all analysed ready for staff next day also sorts students moving classes again as this would be automatically handled overnight so would almost be fully fledged addon application etc.
@vikpaw the idea of doing it like this is that you pull out all the relevant marksheet data at the same time as the student and course data (while those particular marksheets are still current) in order to identify the link between marksheets and subjects. In effect, you're scraping the implicit metadata from the marksheets.
The main reason for doing it like this, was in order to capture the aspect setup when I didn't create it ;-)
If you're reading direct from SQL, it should be easier, as in effect you're grabbing the tables and relation tables (for many-to-many joins) contained in the backend, mostly in the same kind of structure.
There is the thought in the back of my mind that this might lead to a generic "capture the aspect setup in order to do meaningful analysis" application, but to make it cover all schools in a user-friendly way would make for a rather large and complex bit of coding! What's nice about it so far, is that (given a rough idea of how aspects are named in a school) it allows you to link it all together without too much human interaction, which is nice ;-)
I was using things like aspectexternalids and so-on before to do the joins, but these aren't unique in the database (unless combined with the aspectsupplier field). Similarly, students aren't guaranteed to have a UPN in the database. The other reason for using SIMS's internal IDs to join on, is that these are stored in the various tables (or relation tables), so you don't have to make the backend join another table on just to output an admission number, etc, for the report.
@Stephen_Cleaver with this approach, it doesn't matter that the same subject code is used for multiple actual subjects as much as it might; because you're linking students to classes to marksheets, only those students who appear on a Biology marksheet will have a Biology grade showing.
The catch is, of course, that if the same course code is used for multiple sciences, you'd see (for instance) three sets of grade called "science" for a given student, without identifying which is which. I get round this by having a table that remaps the course (and/or subject code and class name) for particular groups, although this is where things vary from school to school.
The best way to avoid this, of course, is to use separate codes for different subjects in the timetable, but I know a lot of people timetable all science lessons (for instance) as "Sc" regardless of what subject within Science they are!
@MattMitchell have covered pretty much everything but there is one caveat to the link between group and student mark,
its not 100% correct from a data analysis perceptive as the grade could have been entered while being a member of a different group and/or entered by a different teacher,
all you can say it the student achieved the grades at a given point in time which is current a member of a given group.
@MattMitchell almost their with your take on how to create the reports created most of these but just trying to get the last one or two to match all the data I need so thanks for this, will see if this works better.
Also looking at Vikpaws idea of adding to the description for Aspects as this might sort me for the odd couple of subjects short term I have an issue with.
@penfold_99 interesting point I had this sort of back of my mind and once linked out of SIM's and out into the new system I am going to see if their is someway of holding this information somehow for instance who taught a specific class the previous year but various other aspects as I can see how historic data like this will be useful in analysis.