[sims] SIM's custom report

[Steven_Cleaver]

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.

 

 

Adno

Year Group

Assessment Results

Result Set

Grade Value

Result

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.

[vikpaw]

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.

[Steven_Cleaver]

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.

 

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.

Edited April 27, 2013 by Steven_Cleaver

[vikpaw]

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.

[Steven_Cleaver]

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.

[vikpaw]

Pleasure.

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.

[Steven_Cleaver]

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 .

[MattMitchell]

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

[vikpaw]

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.

[Steven_Cleaver]

@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.

[Steven_Cleaver]

@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.

[MattMitchell]

@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.

[MattMitchell]

@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!

[penfold_99]

@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.

[Steven_Cleaver]

@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.

Edited April 30, 2013 by Steven_Cleaver

[vikpaw]

So, say you take grades a lot, like 6 times a year, and they have carousels on a variety of subjects your marksheets could change a lot depending on how it's implemented. If you take a snapshot of the data it could be incorrectly linked if you rely on the assumed link of present group to result.

 

However, on the basis you're doing frequent data collection on an on-going basis, you should capture the correct info.

 

On the other hand, just to complicate things even more, could you tie the result date together with the group membership date to be sure of the groups at that time. This still leaves you to pick up the template that group was in and what subject it was. Though you could be looking at a different set of templates each reporting period.

 

I can see how this wouldn't easily extend to any other site, as it relies on a lot of implicit understanding of how the system is set up, probably dependent on naming conventions and the perfect timetable / assessment structure. I know we re-use some groups for other subjects for ease sometimes, and like i said, at the moment are taking data on a course marksheet due to timetabling problems.

 

It's a bit like Quantum Mechanics, if you look too closely at one link it obscures the info in the other link. I think you're going to have to throttle a cat to be sure you know what's up with the data

 

This is good fun.

[Steven_Cleaver]

I am thinking of relying on holding the past data and links in the system somehow rather than rely on trying to do this bit from SIM's not quite sure how yet and am more interested from an overall analysis point, but see where your coming from in regards to snapshot of the data and incorrectly linked.

 

At the moment we three data collection points for Core and 2 for none Core at KS3 and 5 Assessment Points across KS4 but we are looking at extending the number of Assessment points now that we feel the data is being used better and more consistantly and is built into the schools Quality Assurance, with the new system it should be able to handle more assessment points and the Analysis of data that goes with this without putting strain on our MIS Officer as she is looking at setting up Class Tracking Marksheets SIM's for departments.

 

We are trying to keep the extraction of the data and linking into the system as simple as possible even if we have to extract multiple reports as long as we can link these into the system and tie the data together. Use SIM's as more like a data repository to hold the data abit like a couple of other systems we use and then use our own software to add the analyse to the extracted data. I think SIM's is pretty good at holding the data and linking into the various modules within SIM's that use and manipulate this data fo various purposes.

 

We want to try to make it as easy as possible to link the data extracted into the new system as seamless as possible so it would be easier to replicate, this is the only issue with trying to tie the extraction of the data into to many aspects of the system, we want it out in as raw a format as possible.

 

As you say if we look to closely at one Link it can obscure the info in another or you can lose sight of where you want to get to, so is a bit three steps forward two steps back on occassion.

 

As you say this is fun and have picked up a massive amount from this thread that is extremely useful.

[Steven_Cleaver]

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

 

@MattMitchell

 

Hi Matt

 

We have sort of linked the data but are having to really tweek the code to get this to link to the right Aspects for Students-->Subjects-->Classes particularly where subject codes are used in essence for different courses and the only way to identify the diffrence is Classes, and I think your way would be cleaner but can't quite get my head around the specific links for instance TemplateID and related groups (which groups) I can see these but can't quite follow how which to link and I know this is a pain but was wondering if you didn't mind specifically identifying these.

[vikpaw]

Basically, you'll be recreating all the info in your own DB, so you need to extract it all separately and then use complex queries to make it understandable.

e.g. if you run a report on template focus and show marksheet name and group you'll see all the groups that marksheet uses. From it's name you might ascertain the subject.

 

You can also show aspect id and name of the aspect, to specifically know the aspects, and which results it ties in with, which you can pull in later. It might also tell you the subject based on aspect name.

 

If you ask for both pieces of info at once, you'll end up with that horrible cross product we often get when looking at linked tables. Which is usable, but if you keep them separate, you can join them yourself later if you wanted using an inner join query on external template id.

 

So, run them both separately and create two tables. in Excel at this stage for testing. For testing, i just ran the report with a single test template name, but in principal you could do the whole lot.

 

So from student focus get a list groups for each kid and for each group you can lookup the template it was applied to and use that to see the aspect that was used. Then from that and another call on the results table, look up the results in that aspect.

 

Actually, it's more efficient than the way i described it, but maybe easier to follow. If you look at the SQL tables in SIMS you might get a better idea of how it's laid out.

[Steven_Cleaver]

Cheers Vikpaw thanks for this it has made it much clearer for me excellent explanation as always, I have been trying various reports using this method but most of these are just pulling out year for group type but having a play I have realised that if we add Class Marksheets to our Assessment Templates sort of using your description I can get Marksheet Name, Group name (Class), Student Adno and Aspect linked but the Class linked to the Aspect name is the bit I was after trying to get cleanly. I have kept Template ID and Aspect ID in as these may be useful, just need to work on this a bit more but looks like I can get a reasonably clean set of data using this and the other 4 reports I have.