+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 20
MIS Systems Thread, SIMs and Excel in Technical; Hi all, I for my sins developed a system using Excel that colour coded students grades or levels depending on ...
  1. #1

    Join Date
    Dec 2008
    Location
    Brighton/London UK
    Posts
    7
    Thank Post
    13
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    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.

    Any help and suggestions gratefully received.

    Cronk

  2. #2
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    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.

  3. Thanks to apeo from:

    Cronkgarrow (3rd December 2008)

  4. #3

    Join Date
    Dec 2008
    Location
    Brighton/London UK
    Posts
    7
    Thank Post
    13
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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!).

  5. #4

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,408
    Thank Post
    368
    Thanked 639 Times in 521 Posts
    Rep Power
    158
    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.

  6. Thanks to matt40k from:

    Cronkgarrow (3rd December 2008)

  7. #5

    sparkeh's Avatar
    Join Date
    May 2007
    Posts
    6,752
    Thank Post
    1,278
    Thanked 1,651 Times in 1,106 Posts
    Blog Entries
    22
    Rep Power
    506
    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..

  8. Thanks to sparkeh from:

    Cronkgarrow (3rd December 2008)

  9. #6

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,408
    Thank Post
    368
    Thanked 639 Times in 521 Posts
    Rep Power
    158
    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

  10. Thanks to matt40k from:

    Cronkgarrow (3rd December 2008)

  11. #7
    ChrisH's Avatar
    Join Date
    Jun 2005
    Location
    East Lancs
    Posts
    5,009
    Thank Post
    120
    Thanked 282 Times in 260 Posts
    Rep Power
    108
    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.

  12. Thanks to ChrisH from:

    Cronkgarrow (3rd December 2008)

  13. #8

    Join Date
    Dec 2008
    Location
    Brighton/London UK
    Posts
    7
    Thank Post
    13
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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

  14. #9
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    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.

  15. Thanks to apeo from:

    Cronkgarrow (3rd December 2008)

  16. #10

    Join Date
    Dec 2008
    Location
    Brighton/London UK
    Posts
    7
    Thank Post
    13
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.

  17. #11
    ChrisH's Avatar
    Join Date
    Jun 2005
    Location
    East Lancs
    Posts
    5,009
    Thank Post
    120
    Thanked 282 Times in 260 Posts
    Rep Power
    108
    Running reports from SIMs is basically a mail merge. Set up your template with the merge fields and off you go.

  18. Thanks to ChrisH from:

    Cronkgarrow (3rd December 2008)

  19. #12

    Join Date
    Sep 2006
    Location
    London
    Posts
    1,326
    Thank Post
    36
    Thanked 353 Times in 238 Posts
    Rep Power
    79
    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.

  20. Thanks to PhilNeal from:

    Cronkgarrow (3rd December 2008)

  21. #13

    Join Date
    Dec 2008
    Location
    Brighton/London UK
    Posts
    7
    Thank Post
    13
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.

  22. #14

    Join Date
    Sep 2006
    Location
    London
    Posts
    1,326
    Thank Post
    36
    Thanked 353 Times in 238 Posts
    Rep Power
    79
    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.

  23. Thanks to PhilNeal from:

    Cronkgarrow (3rd December 2008)

  24. #15
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    42
    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; 4th December 2008 at 02:04 PM.

  25. Thanks to apeo from:

    Cronkgarrow (4th December 2008)

SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. excel 97 and sims
    By kennysarmy in forum MIS Systems
    Replies: 7
    Last Post: 10th October 2008, 08:41 PM
  2. Excel
    By Edu-IT in forum How do you do....it?
    Replies: 13
    Last Post: 7th March 2008, 08:57 AM
  3. Excel 2007
    By wesleyw in forum How do you do....it?
    Replies: 7
    Last Post: 25th June 2007, 05:39 PM
  4. Excel 2007
    By wesleyw in forum Windows
    Replies: 0
    Last Post: 22nd June 2007, 03:31 PM
  5. RANDBETWEEN in MS Excel
    By woody in forum Educational Software
    Replies: 3
    Last Post: 14th February 2006, 01:34 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •