MS CRM Autofilter gotcha

I am building a report using SSRS that I intend to use as an auto-filtered report inside of MS CRM (see http://blogs.msdn.com/b/crm/archive/2009/03/06/microsoft-dynamics-crm-pre-filtering-tips.aspx). The report pulls data from several views. Everything seemed to be working fine in development until I imported the report into CRM, then parts of the query seemed to stop working.

If you read the end of that article, it points out that all the entities referenced in the FROM or JOIN clauses will have a default filter applied to them that will limit what is included in the query to only records that were modified in the last 30 days.

In my case, I was using a parent entity with 4 outer joins, and I was only retrieving some of the data because some of the related tables had data that was not modified recently.

My solution to this problem was to create a single report that uses only one view with the CRMAF alias, and it used a sub-report that did not have the CRMAF alias on the views. This permitted me to associate the parent report with the entity, and pass the ID’s to the sub-report where there was no filtering.

In my case, I was creating a custom invoice for a custom invoice entity, so I created my parent report with just the one filtered view and gave it an alias of CRMAF_custominvoice. I embedded the sub-report in a table (tablix for VS 2008) control detail band, and gave the sub-report the ID value to pass as a parameter. In the sub-report, I had the rich and complex SQL that I wanted using filtered views, but not using the CRMAF alias.