Best practices for reports in Dynamics CRM 2013

The following best practices can help you to produce reports that are easier to write, comply with established standards, and execute with improved efficiency.

General best practices: This section provides best practices for creating custom SQL-based and fetch-based reports.

• Use an existing report to create custom reports: Check whether there is an existing report in Microsoft Dynamics CRM that is close to the design that you are looking for. Download the report definition of the report, and then modify the RDL file instead of creating a new one from scratch. By doing this, you will save development time and reduce report writing errors.
• Use Microsoft Dynamics CRM formats for currency, number, date and time, calendar: The FilteredUserSettings view contains information about currency format, date and time format, number format, negative number, starting day of the week, calendars, and other formats. Microsoft Dynamics CRM provides the fn_GetFormatStrings database function to obtain the date, time, number, currency, and calendar formats from the FilteredUserSettings view.
• Set the page size: Reporting Services does not provide explicit page orientation, such as portrait and landscape modes, or preset page sizes. Standard Microsoft Dynamics CRM reports were designed for 8.25 x 11 (portrait) or 11 x 8.25 (landscape) page sizes that work for both US letter and A4 paper.
• Back up your reports: Make backup copies of your reports and store them on a computer other than the reporting server.
• Define truncation if needed: Text wrapping is the default behavior for a text box report item in Reporting Services. This means that, unless indicated otherwise, all text will wrap at the defined width of any text area and then grow vertically. If truncation is specified, a text box will truncate text at the width of the text box within the specified padding (default is 2pt left and right.) Any maximum length truncation beyond this will require custom coding. The default Microsoft Dynamics CRM reports are set up with tool tips to show static text or values from data fields when the user hovers the mouse pointer over the report item. If you use truncation, consider setting the ToolTip property to the field value so that the full text will appear when the user hovers over the truncated text.

SQL-based reports: This section provided best practices for SQL-based reports only.

• Writing stored procedures: Adding custom stored procedures to the Microsoft Dynamics CRM database is not supported. However, you can create a separate database and write stored procedures to that database.
• Limit text length and number of items in charts: Microsoft Dynamics CRM reports use only some of the possible chart types from Reporting Services.For any chart type, limiting label length and number of items is recommended for the chart contents to be displayed correctly. When Reporting Services displays a chart with long labels, the chart itself becomes too small to be usable. There are several ways to do this:
  • Limit your chart label length explicitly, truncating it if it is necessary.
  • Consider limiting the number of items displayed in charts.
• Use embedded images in a report: The easiest way to use images with Reporting Services is to put the images into a database. If the images are not in a database, you can use embedded images in .png, .gif, or .jpg formats in a report. The image files that are used by Microsoft Dynamics CRM image files are located in the C:/Inetpub/wwwroot/_imgs/ico folder on a default Microsoft Dynamics CRM installation.

Advertisement

Improve performance of reports in Dynamics CRM 2013

General: These guidelines are applicable for both SQL-based and Fetch-based reports.

• Limit a report to display information from a specified time period, instead of displaying all records in the Microsoft Dynamics CRM database.
• Pre-filter a report so that the dataset is limited.
• Calculate aggregate totals by using SQL code or aggregations in a FetchXML query, instead of passing raw data to Reporting Services and grouping.
• Limit the number of datasets used, if possible.
• When you compare dates, use the UTC date fields for comparisons. For example, compare the createdonutc fields and not the createdon fields in a filtered view or the FetchXML query.

SQL-based Reports: These guidelines are applicable for SQL-based reports only.

• Don’t create a report that uses a large dataset or a complex SQL query available on-demand to all users.
• Don’t select all columns from a Microsoft Dynamics CRM filtered view. Instead, explicitly specify the columns that you want to select in the SQL statement.
• Use SQL stored procedures instead of inline SQL.

Reports in Dynamics CRM 2013

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.

Name	Description
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: <DataProvider>SQL</DataProvider>	The <DataProvider> element value in the .rdl file is set to MSCRMFETCH. For example: <DataProvider>MSCRMFETCH</DataProvider>
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>