[sims] Help creating a report please...

[kennysarmy]

Is it possible to create a report on parents of students on roll that would be in the following format:

 

Parent Name, email address, Son/Daughter1 name and tutor group, Son/Daughter2 name and tutor group, Son/Daughter3 name and tutor group etc

[LosOjos]

You could do it so that it listed the students vertically i.e.

 

Parent Name, email address, Son/Daughter1 name and tutor group

Parent Name, email address, Son/Daughter2 name and tutor group

Parent Name, email address, Son/Daughter3 name and tutor group

 

There's no way of getting it to lay them out horizontally though (not without macros anyway)

[bobsmith]

or you could use some vlookups to translate it to one line per parent. (not as neat but it would work)

[LosOjos]

or you could use some vlookups to translate it to one line per parent. (not as neat but it would work)

 

The way I go about it is to give each student a unique ID that is a combination of their contact's ID and their position in the list (using a COUNTIF on all previous instances of the contact's ID to that point in the sheet)

 

Then you use a pivot table to list all the unique contact IDs, and VLOOKUP to pull through the contact details. Column headings are 1 to the highest student number, then you can VLOOKUP to pull through the students' details.

 

Bit of tweaking and manipulating but gets the job done - of course you can drop the lot in to a macro so Joe Bloggs can just run the report from SIMS and get it all formatted how they want

[kennysarmy]

OK - great.

 

Another question.

 

When I run a sims report on Contacts and add in the student relationships it only seems to show students ON-ROLL - I want a list of all contacts and the associated student with relationship whether the student is on roll or a leaver.

 

In Reports Design Report I've chosen Contact to be the focus, then added in the contacts surname & forename - then clicked Students and added in Relationship and Forename/Surname.

 

When I run the report it only seems to populate the student information from those on roll

[sparkeh]

OK - great.

 

Another question.

 

When I run a sims report on Contacts and add in the student relationships it only seems to show students ON-ROLL - I want a list of all contacts and the associated student with relationship whether the student is on roll or a leaver.

 

In Reports Design Report I've chosen Contact to be the focus, then added in the contacts surname & forename - then clicked Students and added in Relationship and Forename/Surname.

 

When I run the report it only seems to populate the student information from those on roll

Interesting, I played around with this and found it outputted all our contacts whether the pupil was on roll or not.

Anyway you could try filtering on 'Student Status' and tick 'On Roll' and 'Leaver'