I continue to be amazed at how easy it is to work with, extend, and build complete solutions with Dynamics CRM 4. Some Marketing users I support have been building Reports in the Report Wizard tool. This is a great tool for very simple reports. However, if you are looking for any kind of summary information, or calculations which are not built in as a calculated attribute, you can’t do that with the wizard.
I had heard that there were 3 easy to develop reports in CRM.
- Use the Report Wizard
- Start with the Report Wizard and Customize in Visual Studio
- Develop in Visual Studio
Option #2 looks appealing as it seems to hold out the promise of getting you 80% of the way to completion before you touch the RDL. However, there is one big problem: you won’t be able to Preview the report in Visual Studio. This is because the main Data Set for the report (named DSMain) is actually using Dynamic SQL to generate the query. The query builders which come with Report Designer do not support dynamic SQL, and even if they did, you would not be able to set the parameters the way you want to. I would say this option is only well suited for styling and adding a company brand to your custom CRM Reports.
This morning I tried Option #3 and I find this to be the easiest to work with. There did not seem to be any real limitation on how the report was designed. However, I can tell you that when designing your SQL queries, you want to be going against the set of Filtered Views in CRM which are designed to offer precisely this kind of access to the underlying data. I have found that as I build out my custom entities, attributes and relationships, all of these customizations generate a corresponding FilteredX view in the Organization data store. In other words, if you create a custom entity named “Photo” (I am removing any customization prefix for simplicity), should look to report on Photos from a View named FilteredPhoto. Additionally, Relationships to other entities will be manifested in these views as well. As you might expect, a Many-to-Many relationship which is defined between entitles (custom or system), becomes a view which can be used to represent that relationship. Not only are your customizations First Class Citizens in the CRM data schema, but easy access to the data is made available immediately.
Brian Berry is a Director of Technology Consulting with BlumShapiro, focusing on Microsoft Business Intelligence solutions, with a strong focus on Systems Integration, Master Data Management and PerformancePoint Services. He has been helping companies optimize their investments in Microsoft technology for over 12 years.