Welcome, Register for free! or Login below:
EduGeek.net RSS Feeds Register FAQ Members Social Groups User Map Calendar Search Today's Posts Mark Forums Read

MIS Systems Do you use SIMs or similar? Try and ease your woes here.

Go Back   EduGeek.net Forums > Technical > MIS Systems
Reply
 
LinkBack Thread Tools Search Thread
Sponsored Links
Old 03-12-2008, 10:38 AM   #1
 
Cronkgarrow's Avatar
 
Join Date: Dec 2008
Location: Brighton/London UK
Posts: 7
uk
Thanks: 13
Thanked 0 Times in 0 Posts
Rep Power: 0 Cronkgarrow is an unknown quantity at this point
Default SIMs and Excel

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.
????: EduGeek.net Forums http://www.edugeek.net/forums/mis-systems/27465-sims-excel.html

Any help and suggestions gratefully received.

Cronk
  Reply With Quote
Old 03-12-2008, 12:28 PM   #2
 
apeo's Avatar
 
Join Date: Sep 2005
Location: Lost
Posts: 962
uk uk england
Thanks: 35
Thanked 31 Times in 30 Posts
Rep Power: 15 apeo has a spectacular aura aboutapeo has a spectacular aura about
Default

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.
  Reply With Quote
The Following User Says Thank You to apeo For This Useful Post:
Cronkgarrow (03-12-2008)
Old 03-12-2008, 12:39 PM   #3
 
Cronkgarrow's Avatar
 
Join Date: Dec 2008
Location: Brighton/London UK
Posts: 7
uk
Thanks: 13
Thanked 0 Times in 0 Posts
Rep Power: 0 Cronkgarrow is an unknown quantity at this point
Default

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!).
  Reply With Quote
Old 03-12-2008, 12:59 PM   #4
 
matt40k's Avatar
 
Join Date: Jun 2008
Posts: 1,315
uk uk england
Thanks: 99
Thanked 112 Times in 99 Posts
Rep Power: 23 matt40k is a name known to allmatt40k is a name known to allmatt40k is a name known to allmatt40k is a name known to allmatt40k is a name known to allmatt40k is a name known to all
Default

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.
  Reply With Quote
The Following User Says Thank You to matt40k For This Useful Post:
Cronkgarrow (03-12-2008)
Old 03-12-2008, 01:11 PM   #5
 
sparkeh's Avatar
 
Join Date: May 2007
Location: Leicestershire
Posts: 1,239
england uk leicestershire
Thanks: 136
Thanked 79 Times in 72 Posts
Rep Power: 25 sparkeh is a name known to allsparkeh is a name known to allsparkeh is a name known to allsparkeh is a name known to allsparkeh is a name known to allsparkeh is a name known to all
Send a message via MSN to sparkeh Send a message via Skype™ to sparkeh
Default

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..
  Reply With Quote
The Following User Says Thank You to sparkeh For This Useful Post:
Cronkgarrow (03-12-2008)
Old 03-12-2008, 01:17 PM   #6
 
matt40k's Avatar
 
Join Date: Jun 2008
Posts: 1,315
uk uk england
Thanks: 99
Thanked 112 Times in 99 Posts
Rep Power: 23 matt40k is a name known to allmatt40k is a name known to allmatt40k is a name known to allmatt40k is a name known to allmatt40k is a name known to allmatt40k is a name known to all
Default

Quote:
Originally Posted by sparkeh View Post
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
  Reply With Quote
The Following User Says Thank You to matt40k For This Useful Post:
Cronkgarrow (03-12-2008)
Old 03-12-2008, 01:30 PM   #7
 
ChrisH's Avatar
 
Join Date: Jun 2005
Location: East Lancs
Posts: 4,187
uk uk lancashire
Thanks: 8
Thanked 60 Times in 53 Posts
Rep Power: 27 ChrisH is a jewel in the roughChrisH is a jewel in the roughChrisH is a jewel in the roughChrisH is a jewel in the rough
Default

Quote:
Originally Posted by matt40k View Post
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.
  Reply With Quote
The Following User Says Thank You to ChrisH For This Useful Post:
Cronkgarrow (03-12-2008)
Old 03-12-2008, 01:45 PM   #8
 
Cronkgarrow's Avatar
 
Join Date: Dec 2008
Location: Brighton/London UK
Posts: 7
uk
Thanks: 13
Thanked 0 Times in 0 Posts
Rep Power: 0 Cronkgarrow is an unknown quantity at this point
Default

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
  Reply With Quote
Old 03-12-2008, 03:47 PM   #9
 
apeo's Avatar
 
Join Date: Sep 2005
Location: Lost
Posts: 962
uk uk england
Thanks: 35
Thanked 31 Times in 30 Posts
Rep Power: 15 apeo has a spectacular aura aboutapeo has a spectacular aura about
Default

Quote:
Originally Posted by Cronkgarrow View Post
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.
  Reply With Quote
The Following User Says Thank You to apeo For This Useful Post:
Cronkgarrow (03-12-2008)
Old 03-12-2008, 03:50 PM   #10
 
Cronkgarrow's Avatar
 
Join Date: Dec 2008
Location: Brighton/London UK
Posts: 7
uk
Thanks: 13
Thanked 0 Times in 0 Posts
Rep Power: 0 Cronkgarrow is an unknown quantity at this point
Default

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.
  Reply With Quote
Old 03-12-2008, 03:59 PM   #11
 
ChrisH's Avatar
 
Join Date: Jun 2005
Location: East Lancs
Posts: 4,187
uk uk lancashire
Thanks: 8
Thanked 60 Times in 53 Posts
Rep Power: 27 ChrisH is a jewel in the roughChrisH is a jewel in the roughChrisH is a jewel in the roughChrisH is a jewel in the rough
Default

Running reports from SIMs is basically a mail merge. Set up your template with the merge fields and off you go.
  Reply With Quote
The Following User Says Thank You to ChrisH For This Useful Post:
Cronkgarrow (03-12-2008)
Old 03-12-2008, 04:22 PM   #12
 
PhilNeal's Avatar
 
Join Date: Sep 2006
Location: London
Posts: 409
uk
Thanks: 2
Thanked 40 Times in 25 Posts
Rep Power: 12 PhilNeal has a spectacular aura aboutPhilNeal has a spectacular aura about
Default

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.
  Reply With Quote
The Following User Says Thank You to PhilNeal For This Useful Post:
Cronkgarrow (03-12-2008)
Old 03-12-2008, 04:45 PM   #13
 
Cronkgarrow's Avatar
 
Join Date: Dec 2008
Location: Brighton/London UK
Posts: 7
uk
Thanks: 13
Thanked 0 Times in 0 Posts
Rep Power: 0 Cronkgarrow is an unknown quantity at this point
Default

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.
  Reply With Quote
Old 03-12-2008, 08:15 PM   #14
 
PhilNeal's Avatar
 
Join Date: Sep 2006
Location: London
Posts: 409
uk
Thanks: 2
Thanked 40 Times in 25 Posts
Rep Power: 12 PhilNeal has a spectacular aura aboutPhilNeal has a spectacular aura about
Default

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.
  Reply With Quote
The Following User Says Thank You to PhilNeal For This Useful Post:
Cronkgarrow (03-12-2008)
Old 04-12-2008, 09:18 AM   #15
 
apeo's Avatar
 
Join Date: Sep 2005
Location: Lost
Posts: 962
uk uk england
Thanks: 35
Thanked 31 Times in 30 Posts
Rep Power: 15 apeo has a spectacular aura aboutapeo has a spectacular aura about
Default

Quote:
Originally Posted by Cronkgarrow View Post
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.

Last edited by apeo; 04-12-2008 at 02:04 PM..
  Reply With Quote
The Following User Says Thank You to apeo For This Useful Post:
Cronkgarrow (04-12-2008)
Reply
Similar Threads
Thread Thread Starter Forum Replies Last Post
excel 97 and sims kennysarmy MIS Systems 7 10-10-2008 08:41 PM
Excel Edu-IT How do you do....it? 13 07-03-2008 08:57 AM
Excel 2007 wesleyw How do you do....it? 7 25-06-2007 05:39 PM
Excel 2007 wesleyw Windows 0 22-06-2007 03:31 PM
RANDBETWEEN in MS Excel woody Educational Software 3 14-02-2006 01:34 PM


Tags
excel sims sql


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search Thread
Search Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 02:28 AM.
Powered by vBulletin® Version 3.8.2
Copyright ©2000 - 2009, Jelsoft Enterprises Ltd.
SEO by vBSEO 3.3.0 ©2009, Crawlability, Inc.
Copyright EduGeek.net




website uptime

© 2005 - 2009 EduGeek.net
no new posts