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.