Anything that’s been done twice is usually better. That includes double cream and double barreled. Except that report pre-filtering does not work if you need a filtering entity to be included more than once in your SQL.
Consider this totally random piece of SQL:
select a.revenue / b.revenue from FilteredAccount a inner join FilteredAccount b on a.parentaccountid = b.accountid where b.revenue is not null
You cannot just apply pre-filtering because alias CRMAF_FilteredAccount cannot be used twice in the same statement. Not the most efficient but reasonable workaround includes temporary tables to get filtered records first and then use them as needed.
declare @accounts table(accountid uniqueidentifier) insert @accounts select accountid from FilteredAccount CRMAF_FilteredAccount select a.revenue / b.revenue from FilteredAccount a -- apply filter to accounts inner join @accounts a1 on a.accountid = a1.accountid inner join FilteredAccount b on a.parentaccountid = b.accountid -- apply filter to parent accounts inner join @accounts a2 on b.accountid = a2.accountid where b.revenue is not null
Time for double distilled.