iceman

I thought just for reference I would detail here the steps that created the problem in case anyone else experiences it. It appeared to surface after the last SIMS update prior to 9th May 2016 as it didn't happen in earlier years. In spring of 2011 one student was entered as an applicant for Sept 2011 entry but never took up their place In spring of 2013 three students were entered as an applicants for Sept 2013 entry but never took up their places For some reason the data team never marked these four as withdrawn or deleted them following their decision not to accept the place In the spring of 2016 (on 9th May) the data team uploaded into SIMS the new list of applicants for Sept 2016 entry This is where it went wrong Without notifying the user, when the 2016 applicants were entered into SIMS, SIMS also included those four historical applicants from 2011 and 2013 in the 2016 applicant list (it didn't do this in 2014 or 2015) At the same time it assigned a status to just those four such that any report based upon Population: On Roll considered them to be currently on roll Hence, at least in my case, any report based upon Population:On Roll included those four non existent students who were not on roll, had no admission number, no attendance and in one case no UPN. The data team at the school have been able to delete them from the Sept 2016 applicant list, which solves my problem, but I have advised them to report this to Capita as a bug.

I'll tell the school to find out how to delete them, or at least mark them as withdrawn - it's their problem! I quite agree. When it was all a huge set of DB4 files it was relatively straight forward to create a synchronised 'replicated' version of the data in a proper SQL database and then use that copy for analysis. But as soon as they ported it to SQL I had a quick browse to see what was going on and decided it was better to use the tools they provide to extract data for the very reason you suggest - if it goes wrong then its their fault not mine. Thanks to all for your help. At least I now fully understand the problem and I have explained it all to the school. Now its up to them, together with their 'paid for' County ICT support unit, to implement the solution! Regards

Ah, yes I understand. I'll have a go with that as it would be interesting to know what is really in that status field. Might have to be later though as I shall need to go into the school to use that (I work from home using remote access into the school). I'll stick with the exports for data extraction on the grounds that 'if it ain't broke'. Trouble is it looks like something in sims is broke!

No, the only place is in my reports and also they appear in the sims 'general student list report' We have contacted the ICT services for our county and they suggest marking those four as withdrawn in order to explicitly set the status flag. However that doesn't explain why they are suddenly showing up now, after an upgrade. I also notice they are now showing up in many other reports using population students on roll that are nothing to do with me eg ParentPay and GroupCall exporter to various external bodies like go4schools and sisra. I think we shall have to delete them.

Thanks for that. I know a lot about SQL but don't know anything about the SQL Query Analyser tool - don't even know if we have it or where to get it. However if that allows me to run SQL queries against the sims database that would greatly simplify my development work!

No, that's also odd. They don't appear in the student search with status selected as , current, guest or any of the other possible values. They only appear in reports.

@jinnantonnixx Yes they look like they have a status of null, and a null admin number - that's what is causing the null conversion error in the external program. How would I check what is actually in that field? After all an empty string could look like null on the screen. Come to think of it how could an empty string get in there anyway and why would it suddenly appear now?

Hello Bob, glad you picked up my post. It gets worse, when I added the admission date and leaving date I got some really strange dates - see below. That would seem to indicate that the data team is wrong, they are not pre-admissions for sept 2016 but from much earlier. But those students have never been exported before now and the report hasn't changed for ages. I might add that I don't deal with the data entry side of sims at all, the data team do that. My role is the analytical processing the data that is already in sims

I have several SIMS reports that export data to xml files every night for further processing externally. All of them are based on: Data area: Student - Population: On Roll They have all been running fine for a year or so. On 9th May the external processing reported an error which I traced to the reports exporting four students who were not on roll and did not have an admission number or any attendance record. They had simply been entered into SIMS by the data team as being accepted for a September 2016 intake. As a test I added the student status field to one of the reports and you can see that the status is not shown as 'on roll', so they shouldn't have been exported. We are running SIMS Build 50 ver 7.168. Is this a bug introduced by the latest upgrade? Below is the design of one of my reports and the preview showing the four students who should not be there I think I may be able to do a workaround by explicitly adding a filter where student status is 'on roll', but I've never had to do that before. Any other suggestions?

@vicpaw Yes, I export the aspectID and use that in my database as a PK, but that's quite easy to ripple through if/when the ID changes on recreation.

OK fixed it. I wrote a report to show me aspects and templates and found it in a dummy test template that I had made nearly 6 months ago and had completely forgotten about (as you mentioned, Bobsmith). Deleted that template and then it was a five minute job to delete the aspect in question, recreate it with the same name but as decimal and pop it back into the proper template in place of the integer one. All that effort because the Dep. Head told me they would only ever need integers stored in that aspect - except when they don't!

Thanks Vikpaw, that's essentially where I had got to. Fortunately only one single value had been entered into the aspect. The SIMS manager had entered the number 38 for one student and then tried to enter 27.6 for the next one, that's when they discovered the problem! My main issue is that this aspect, along with many others, is only really used for data entry and then exported overnight to an external SQL database and processed from there in another program. That program looks for the aspect name Aspect_1 in the database in order to decide what to do with it. If I change the templates and reports to use the name Aspect_2 instead, then the external database needs to be changed, the external system changed to use Aspect_2 everywhere where it used to use Aspect_1 and then recompiled and re-distributed to everyone that uses it. That's much more work than simply deleting Aspect_1 in SIMS and recreating it as a decimal one. But deleting it has defeated me so far as SIMS keeps telling me its being used, even though I've deleted the data in this aspect and removed any reference to it from the template, mark sheets and reports that used to use it.

Thanks but I sorted out the grade set thing within minutes of posting my question (as usual). What!!? Surely not. Never heard of such a thing. Actually they communicated quite clearly, its just the idiots devising this project hadn't thought through the implications before they communicated. If only they would simply tell me what knowledge or information they wanted to get and left me to work out how to get it from the data it would be done by now.

Its more of a convenience than a necessity. I have templates and mark books that use Aspect_1 and reports that export it so they would all need to be changed to use Aspect_2 and, more importantly, so would all the follow up processing done by the external database and analysis system as it needs to know what the aspect is called. But either way, without deleting the Aspect_1 SIMS gets even more cluttered up with yet more aspects that aren't used by anything.

@PhilNeal Yes you are right if SIMS treats this in a simplistic manner, You obviously cannot convert a textual data type into a numerical one if it already contains the value 'Fred'. But there are several routes by which databases will allow data type changes - or at least should do. If all the data in the aspect has been deleted, which I had already done using the method that RootsSchImp mentioned, then there would be no conversion issues. Secondly, if the data type is changing from a base type to a derived type. In my case that is what I wanted to do, change an integer type to a floating point type. Every integer can be expressed as a floating point value as the field size is increasing (clearly it is more problematic the other way round but SIMS handles it when you copy/paste columns) . SIMs could even change the data type using one line of SQL executed against its back end database ALTER TABLE TheTableName ALTER COLUMN TheAspectName float; The only issue would be if the aspect is used as a foreign key to another table. But if so it would make sense to for SIMS to have set the constraints so that changes in the FK are rippled through anyway. Never mind, thanks to all who replied. I deal mainly with SQL databases and the odd user interface of SIMS always frustrates me. It seems to get in the way more than help. This is evident by the many answers in this forum which, whilst absolutely correct, usually involve lengthy, repetitive manual operations in SIMS to get something simple done.

I tried to go through those steps but then got frustrated as I couldn't delete the original aspect because it was used somewhere, but SIMS wouldn't tell me where. It wasn't in all the places I would expect it to be. I think I'll give up with that one and just round the numbers I need to input. There really is no reason why sims can't alter the data type of an aspect. It happily allows you to copy between different numeric types and does the conversion for you. Even being able to globally rename an aspect wouild be handy and this should be possible as well since they are all just aliases. I think there is still a great deal of Dbase4 baggage around in sims. (Come to think of it I used to be able to manipulate the data in all those DB4 files much easier than I can now!) The data in sims is really only used for data input, all the processing is done outside of sims in another SQL database and specialist reports created from there so there is some limits on the aspect names I can use but I'll have alook at the built in ones. - Although I'll probably still have the same issue deleting the ones I have now.

I've found out how to add the new values to the aspect. I changed the underlying user defined gradeset. That seemed to work. However the gradeset for the other aspect is the sims defined one of marks-integer and I still can't see how I change that to another sims defined one of marks-decimal. Surely I haven't got to Make a new decimal aspect called Aspect_2 Copy all the existing results over from the integer Aspect_1 to the decimal Aspect_2 Delete the integer Aspect_1 Make a new decimal Aspect_1 Copy all the results over from decimal Aspect_2 to the the new decimal Aspect_1 Delete Aspect_2 !!!!

I have an aspect that has allows textual grades 4-, 4+, 5- 5+ etc together with associated numerical values. Data has been entered into this aspect. Now the powers that be want to also allow grades of 4, 5, 6 into this aspect (together with their numerical values) I cannot seem to edit the allowed grades and the help file only shows how to edit things like the aspect name, column heading etc. How do I change the set of allowed grades for an aspect? And on an associated point they also want another aspect changed from marks-integer to marks-decimal so I need to do that as well !

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

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

That looks very similar to what I am trying to achieve. Is there any way you could let me have a look at the SIMS report design to see how you went about it?

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?

In order to carry out further analysis I need to find out the overall percentage attendance for each year group between certain date ranges (they will be half terms, either with the parameters entered at run time or maybe I'll just make six different reports) (I also need similar data for overall percentage attendance for each gender and overall percentage attendance for Pupil premium/non pupil premium but if I can see how to do the first one I might be able to sort out the other two) eg as a table the first report might look like [table=width: 500, class: grid] [tr] [td]Date From[/td] [td]Date To[/td] [td]Year Group[/td] [td]Pcnt Attend[/td] [/tr] [tr] [td]1/9/2015[/td] [td]15/12/2015[/td] [td]7[/td] [td]98[/td] [/tr] [tr] [td]1/9/2015[/td] [td]15/12/2015[/td] [td]8[/td] [td]99[/td] [/tr] [tr] [td]1/9/2015[/td] [td]15/12/2015[/td] [td]9[/td] [td]88[/td] [/tr] [tr] [td]1/9/2015[/td] [td]15/12/2015[/td] [td]10[/td] [td]89[/td] [/tr] [/table] This is going to be used elsewhere so I'd like the output as an xml file. I realise that under Reports | Lesson Monitor | Group Reports | etc I can see roughly this sort of data but that route requires manual input and I need to get this data out of sims overnight to import into another system so I can't easily use those reports. Anyway the layout of these is not suitable (eg some are broken down by reg group as well) and because I cannot see the design of those reports I cannot copy and adapt them to my needs. Can anyone help?

Thank you- Sims programmers certainly like to make it easy for their users don't they ! So Aspects, templates & resultsets have a category assignment dropdown list at the time of creation but reports don't and require a more convoluted approach Deleting a report when in a category doesn't really delete the report but deleting a report when in My reports or a focus does Ah, that's all so obvious, I should have realised that myself Cheers anyway

When I go to 'edit an existing report' and open the 'report browse' window I can browse by My reports, Favourites, all sorts of Focus and ... category. Because I have 10 - 15 reports with different foci that are all to do with one particular project I'd like to assign them all to a Category I have made so I can find them easily. I think I have worked out that if I drag a report name from the browser onto a category name then it gets assigned to that category. First, can anyone confirm that this is the right (the only?) way to assign a category to a report? Secondly, How do I remove a report from a category if I change my mind later?