Best practices for reports

Article
11/28/2016

Applies To: Dynamics 365 (online), Dynamics 365 (on-premises), Dynamics CRM 2016, Dynamics CRM Online

The following best practices can help you 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 Fetch-based and SQL-based reports.

Use an existing report to create custom reports

Check whether there is an existing report in Microsoft Dynamics 365 that is close to the design you are looking for. Download the report definition for that 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 365 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 365 provides the fn_GetFormatStrings database function to get the date, time, number, currency, and calendar formats from the FilteredUserSettings view.

Use these resources to correctly format data values in your reports.

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 365 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 365 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.

Best practices when you create reports that include date and time fields

When you create reports that use date and time fields, be aware of the following:

To be consistent with the date and time values in Microsoft Dynamics 365, when you create reports that use a Coordinated Universal Time (UTC)-based field (Time-Zone Independent or Date Only) don’t convert the value to a regional time-zone based (User Local) value.
If you make a change to the date and time behavior of a field in an entity, you may need to regenerate reports that use that date and time for the report to display the field correctly.
- Regenerate Report Wizard reports. Edit the report using the Report Wizard with the same parameters to regenerate the report.
- Regenerate reports based on a managed solution. For managed solutions, instead of editing the report, re-import the solution to regenerate the report.
- Regenerate custom reports created with Microsoft Visual Studio. Update the date and time fields in Visual Studio as necessary and republish the report. More information: Modify an existing SQL-based report using SQL Server Data Tools

For more information about date and time fields, see Behavior and format of the date and time field.

SQL-based reports

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

Writing stored procedures

Adding custom stored procedures to the Microsoft Dynamics 365 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 365 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 limit text length and items in charts:

Limit your chart label length explicitly, shortening it if necessary.
Consider limiting the number of items displayed in charts. For more information, see Example: Limit the number of items displayed in a chart.

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 365 are located in the C:/Inetpub/wwwroot/_imgs/ico folder on a default Microsoft Dynamics 365 installation.