Some SQL wizardry required

[ConTheITGuy]

I would like to produce, from SIMS .net, a spreadsheet with a row for each year 11 student, and then a column for each group that exists in year 11. For example: English Set 1, English Set 2, Maths Set 1, etc.. Then next to each student, in each column, a BOOL yes or no whether or not they belong to that group.

 

Has anyone any ideas what kinda report I could design in SIMS to achieve this? Do I even make sense??

 

Thanks,

 

Andy

[penfold_99]

hi andy,

 

what do you need to do with the information?

 

i ask as if you only want to look at the information and not manipulate it you can do this in a report using analysis as the output.

[ConTheITGuy]

Am after mailmerging it - lots of "IF student is in this set THEN write this text ELSE don't". If it comes to it, I might end up doing as you say, and just having to create my own table and tick the boxes myself. Did something similar with 60 kids last year, hoping I can get away with not having to do it for 200 kids this year!

 

 

Andy

[penfold_99]

what you could do it export a list of all the students and all the classes using the report generator to export to excel, then on a second sheet use a vlookup to look for the group of that column and set a value.

[vikpaw]

There's probably easier ways than this, i just happen to be playing with pivot tables at the moment.

 

What exactly are you trying to do with the spreadsheet afterwards? I have a marksheet similar to what you want that we use for sixth formers to show whether they are studying the higher or lower level of each subject. This would make more sense if you need to maintain links to data in SIMS.

 

For now, design a report that lists the students name (i just pick name and reg as it's convenient), then add the class name from the classes subsection. Filter it for Y11 students. There's no need to sort really. Send the output to Excel just make sure that 'suppress duplicates' is not ticked.

 

Run the report, then in excel from the data menu choose pivot table, accept all the defaults. (i'm using office 2k3, don't have a clue on 2k7 yet - it's probably easier!!) from the pivot table field choose box, drag students to the column on far left. also drag students to the data area in the middle. drag class to the row at the top.

 

this should give you a '1' for each subject where a student takes that class. you get sub-totals at the end of columns and row.

 

you can right click on the table and get it to fill blank cells with NO. If you really need the Yes part then, you have to copy paste the data but just the values into a new sheet and then can do a find and replace on it.

 

it depends on what you are trying to achieve after, but this should be a good start.

[ConTheITGuy]

Thanks to both of you. Vikpaw, that looks almost exactly like my spreadsheet of last year! Just the job. Will go play with that.

 

 

Andy

[vikpaw]

sorry i got sidetracked whilst replying. i see what you are after now.

 

i'd be tempted to say, use individual reports from Assessment Manager as you can run them off by class, and it's a live record. or, even better use SIMS Profiles and have fixed comment banks for each subject, plus it'll allow you to have multiple different comments for each subject the student does.

 

it sounds like you are producing some kind of report / feedback system. so use the built in functionality. the spreadsheet is just an interim step in a manual solution.