When creating reports using the Query Builder, you can either get all the data or data filtered by affiliation. Often times, you don’t want the affiliation manager role to be able to see data from other affiliations. The admin role would likely want to see data across all affiliations.
To filter data in reports by the user’s affiliation running the report, you can add ‘AffiliationIndex IN (@CurrentUserAffiliations)’ in the where clause.
Subscription reports don’t allow ‘@CurrentUserAffiliations’ so you can’t just cut and paste the query with this condition in the where clause. The subscription recipient list may also contain users from different affiliations, and email address may not even be their username to successfully find the user and what affiliation they belong to. Therefore, if you want the subscription report to be filtered based on a recipient’s affiliation, then you have to hard code the affiliation in the where clause: