Friday 11 January 2013

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.

image

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.

7 comments:

  1. Yes I face this issue thank you very much

    ReplyDelete
  2. Nice and really helpful post.

    ReplyDelete
  3. Have you tried naming the datasource as CRM and server name as localhost. If you do that, then no matter which server you run that report on, it should work. Give it a go...

    ReplyDelete
  4. Thanks! I had the same issue as well. This fixed it.

    ReplyDelete
  5. Hello Sapan,

    I did not try with localhost. I would try it on this weekend and update you for the same.

    ReplyDelete
  6. Thanks i had this exact issue, have been looking for hours and this resolved my issue. Very helpful

    ReplyDelete
  7. You just need to use shared datasource.

    ReplyDelete