Dynamics CRM 4/2011 - Report Writing

Question

I'm looking for some guidance on report writing for Dynamics CRM.

Our current CRM is Dynamics 4.0 - which is few years old. It's an on-premise version. I have been working with this CRM for the past few days and I understand that I have an awful lot to learn.

My task is to write reports for this CRM. Built-in report wizard is nowhere flexible enough to to produce required reports. I'm finding it difficult to find resources and books on report writing for Dynamics CRM. I guess this is because Microsoft and their partners are keen on selling training sessions.

So far I know that I can download reports and import them into BIDS. I can modify reports in bids and the export them back into Dynamics.

One of my main concerns is that I don't know whether we'll benefit from migrating Dynamics CRM 4 to Dynamics CRM 2011. I'm keen on working with the most up-to-date system, but I must be able to justify migration to the newever version.

I have looked at third part solutions, such as Zap, but this is not something we are interested in because we'll have to extend Dynamics in near future to provide additional features to our customers.

Ideally I'm hoping to find a way to create a report layout which I can apply to all future reports. Additionally I'm aiming to write reports in such a way, that I can integrate them into our company site by pulling data out of dynamics.

So far I have managed to export reports from Dynamics CRM 4 to BIDS, but I'm not sure what is the right way to go about writing SQL. Reports that have been created with a Wizard, make a use of dynamic SQL. Creating reports from scratch, requires me to write querires against views in the Dynamics database.

Question is what is the right way to go about this?

Answer 1

Dynamics CRM 4 uses SSRS reports. If the underlying database is SQL Server 2005 you pretty much need to write the reports using Visual Studio 2005 w/ BIDS installed. If the underlying database is SQL Server 2008 I would recommend writing the reports in Report Builder, although you can also use Visual Studio 2008 w/ BIDS. I've heard Report Builder gives better layout options. SQL Server 2005 reports can easily be converted to SQL Server 2008 reports, but going from 2008 to 2005 is not easy, you basically have to start over from scratch, so make sure you are using the right IDE to create the reports.

Dynamics CRM 2011 supports SSRS and fetchxml. If you are writing reports for CRM 2011 Online, fetchxml is the only option since in the Online model you have no direct access to the underlying SQL database. For more information, refer to "Reporting for Microsoft Dynamics CRM Using Microsoft SQL Server Reporting Services" in the CRM 2011 SDK help file or navigate to http://msdn.microsoft.com/en-us/library/gg328097.aspx

To write SSRS reports for CRM 4, I would refer to the Report Writers Guide found in the CRM SDK documentation or @ http://msdn.microsoft.com/en-us/library/bb955081.aspx. It is possible to write these reports from scratch, but not terribly easy or intuitive until you really get them down.

Microsoft offers an online book for SSRS @ http://msdn.microsoft.com/en-us/library/ms159106.aspx