The question is an easy one to ask: "How do I report the course details of learners from last year who have not returned so far this year?" but not easy to show in a report as it is difficult to filter outside the year that you are wanting to report on.

 

It is possible though and here is a report filter that should do it:

 

 

This should be placed in the Report filter string found in File\Report Properties of a report.

 

 

Although the filter may look daunting it boils down to using the SQL() function to execute an sql command that returns true if a person does not have registrations in 2017.

 

The SQL command can be split up into three parts for the purposes of explaining how it works:

 

... which when written out a little more legibly looks like the following:

select count(1) as RC

from entries as Ent

join vRepActivity as Act on Act.siteid= Ent.siteid and Act.activityid= Ent.ACTIVITYID

where (Act.Year= 2017 and Ent.peopleid=a.peopleid)

and which simply returns the number of registrations ( count(1) as RC ) in 2017 (where Act.Year= 2017) for a learner ( and Ent.peopleid=a.peopleid) from the tables entries and vRepActivity. The end result of this is that for every learner found in the filtering asked for when the report is run the select statement will be 0 or the number of registrations a person has in 2017.

 

... which if The Select statement  shows no registrations for a person ( when (select statement)=0 ) return the person's ID ( then a.Peopleid ) otherwise return zero ( else 0 )

 

The check to see if the person should be included 

a.Peopleid = ( the results from the case statement )

As the result of the case statement will be either 0 or a people ID this check will be true if the ID of the person in question ( a.PeopleID ) matches the results from the case statement and the person's registrations should be printed