I soon found limitation using this method. You can't use the same CRMAF_Filtered
Therefore if you have multiple datasets or UNION query that are using that filter more than once, it will not work. To avoid this issue, we can use the Explicit pre-filtering - the second method that CRM provides.
Let's say that you want to have a report that display single Account details and the associated related records information. However rather than building complex single query you want to break it down into multiple datasets.
- Create a parameter called @CRM_FilteredAccount. You can set the default value to just:
- Build your datasets using this parameter. Use the SQL Dynamic Query so that CRM will replace the parameter with the Advanced Find instance that you specify when running the report.
These are two of the dataset examples:
Hope this helps,