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