Report customizations
Part 7 of "11 things to know about customization"
Published: April 20, 2007
Customization and reporting are linked. The purpose of adding custom entities or attributes is to store information – the purpose of storing information is generally to report on it. Be sure to consider how you will be able to report on the data stored in customizations you add before you create the customizations.
On This Page
Reporting Solutions
Microsoft CRM includes Microsoft SQL Server Reporting Services as the primary reporting engine. And, with Microsoft Visual Studio you can create professional looking reports. See Create a report in 15 minutes or less for tips about how to get started. But truly mastering the full capabilities of creating professional SQL Server Reporting Services reports may take some time. If you do not already know how to do this, you can hire a developer to create more sophisticated reports for you. The reports provided with Microsoft CRM provide good examples and starting points for custom report development.
Excel reports
Don’t forget that you can export the results from views or Advanced Find queries into dynamic Microsoft Excel worksheets and pivot table reports. People in many organizations are already familiar with using Excel-based reports. Therefore, you may already have the skills to make these reports fit your need. The main limitations that these reports have are the same limitations of the Advanced Find feature:
| • | They can only return data from a single entity. Although you can use data in other entities as criteria in your query, you cannot return data from those entities. |
| • | You cannot create queries that show exceptions. For example, you cannot create a report that will show all the accounts that do not have an opportunity associated with them. |
There are a number of tricks you can use to overcome these limitations. If you are already familiar with how to connect Excel to SQL Server data sources and know a bit of Transact-SQL, you can use these reports to get a rapid head start. As soon as the report is created, you can access the Transact-SQL query that is retrieving the data and make modifications to overcome these limitations. The article, 2 ways to see combined account and order data, demonstrates some of these methods and also shows how to add the report so that it can be opened from the reports area of Microsoft CRM.
Filtered Views
If you are developing reports from the Microsoft CRM database you should use a set of SQL Server Views called “filtered views” rather than taking data directly from the base tables. These views use the user’s network account credentials to enforce security. Users of the report will see only the data that Microsoft CRM security would allow them to see in the application.
