But when the user wants to create a report that needs additional totals, different formatting, summaries in the header etc. A custom report is required.
Do you use Filtered Views or Fetch?
Filtered Views allows the report developer to query underlying SQL data directly. 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.
- Figure: Filtered Views in the CRM SQL Database
Fetch is a proprietary query language that is used in Microsoft Dynamics CRM. It is based on a schema that describes the capabilities of the language. The FetchXML language supports similar query capabilities as query expression. It is used primarily as a serialized form of query expression, used to save a query as a user owned saved view in the userquery entity or as an organization owned view in the savedquery entity.
Now with that out of the way which one do I use?
- If you’re using CRM Online then you have no choice, you will use Fetch as CRM Online does not allow access to the underlying CRM SQL Database.
- If you are using On-Premise CRM and are *unlikely* to ever migrate to CRM Online then Filtered Views is the right choice.
- If you are using On-Premise CRM and there is *any* chance of moving the CRM Online then use Fetch (otherwise your custom reports will need to be re-written to use Fetch).
Fetch does not support RIGHT OUTER JOIN and FULL OUTER JOIN.
Fetch does not support EXISTS/IN condition with sub-query/expression.
An amount of 5000 returned records maximum.
No “UNION” selects.
You cannot specify group by / sum queries – You can only select the records in detail and then perform the aggregation in your report.
Number of entity join (link) limitations.
FetchXML reports cannot use non-CRM online data sources.
Learning curve – for report writers that are not familiar with FetchXML the syntax is quite different from SQL.
What do you need get started writing Fetch based CRM Custom Reports?
Get up and running quickly with Fetch:
- Create your report using the CRM Reporting Wizard
- Save the RDL file
- Import RDL file into Reporting Services Project
- Update and enhance report
- Upload back into CRM
More advanced users will have a boilerplate(s) for the various CRM report styles they produce and just build on top of the template.
Here’s an example of a before (CRM Wizard) and after (Visual Studio) for an Activity Report:
- Figure: Report created using CRM Wizard
- Figure: Report updated in Visual Studio
Five enhancements that required a custom report:
Company logo top left
Total call minutes in header
Total call count and minutes totals for staff
Links to regarding account (in a grouping)
Colour coded series for charts that relate back to the report data headings