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.