[sims] Assessment - Attach a result to seveal Result Sets at the same time?

[iceman]

The school I work with has a new system of 3 assessment points each year, for each year group and each subject. Results from these assessments are attached to result sets that identify an assessment point (AP) and a year group (Y) eg AP_01_Y_07, AP_02_Y_07, AP_03_Y_07, AP_01_Y_08, AP_03_Y_08 etc.

 

They also have the concept of a 'Minimum Expectation' grade for each subject/student which has its own aspect (ME) which is attached to its own result set (AP_Potential). This is bulk imported into sims from calculations done in a spreadsheet.

 

I have now learnt that the 'Minimum Expectation' is only for one academic year, so a student's ME for Y8 will be different to their ME for Y7.

I could deal with that by making new results sets, AP_Potential_Y7, AP_Potential_8 etc. and detaching/attaching the existing data to the new result sets.

 

Question

 

It would be far more useful if, when bulk importing the ME grades, I could attach the grade to each of the three existing result sets for one year (eg AP_01_Y_08, AP_02_Y_08, AP_03_Y_08) at the same time. Then I could easily compare a result from any assessment against the corresponding ME by using assessment and ME aspects from the same result set.

 

Can this be easily done without too much manual copying? If so how?

[bobsmith]

I put yearly targets against a generic resultset for that year e.g. Y08, then my assessment points are stored against Y08_01, Y08_02 etc.

 

I can't think of any benefit of having the targets stored against the termly resultsets as you're describing.

[iceman]

I can't think of any benefit of having the targets stored against the termly resultsets as you're describing.

 

My system, one one that does all the real analysis, pulls loads of data from SIMS overnight into an sql database and then performs various analyses. The user gets reports from the database, not from SIMS. One way the user selects a subset to look at is by result set. So at the moment, with only one target per subject, the assessment data gets picked up from a result table joined to a target table using something like

SELECT x,y,z

FROM result

INNER JOIN target

ON result.subjectID = target.subjectID

WHERE result.resultset = 'AP_02_Y_08';

 

To match results with targets that could be different each year I would need a different target result set for each year anyway (eg target_Y07, target Y08 etc) but then to get the joining clause I would have to do a calculation involving a substring of each result set to enure that I used Y8 targets with Y8 assessments.

 

Something like

 

ON SubStr(result.resultset,9,2) = SubStr(target.resultset,8,2)

AND

result.subjectID = target.subjectID

 

If, however, I had done some 'preprocessing' as it were in sims so that I had the same Y8 target assigned to the three results sets already used by assessment grades (ie one taget for each subject but stored against three resultset) that would simplify the join and make it much faster to run the sql as I could then simply say

 

ON result.resultset = target.resultset

AND

result.subjectID = target.subjectID

[bobsmith]

that sounds cool, but yeah more work at the SIMS end.

 

How about a monster marksheet with a formula columns to copy the target from resultset A to resultset B, resultset C etc?

[iceman]

that sounds cool, but yeah more work at the SIMS end.

 

Yes it is quite cool, especially since I deal mainly with the sql and actually touch sims as little as possible, just advising the sims person on what to do .

 

In the end I made a compromise based upon what you do. I made a result set for every yeargroup (Y07_Potential, Y08_Potential, Y09_Potential etc) and then made a big marksheet that associated each of my existing aspects (ME_English, ME_Maths etc) with each result set.

 

Now, using a formatted marksheet export/import the sims person can bulk import the data into the appropriate column for the yeargroup yet after feeding all the results into my database I can easily use sql to extract the correct data for all yeargroups by using a self join with a case clause that selects which result set to use based upon the student's yeargroup. Something like that shown below. Only takes a few ms to process 10,000 results and give me the correct target associated with the right subject for any student's yeargroup.

 

SELECT DISTINCT results.Student_ID                   ,
   T1.Result           AS ME_English_Result              ,
   T1.Grade_value   AS ME_English_Grade_value         ,
   T2.Result           AS ME_Maths_Result                ,
   T2.Grade_value   AS ME_Maths_Grade_value           ,
   
   ... other stuff

FROM results

LEFT OUTER JOIN results AS T1
ON  results.Student_ID = T1.Student_ID
AND T1.Aspect_name     = 'ME_English'
AND T1.Resultset       =
       CASE
           WHEN students.YearNumber = '07' THEN 'Y07_Potential'
           WHEN students.YearNumber = '08' THEN 'Y08_Potential'
           WHEN students.YearNumber = '09' THEN 'Y09_Potential'
           WHEN students.YearNumber = '10' THEN 'Y10_Potential'
           WHEN students.YearNumber = '11' THEN 'Y11_Potential'
       END

LEFT OUTER JOIN results AS T2
ON  results.Student_ID = T2.Student_ID
AND T2.Aspect_name     = 'ME_Maths'
AND T2.Resultset       =
       CASE
           WHEN students.YearNumber = '07' THEN 'Y07_Potential'
           WHEN students.YearNumber = '08' THEN 'Y08_Potential'
           WHEN students.YearNumber = '09' THEN 'Y09_Potential'
           WHEN students.YearNumber = '10' THEN 'Y10_Potential'
           WHEN students.YearNumber = '11' THEN 'Y11_Potential'
       END


... repeat for T3 to T6 for the other aspects

[datamanbs]

If you are saying the minimum expectation grade does not change over the year then I would have an aspect for each year group for each subject, FrenchME7, FrenchME8 etc. Then you can use the aspect without a result set and it will be picked up in all your marksheets for that subject and year group. If the grade changes, the latest one will be displayed in all marksheets which is probably what you want. We do a similar thing - it is the only aspect that is not in a resultset.