Working with Microsoft Dynamics(TM) CRM 3.0

Your users will love their Microsoft CRM system if they have quick access to all of the reports they need. Unfortunately, most people wait until just before going live with Microsoft CRM to start thinking about and creating reports. Consequently, reports frequently get rushed and shortchanged on development and testing. So our first and most important tip is don't ignore reports until the end of your implementation process. Reports always take longer than you expect to develop and test!

Here are a few other tips to keep in mind when you're working with Microsoft CRM reports.

General

• Always keep backups of your report files, and never edit the live reports. If possible, save your reports in a version-control mechanism such as Microsoft Visual SourceSafe so that you can roll back to previous versions if necessary.

• Adding images or logos to your report will help improve their appearance, but adding too many can slow down the report's performance. Therefore, be mindful of the number of images used and their file sizes. Embedding images in the report or database (instead of referencing an external URL) provides for better portability.

• If you run a report with a graphic and a red X displays where the graphic should be, you're probably experiencing a problem in which Internet Explorer blocks a cookie from Reporting Services. You can explicitly allow cookies from the server running Reporting Services by modifying the Privacy settings within Internet Explorer (explained in Microsoft Knowledge Base article 908672).

• When using the Reporting Services Designer, be sure you are in the layout or data view if you click the Save button on the toolbar. If you click Save on the Preview tab, Visual Studio tries to save the output of the report instead of the report itself.

• If you create custom stored procedures or views for your reports, you should not add them in the Microsoft CRM databases. Remember, Microsoft does not support modifying or altering the SQL Server databases directly; you should create a separate database for this type of situation.

• Microsoft CRM conveniently creates two columns for each lookup and picklist field in the filtered view. You can reference the lookup or picklist value, or just reference the name directly. Make sure that your report writers know this so that they don't waste time trying to join filtered views to display the names.

• Two additional views (FilteredStringMap and FilteredStatusMap) provide the picklist and status reasons, respectively. You might need to reference these views for custom parameter lists.

Performance

• If possible, you should try to use an entirely separate server for your reporting needs instead of the live production database. Keeping reporting on a separate database server is absolutely critical for larger organizations, databases with large amounts of data, and companies with complex, time-consuming report queries. Moving reporting to a separate database server provides the following benefits:

  • You can add indices specific to the needs of the report queries without adverse effects on the transactional database.

  • If any particular report or load creates a performance bottleneck, you won't affect other Microsoft CRM users when the report runs. Data caching and snapshots might also help alleviate this problem.

  • You can create custom stored procedures and views as necessary.

  • You can configure refined security for report authors. For instance, you can limit which stored procedures and views a particular report author can access.

• When creating queries in your reports, do not use select * (to return all of the columns in your query). You will get better performance by selecting only the columns you need returned.

• Make sure that you modify the default pre-filters for each custom Reporting Services report you create to further limit the amount of data returned (thus improving performance).

• Perform filtering, calculations, and grouping in SQL Server rather than in Reporting Services where possible. SQL Server performs grouping operations more efficiently and quickly than Reporting Services.

• Because filtered views return a UTC and a local date for each date field, make sure that you also reference the UTC date when comparing dates in a report.