Custom Reports in CRM 2011 using SSRS- Tips

As you all know there are mainly two types of Custom reports could be developed by using SSRS ( SQL Server Reporting Services ) 

•  SQL- based ( Microsoft MSDN says "For security reasons, you cannot deploy custom SQL-based reports to Microsoft Dynamics CRM Online") 

•  Fetch - based. ( Works in both On-Premise and Online versions)

Limitations of Fetch- based Reports:

The limitations of fetch-based reports are well explained in the following MSDN Blog post.

Reference:

"http://blogs.msdn.com/b/crminthefield/archive/2012/11/27/custom-reporting-in-microsoft-dynamics-crm-fetch-vs-filtered-views.aspx"

"

1. Fetch does not support RIGHT OUTER JOIN and FULL OUTER JOIN
2. Fetch does not support EXISTS/IN condition with sub-query/expression
3. An amount of 5000 returned records maximum
4. No “UNION” selects
5. You cannot specify group by / sum queries – You can only select the records in detail and then perform the aggregation in your report. 
6. Number of entity join (link) limitations
7. FetchXML reports cannot use non-CRM online data sources
8. Learning curve – for report writers that are not familiar with FetchXML the syntax is quite different from SQL." (Ref:http://blogs.msdn.com/b/crminthefield/archive/2012/11/27/custom-reporting-in-microsoft-dynamics-crm-fetch-vs-filtered-views.aspx)

Now if you start developing SQL- based report, please never forget the following tips.

"Filtered views exist for all Microsoft Dynamics CRM entities, including custom entities. Your custom SQL-based reports cannot read data directly from the Microsoft Dynamics CRM database tables. Instead, you must use the filtered views to retrieve data for your custom SQL-based reports."(Ref:http://msdn.microsoft.com/en-us/library/gg328467.aspx)

"SQL-based reports in Microsoft Dynamics CRM use the filtered views provided for each entity to retrieve data for the reports. Filtered views are fully compliant with the Microsoft Dynamics CRM security model. When you run a report that obtains data from filtered views, the Microsoft Dynamics CRM security role determines what data you can view in the report. Data in filtered views is restricted at these levels: the organization, the business unit, the owner, and at the field level."(Ref: http://msdn.microsoft.com/en-us/library/gg328467.aspx)

In simple words, when we use Filtered views for SQL- based reporting, CRM implements the same security model into our report, which is really cool.