Dynamics CRM 2011 On-premises – “CRMAF_” does not work for Custom SSRS reports

This is a small post. I hope this will help developers at least by saving a day.

To run a contextual custom SSRS report for Dynamics CRM 2011 on-premises, we need to give an alias with “CRMAF_” keyword to a Filtered View in our SQL query. For instance, you wish to run a custom SSRS report for an Account entity from an entity record, entity list area and reports area then provide an alias as shown in below query.

SELECT 

accountid

FROM

    FilteredAccount AS CRMAF_FilteredAccount

When you upload this a report with above query Dynamics CRM will replace the FROM table name with a sub-query parameter. To know more about “CRMAF_” magic please refer below URL.

http://msdn.microsoft.com/en-us/library/gg328288.aspx#SQLBasedPre_Filtering

In my case when I had uploaded a report using a simple query as specified above, Dynamics CRM did not replace it with a sub-query parameter on my development machine. I had also removed the report record from development machine more than once and re-imported it as afresh report with no luck. However the same RDL file was working well on another CRM on-premises server/machine. After some investigation I came to know that the problem is related to report data source connection string!!!

Below is the connection string that I had specified in my custom report data source properties.

Workaround

I had replaced the above connection string with some other server and database name and uploaded it again on development machine. Voila!! This change worked for me and was able to run my report contextually on my development machine.

Did anyone else face such problem? If yes then please feel free to share your understanding about the problem with the solution(s) as comments on this post.