Query Builder and Subscription Reports by Affiliation

Query Builder/Subscription Reports Filtered by Affiliation

When creating reports using the Query Builder, you can either get all the data or data filtered by affiliation. Often times, system administrators don’t want someone with an affiliation manager role to see data from other affiliations. Only 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: