[acs] Facility Report

[kearton]

Does anyone know if it is possible to get a report from Facility in text format which lists all students followed by the names (or staff codes, or email addresses, etc) of all their teachers, please?

e.g

Joe Bloggs, Mr Apple, Ms Banana, Mrs Cherry, Mr Durian, Mr Elderberry, Mrs Fig, Mr Grape

Jane Smith, Mr Alpha, Mr Bravo, Mrs Charlie, Ms Delta, Mrs Echo, Mr Foxtrot, Mr Golf

etc etc

[TS1988]

Does anyone know if it is possible to get a report from Facility in text format which lists all students followed by the names (or staff codes, or email addresses, etc) of all their teachers, please?

e.g

Joe Bloggs, Mr Apple, Ms Banana, Mrs Cherry, Mr Durian, Mr Elderberry, Mrs Fig, Mr Grape

Jane Smith, Mr Alpha, Mr Bravo, Mrs Charlie, Ms Delta, Mrs Echo, Mr Foxtrot, Mr Golf

etc etc

 

Not sure if this gives you what you want..take a look at the screenshot and let me know. Easily exported to excel.

[kearton]

Looks like the right sort of thing. Many thanks!!!

[TS1988]

Great, no problem!

[kearton]

What I (now, contrary to my initial post, sorry) need is the staff email address (e.g forname.surname) but the Email field in our Facility is blank. D'oh!

The "Name" field contains "Mr Smith" or "Mrs Jones" etc... no forenames.

The "Forename" and "Surname" fields have the correct information, but is there a way to append the content of 2 fields into 1 field for a report please?

See attached... what I tried doesn't work...

 

Edited March 22, 2013 by kearton

[kearton]

Managed to get the names concatenated by using a + instead of an &.

I also tried populating the Email field and using that but it gets left blank for some students... the pattern seems to be if the students have multiple teachers teaching the same subject, and it doesn't list all the teachers email addresses, even if it DOES list all the teacher names on the same report. I'm really not liking Facility...

[tli]

The issue is more to do with the complexity of the data rather than an problem with Facility.

 

The reporting items that you seem to be using will pull through the email address of the first member of staff for each subject that it comes to. If that member of staff hasn't got an email address then you'll get a blank. What you need is to produce a list of staff for each individual subject. You should then have access to the email addresses.

 

The problem is that this may not give you a table in the format that you were looking for.

 

Once you've got lists, there are various methods of dealing with them using something called 'inline' filtering or possibly a range of inbuilt functions.

 

If you post what you've got so far, I might be able to suggest something.

 

As far as things like concatenating are concerned, Facility has it's own syntax which you'll need to get to know.

[SkywOrca]

Not sure if this is still a report that you will need, or if the advice already given has achieved what you need, but there are ways to get Facility Reports to do pretty much what I think you're after. As has been stated already, however, it's as much down to the quality of your underlying data as to how well any report will run. Also, whilst I've use forename/surname to create this report, if you have valid email addresses in the email field for all your teaching staff you should probably use that instead as it may be more accurate than this method.

 

Firstly, you can use the inline list manipulation functionality to get the system to create a list of items in a format that you need:

Student.Groups(Teachinggroup.Lecturer.Id > '', Teachinggroup.Lecturer.Forename + '.' + Teachinggroup.Lecturer.Surname)

This works through each item in the list node (in this case that would be Student.Groups) and first checks if a filter condition returns true (Teachinggroup.Lecturer.Id > '') to include selected items from the list, this should eliminate any blanks where you have groups with no teachers. It will then return whatever values you specify as a list (Teachinggroup.Lecturer.Forename + '.' + Teachinggroup.Lecturer.Surname). Note that when you're inside the inline area you should remove the list section from the data elements - when you press F9 and pick an additional data element to include, for example, the result that gets inserted would still have the "Student.Groups." section included. Student.Groups.Teachinggroup.Lecturer.Personal.Email should become Teachinggroup.Lecturer.Personal.Email, for example.

 

You may still have some issues with the data returned from this list, for example, you might have the same teacher for several groups for any given student, so you can remove these duplicates, and you can also perform a basic alphabetical sort on the values in your new list, albeit that this will sort by forename first in this case:

Sort(Union(Student.Groups(Teachinggroup.Lecturer.Id > '', Teachinggroup.Lecturer.Forename + '.' + Teachinggroup.Lecturer.Surname)))

 

To get this list out as a single line per student you can use the generate title functionality, but the problem here becomes how to identify a suitable title to give to the items so that they will appear neatly in the pivot. For this type of report, I usually use the Apply function, but only really as a shortcut. Apply will enumerate a list of items and iteratively apply a function to each value in turn. It was the older functionality that achieved the result we got above using the new inline filtering. What Apply does that we can use here, however, is give you an "applyposn" variable that returns the index of the current iteration value from the list, we can then use this value to create a "Teacher 01", "Teacher 02" formatted title for each value, such that they will appear neatly in the report:

Apply(Sort(Union(Student.Groups(Teachinggroup.Lecturer.Id > '', Teachinggroup.Lecturer.Forename + '.' + Teachinggroup.Lecturer.Surname))), v, 'Teacher ' + Right('0' + (applyposn + 1), 2))

This includes a leading zero as the default sorting that would apply is alphabetical, and would put 10, 11, 12 etc. before 2, 3, 4. Make sure you select Generate as the title type.

 

 

When you run this report, you should be able to export the result as a csv formatted document. Facility reports list functionality and internal variables are what make it an extremely powerful reporting tool - they can seem bewildering to begin with, but if you can wrap your head around them you can come up with some extremely complicated yet impressive reports. Also, I should probably point out that there is an active and supportive community available over on the Advanced Learning Forums.