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)
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.