Microsoft Dynamics CRM 2013 includes reports that provide useful business information to the user. These reports are based on Microsoft SQL Server Reporting Services, and provide the same set of features that are available for the Microsoft SQL Server Reporting Services reports. The report definition (data and layout) of Microsoft Dynamics CRM reports are contained in an .rdl file, and the contents of the .rdl file conform to the Microsoft SQL Server Report Definition Language Specification.
1. System Reports (Out-of-box Reports):
Dynamics CRM 2013 comes with 25 out-of-box reports for viewing your business data. The following table shows a list of available reports and what data they get when you run.
|Account Distribution||Identify patterns in top revenue-generating accounts.|
|Account Overview||View a one-page overview of an account.|
|Account Summary||View a chronological summary of an account.|
|Activities||Display a list of activities.|
|Campaign Activity Status||Track campaign activities.|
|Campaign Comparison||Compare two campaigns.|
|Campaign Performance||Track the progress and status of campaigns.|
|Case Summary Table||View the patterns in cases.|
|Competitor Win Loss||Compare how your sales team performs against competitors.|
|Invoice||View an invoice and its line items.|
|Invoice Status||View your accounts receivable.|
|Lead Source Effectiveness||Compare your lead sources.|
|Neglected Accounts||Identify accounts that have not been contacted recently.|
|Neglected Cases||Identify cases that have not been contacted recently.|
|Neglected Leads||Identify leads that have not been contacted.|
|Order||View an order and its line items.|
|Products By Account||View products that are used by an account.|
|Products By Contact||View products that are used by a contact.|
|Progress against goals||View progress against goals|
|Quote||View a quote and its line items.|
|Sales History||Understand past sales performance.|
|Sales Pipeline||View anticipated potential sales.|
|Service Activity Volume||View the patterns in service activity volume.|
|Top Knowledge Base Articles||Identify the most frequently used knowledge base articles.|
|User Summary||View user contact and security role information.|
2. Custom Reports:
You can also create custom reports in Dynamics CRM 2013, there are two types of custom reports you can create in Dynamics CRM 2013: Fetch-based and SQL-based.
Fetch-based: Fetch-based custom reports use FetchXML queries, which are proprietary to Microsoft Dynamics CRM, to retrieve data for reports. These reports are introduced in Microsoft Dynamics CRM 2011 and use FetchXML queries to retrieve data for reports. You can deploy custom fetch-based reports to Microsoft Dynamics CRM Online and to on-premises Microsoft Dynamics CRM 2011. All reports that are created using the Report Wizard in the Microsoft Dynamics CRM 2011 are Fetch-based reports. You can deploy custom Fetch-based reports to Microsoft Dynamics CRM Online and On-Premises.
SQL-based: These reports use SQL queries to securely retrieve data for reports from filtered views defined by the system. These are the same reports that have been available for previous versions of Microsoft Dynamics CRM. The default reports that are shipped with Microsoft Dynamics CRM 2011 are SQL-based reports. You cannot deploy custom SQL-based reports to Microsoft Dynamics CRM Online.
Use SQL and filtered views to retrieve data for reports: Microsoft Dynamics CRM data and metadata are stored in a Microsoft SQL Server database named <organization_name>_MSCRM on the server that is running Microsoft SQL Server in the Microsoft Dynamics CRM installation. 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.
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.
The following sample SQL code returns all columns from the filtered view for an Account entity:
SELECT * FROM dbo.FilteredAccount
Filtered views also provide a way to pull Microsoft Dynamics CRM report data into Microsoft Office applications, such as Microsoft Office Excel and Microsoft Access. For a complete listing of all the standard filtered views organized by product area
Differences between the RDL File of SQL-based and Fetch-based reports: The following table lists the differences between .rdl files of SQL-based and Fetch-based reports in Microsoft Dynamics CRM.
|SQL-based Report||Fetch-based report|
|The <DataProvider> element value in the .rdl file is set to SQL. For example:
|The <DataProvider> element value in the .rdl file is set to MSCRMFETCH. For example:
|The query specified for retrieving data is in the <CommandText> sub-element under the <Query> element in the report definition (.rdl file) is a SQL query. For example, the query for retrieving all account names for a SQL-based report will be:
<CommandText>SELECT name FROM FilteredAccount;</CommandText>
|The query specified for retrieving data is in the <CommandText> sub-element under the <Query> element in the report definition (.rdl file) is a FetchXML query. For example, the query for retrieving all account names for a Fetch-based report will be:
<CommandText><fetch version=”1.0″ output-format=”xml-platform” mapping=”logical”><entity name=”account”><attribute name=”name” /> </entity></fetch></CommandText>