Adding SQL Reporting Services reports to CRM

As you are probably aware, Microsoft CRM 3 and 4 uses SQL Reporting Services for creating reports. If you are familiar with this tool from non-MS CRM implementations, there are a few tricks for getting the reports to work correctly. Please read the CRM SDK and the report writers guide for specific details on parameter naming and some other stuff.

The part that is the major deviation from normal SQL RS work, is how to deploy reports. Even though normal deployment might seem to work, the report won't be recognized by CRM properly.

So, what do you need to do?
1. Create a report that does NOT use a Shared Datasource. Instead create an embedded datasource called CRM that connects to the CRM database server. If you are going to use the Filtered Views, you have to use Windows Authentication. Save and build the new report (DO NOT deploy to the server).

2. Open CRM, Go to "Workplace" and select "Reports". Click "New" in the list. You will see a form where you can select the rdl-file and also select where in CRM the report will be available. Select the rdl-file that resides in the VS Project folder. Press Save.

If, at this point, you had used a Shared Data Source, you would be getting a very complicated error, as described by Menno here: http://blogs.msdn.com/mscrmfreak/archive/2006/04/27/584595.aspx

If all went well, the report will be uploaded correctly.

When uploading the report, CRM will replace the datasource in the rdl with the standard shared datasource. So, your report will still be movable.

Also, when uploading reports like this, CRM will hide all parameters starting with "CRM_". There are several parameters that CRM will fill with data for you if they exisit. For instance the parameter "CRM_URL" will be set to "http:///CRMReports/viewer/drillopen.aspx". This enables the report to create drill-down functionality that loops back into CRM (since there is very good support for URL-addressability in CRM).

Please note that all deployment of reports to CRM should be done in this manner and never directly from Report Designer. This is a bit of a hazzle since it is a bit tedious compared to just deploying from the Report Designer.

In CRM 4 there is a very nice wizard in the application for generating reports, even though it is not nearly as advanced as the report designer.

Gustaf Westerlund
Microsoft Dynamics CRM Consultant

WM-Data/Logica CMG
www.logicacmg.com

Integration basics

As I have been writing, I am currently involved in a rather large integration project. For those of you who don't have experience of working with larger integration projects I would like to just share my view of how to set up the architecture connecting Microsoft Dynamcis CRM with the integration engine (BizTalk, WebSphere, Sonic and more). Bellow, I will refer to the Integration Engine, simply as "the Bus"

First of all, communication with the bus can be divided into two different classes:

1. CRM Initiated Communication
2. Bus initiated communication

In the picture to the right, these two are represented with a connection each (connection 1 = CRM Initiated Communication, connection 2 = bus initiated communication)

CRM initiated communication might be considered the simplest, since it mainly controlled from CRM. It can be code that runs from aspx-pages, callouts/plug-ins or workflow addons. It simply consumes the webservice at the CRM adapter on the bus.

Bus initiated communication is set up by the bus consuming a customly created web service. This "proxy" webservice translates from integrationmessages and datastructures to CRM native communication.

Integration is then definied by defining messages that uses either connection 1 or 2. Each message has two parts, Request and Response, and the response is the syncronous answer to the request. Assyncronous messages should be set up as two individual messages one for each adapter.

So, why create a proxy web service? Why cannot the bus connect directly to the CRM web service? The reason for this is usually that the communication is defined using messages (as described above) and the person responsible for the CRM Adapter on the bus cannot be expected to have any CRM knowledge in general or specificly CRM Webservice knowledge, hence that is the CRM developers job.

These are some of my experiences and reflections on integration with a centralized integration architecture. If you have any comments or you have some other method that you use, please leave a comment, so that we can discuss it further.

Gustaf Westerlund
Microsoft Dynamics CRM Consultant

WM-Data/Logica CMG
www.logicacmg.com

Busy, busy + SDK

It has been some time since my last entry and it is mainly due to the fact that I am currently involved up to my ears in two large integration projects with Microsoft Dynamcis CRM 3. Very interesting and demanding but the problems we are facing are mostly project specific and not anything I believe you would find interesting and my customers probably wouldn't want me to talk to much about. When I run into something that I think will be of interest, and I have the time, I will of course let you know.

Personally, I havn't had much time to dig into CRM 4.0 yet but I noticed that the SDK has been released. You can find it here: http://www.microsoft.com/downloads/details.aspx?FamilyId=82E632A7-FAF9-41E0-8EC1-A2662AAE9DFB&displaylang=en

Happy coding!

Gustaf Westerlund
Microsoft Dynamics CRM Consultant

WM-Data/Logica CMG
www.logicacmg.com