SIMs and Excel

[Cronkgarrow]

Hi all,

 

I for my sins developed a system using Excel that colour coded students grades or levels depending on whether they were on course to hit their target, miss their target, or a near miss. This is done for all years and all subjects. The formulae and conditional formatting all works fine.

 

What doesn't work fine is the update process - at present, after every round of data collection, I have to pull a marksheet off for each subject and each year. This gets rather time consuming.

 

What I want to know is there a way of simplifying this by producing (or automating) a report that updates a master spreadsheet (or a bunch of them) - so that my traffic lighting spreadsheets just need to be changed to refer to the data sheets.

 

I have also wondered whether it would be possible to use SQL within sims to interrogate SIMs' data directly. I know nothing about SQL but have thought if I could bypass the SIMS program altogether then I could make things much simpler. I could then nag to go on courses etc.

 

Or is there a third option I'm missing? The SIMS manager has started doing some traffic lighting of her own, not as subtle as mine, but ok in itself. It just doesn't allow you to look at one student, for all subjects, and see by colour how they're doing.

 

Any help and suggestions gratefully received.

 

Cronk

[apeo]

IMO:

 

1 - Yes you can automate it all if you so wish and the way i would do it is to create your own Report Template that has all the macros/scripts in that does what you already do manually. Problem with that is you need a pretty good understanding of VBA. I could assist but if you dont know VBA that well then its gonna be tough.

 

2 - Depends on how you want to read your SQL data, you can cause ultimately the SQL Data is on your SQL Database but you cant do more then read as capita will not support you. There is a command reporter tool but all that does is run reports in sims but using a commandline.

[Cronkgarrow]

Hiya,

 

Thanks for the reply.

 

I dabble in vba - but mostly hijacked code from MrExcel.com.

 

So when you say scripted you mean build the whole spreadsheet from scratch using script? That's pretty daunting. I was thinking more of reporting the base data to a spreadsheet, which my main spreadsheet would pull data from. When reporting, can you get it to overwrite an exisiting spreadsheet with the same name?

 

And as for reading SQL - the spreadsheet wouldn't be editable by the user, it's used for reference by teaching staff. The fact it's doable is all I really needed to know - I can mention it as a possible training possiblity - if I can bypass SIMS entirely I'd be a happy man (I sooo hate SIMS!).

[matt40k]

I know someone who's made a report that has VBA code which turns the grades into colours as a report. I've PM you his deals.

[sparkeh]

You can do colour coding in SIMS marksheets, can't remember off the top of my head whether you can filter marksheets down to groups of pupils though..

[matt40k]

You can do colour coding in SIMS marksheets, can't remember off the top of my head whether you can filter marksheets down to groups of pupils though..

 

I think your refering to, SIMS Manual: Assessment in SIMS .net (7.112) Page 39

[ChrisH]

I know someone who's made a report that has VBA code which turns the grades into colours as a report. I've PM you his deals.

 

I have just produced a report template that colours in the table cells in word according to the cells value. It looks at the result of a calculation and colours it appropriately. It is not as straight forward as doing it in Excel but still pretty straight forward code.

[Cronkgarrow]

You're talking to someone who's never done a report in SIMS!

 

I don't really want to get in to using word at all if I can help it - my spreadsheet allows staff to look at whole years/classes by subject, or to look at individual pupils, with all subjects. The colouring is based on a lookup table at KS3,and on a simple comparison at KS4.

 

I just need to be able to pull the raw data off - but I'll have a look at reports a bit when I'm next back in school (I work from home four days a week).

 

Greg

[apeo]

Hiya,

 

Thanks for the reply.

 

I dabble in vba - but mostly hijacked code from MrExcel.com.

 

So when you say scripted you mean build the whole spreadsheet from scratch using script? That's pretty daunting. I was thinking more of reporting the base data to a spreadsheet, which my main spreadsheet would pull data from. When reporting, can you get it to overwrite an exisiting spreadsheet with the same name?

 

And as for reading SQL - the spreadsheet wouldn't be editable by the user, it's used for reference by teaching staff. The fact it's doable is all I really needed to know - I can mention it as a possible training possiblity - if I can bypass SIMS entirely I'd be a happy man (I sooo hate SIMS!).

 

Pretty much what i do but i understand enough to fiddle. Effectively when you export to Excel, what happens is that it will dump the data into excel then format it. Thats what happens when you use the default template, all you would need to do is to get a copy for the default template then fiddle with it so it does what you want it to do. For example (without actually seeing what your spreadsheet does im only making assumptions), you can edit the template so its actually a version of your spreadsheet only dif is that it will do all the manual bits for you i.e. at the moment you run a report from sims then copy and paste the data into your spreadsheet, well you can get a script/macro to do that.

[Cronkgarrow]

Cool.

 

So I can specify what fields in what columns and what the "tab" it should go to? If that's the case I should be able to make my life soooo much easier.

[ChrisH]

Running reports from SIMs is basically a mail merge. Set up your template with the merge fields and off you go.

[PhilNeal]

Its possible to do very complex traffic lights directly in SIMS and hundreds of schools do this all of the time.

 

It is also possible to produce a marksheet that includes all of the data you require (whole school and mulitple aspects) and export directly to a spread sheet.

[Cronkgarrow]

Well the main reason SMT want to stick with my Excel sheet, as opposed to the traffic lighting directly in SIMS, is that our SIMS manager doesn't know how to show all subjects one sheet per child, with colouring.

 

I think her and I need to put our heads together a bit. I'll have a chat with her tomorrow.

[PhilNeal]

I think that is possible now - if you'd like to email me with the output you need I will get someone to take a look at what you are trying to do.

 

There are some really expert users that are always willing to help on SupportNet.

[apeo]

Cool.

 

So I can specify what fields in what columns and what the "tab" it should go to? If that's the case I should be able to make my life soooo much easier.

 

Basically the way it can be done is that data will be exported to an excel file, which in turn has macros that will do whatever you want to do. In the case of default templates it setup formating mosty, basically the options you have in Sims Reports. Heres how I've done this before, run a report and go to My Documents\tempSimsRpt and in there you should find either ReportData.xls or ExcelList.xls. What i did is take a copy of the file, had a look at the vba and altered it to match what i wanted. Then used it as a user-defined template.

 

Cant really give you code examples as im not sure what you want to achieve.

Edited December 4, 2008 by apeo

[Cronkgarrow]

Hello again,

 

Thanks for the replies, browsing the code now, although tiz making me headscratch somewhat. Thank goodness it's been well commented. I still can't work out how to make a report have assessment manager data in it - I'm such a SIMS noob.

 

Have spoken to the SIMS Manager and she's not been amazingly helpful. Politics is involved as currently there are two traffic lighting systems in school - hers and mine. Mine is in excel, hers is in SIMS. Hers can't do everything mine can do, but in some ways I prefer hers. I think I'll try pushing her to get hers perfect so that SMT can let me drop mine.

 

I've passed on the traffic lighting that matt40k gave me - lets hope she gets keen.

 

In the meantime people, thanks for all your ideas, it's given me some thoughts, but in the end the SIMS manager is right, we shouldn't be having two systems in one school.

 

Thanks All!

[apeo]

No probs. I think it would be better to have just 1 system and if it can all be done in SIMS then all the better.

[Willzzz]

Well the main reason SMT want to stick with my Excel sheet, as opposed to the traffic lighting directly in SIMS, is that our SIMS manager doesn't know how to show all subjects one sheet per child, with colouring.

 

OK Several options here:

 

1) A report with VBA macros, this isn't as daunting as it sounds really. SIMS will output all the data as a list, you just need a macro to loop through each student, and then through each subject.

 

What is useful is that you can run a report directly from the student details page, and it will return data only for that student.

 

2) An individual report, the drawback is that ironically SIMS doesn't make it easy to run a report for an individual student. It would be awesome if you run an individual report on the fly from the student details page, like you can with STAR reports.

 

3) The full on database solution.

The system I am running here is an SQL database with a web front end. It duplicates a lot of the SIMS functionality only in a logical, user friendly, modular, customisable way. SIMS annoys the hell out of me, and quite frankly the time I have spent creating my own system I have recouped in such a short time span compared to thrashing away at SIMS until it eventually works.

 

Please let me know if you are interested in how this works.

[Cronkgarrow]

Your bespoke front end to bypass sims sounds like my idea of heaven to be honest.

 

SIMS is the most unfriendly and counter-intuitive piece of s.... oftware I've come across in a long time... and I thought SIMS.net was supposed be all shiny and new and sensible... but no.

 

Although I'd love to have something like your system running, I'm not the SIMS manager, so it'd be a major fight. I don't really have the skills. Our last SIMS manager did a lot of his own bespoke things, but when he left they all got dropped because noone knew how to keep them working.

 

So, in the meantime I shall just have to live with things as they are, and hope that the SIMS manager can learn enough so that my system can be dropped.

[Willzzz]

Here is a file I have produced for our tutors, this is a small part of what my system does, but I think this is the sort of thing you are trying to produce.

 

Hopefully SIMS can produce something similar, it just isn't easy figuring out how.

Using Reports student ks3.doc