Use Shared Data Source for custom CRM report

Recently I have been asked to modify some reports. In my current project we have 4 environments (Development, Training, UAT, and Production) and we have custom reports for each environment. The reports for each environment are different because the data source is different. So the deploy administrator complain about numbers of report. They come and ask me if there is a way to have one report for all environments.

So this is how I managed to do it:

If you have a look the CRM reports in report manager, there is a Data Source call: MSCRM_DataSource
I have noticed that actually all CRM reports use this data source, so why can’t I use it for our custom reports? I think it shouldn’t be a problem.



In Visual Studio, open the report project, Add a Shared Data Sources call: MSCRM_DataSource, it is important to keep it as same as CRM’s.
The connection string just uses the development environment, e.g.: Data Source=crm;Initial Catalog=org_MSCRM



Once finish it, upload the report rdl file by Report Manager, e.g http://crm/reports
Notice that you shouldn't upload it from CRM directly because CRM doesn’t support this technique. (You may get an error if you do it from CRM: Object reference not set to an instance of an object.)

So do it from Report Manager. After I upload the report, it appears in CRM Report area. That's good, however I can't edit the report! The error message is: String was not recognized as a valid DateTime.

Microsoft actually has a KB (920125) for this error: http://support.microsoft.com/kb/920125. The KB says: "This problem occurs because the Microsoft SQL Server Reporting Services report contains functionalities that are specific to Microsoft Dynamics CRM. For example, the Microsoft Dynamics CRM Pre-filtering functionality is specific to Microsoft Dynamics CRM."

Ok, I see. I remember the default CRM pre-filtering function is 'modified on' which is a datetime data type. If you upload the report from CRM, the report property may different with what you do from Report Manager.

So where is the pre-filtering values saved? If you open SQL Server Management Studio, expend ReportServer database, take a look the Catalog table. All reports information is saved in this table. I noticed there is a property element for all CRM reports: <ModifiedOnDateTime>2008-03-29T17:59:15</ModifiedOnDateTime>

It is a datetime data type and it doesn’t exist in the custom report! Also I noticed that the <OriginalName> element needs to be added as well to make the CRM Download Report function working properly.

So, this is how I add these elements into the custom report, the SQL query needs to run against the ReportServer database:

DECLARE @ReportName AS varchar(50)
SET @ReportName = 'My CRM Report' -- Change to the report name

DECLARE @ModifiedDate AS datetime
SET @ModifiedDate =(SELECT ModifiedDate FROM Catalog WHERE Name = @ReportName)

UPDATE Catalog
SET Property = REPLACE(CONVERT(varchar(MAX), Property), '</Properties>',
   '<ModifiedOnDateTime>'+ CONVERT(char(19), @ModifiedDate, 126) +'</ModifiedOnDateTime><OriginalName>'+ @ReportName +'.rdl</OriginalName></Properties>')
WHERE (Name = @ReportName)

It works like a dream after run a iisreset on the CRM server. Now I can edit the report without any problem!

By using this technique the advantage is: all environments will share the default CRM data source, developers don’t have to create different copies of report for each environment.