19 May 2008

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.

27 comments:

Pedro said...

Fantastic blog!
I'm attending a master bi degree, and I'm enjoying the CRM subjects...
Thanks for the blog!
Cheers!!
Pedro

Anonymous said...

Hi
How did your report appeard in CRM Report area after deploying from RS? I tried it few times but they are never showing in CRM. You also didn't mention what version of CRM you are using in this article...I assumed that 4.0 but maybe I'm wrong.

King regards,
pen_2

Anonymous said...

same with me ! report is not appearing in Report area in MS CRM, I am using 4, Please suggest if anything other need to be there

Jim Wang said...

Sorry for the confusion. This post is for CRM v3.0.


In CRM 4.0, reports has been treated as an entity. So you have to upload the report via CRM. However after install Microsoft Dynamics CRM Connector for SSRS, all reports upload from CRM will use the shared datasource(/OrgName_MSCRM/4.0/MSCRM_DataSource) which provided by SSRS connector, so you don't have to worry about it.

During the report design, just use a normal connection string(not the shared data source) connect to the development database, then you can upload the report to any environment via CRM, it will automatically change it's datasource to /OrgName_MSCRM/4.0/MSCRM_DataSource.

Anonymous said...

酒店經紀PRETTY GIRL 台北酒店經紀人 ,禮服店 酒店兼差PRETTY GIRL酒店公關 酒店小姐 彩色爆米花酒店兼職,酒店工作 彩色爆米花酒店經紀, 酒店上班,酒店工作 PRETTY GIRL酒店喝酒酒店上班 彩色爆米花台北酒店酒店小姐 PRETTY GIRL酒店上班酒店打工PRETTY GIRL酒店打工酒店經紀 彩色爆米花

咪摩兔 said...

桃園當鋪.桃園汽車借款.中壢環球當鋪
高雄當鋪.高雄汽車借款.統一當鋪
高雄汽車借款.高雄當鋪.川勝當鋪
嘉義汽車借款.嘉義當鋪.世界當鋪

Unknown said...

Thanks for the sharing and if you want to share Outlook data with your colleagues, welcome to check:
WinPST Share Outlook

Patrick Frye said...

I appreciate the work of all people who share information with others.
best dissertation writing service

Albert Barkley said...

Hi, your views are awesome. I never had seen such informative views on any other website. It will really help me in improving my Dissertation Writing Services UK skills.

Quân Đào said...

học kế toán thực hành cấp tốc
học kế toán thực hành cấp tốc
học kế toán thực hành tại cầu giấy
học kế toán thực hành tại thanh xuân
]học kế toán thực hành tại hà đông
học kế toán thực hành tại long biên
học kế toán thực hành tại long biên
học kế toán thực hành tại hải phòng
học kế toán thực hành tại bắc ninh
học kế toán thực hành tại tphcm
học kế toán thực hành tại quận 3
học kế toán thực hành tại hải phòng
học kế toán thực hành tại bắc ninh
học kế toán thực hành tại bình dương
học kế toán thực hành tại biên hòa
học kế toán thực hành tại vinh
học kế toán thực hành tại vinh
học kế toán thực hành tại huế
học kế toán thực hành tại đà nẵng
học kế toán thực hành tại đà nẵng
học kế toán thực hành tại đà nẵng
học kế toán thực hành tại hải dương
học kế toán thực hành tại hưng yên
học chứng chỉ kế toán
học kế toán ở đâu tốt

Albert Barkley said...

Custom CRM has been always a good option while collaborating with team members on a specific project or projects. Your post is good and will be much helpful to making changes in CRM according to needs. Dissertation writing service

writing tips said...

Thanks for sharing your valuable article about custom CRM. It is very informative post according to need .Cheap dissertation writing services

Unknown said...

Thanks for your article. This is arsh sran founder of Apk trendz,collection of whatsapp mods for android. Download whatsapp plus , gbwhatsapp, yowhatsapp and ogwhatsapp

OGEN Infosystem (P) Limited said...

This is an informative and valuable blog for me, that I have seen on your blog. Thanks for sharing it. Visit for
Offshore Anonymous Hosting

Jenny said...

Informative article.
itube studio software for pc

Aar Overseas said...

Best Immigration Services in Delhi for Canada|Best Canada Education Consultants in Delhi|Overseas Education Consultants in Delhi for Canada
Canada Immigration Services in Delhi | study visa consultants in delhi for canada | best immigration consultants in delhi for canada | canada pr visa fees

Appy said...

aptoide

Nicolay said...

https://bpserv.host - offshore bulletproof hosting company.

Michael said...

Thanks for sharing this wonderful article.
Fakaza Music 2020

Anonymous said...

Visit
Global Employees
Global Employees

sonam escorts said...


Thank you for sharing this information. My name is Sagar I'm an ENT Specialist in Nasik. I have more than 16 years of experience in Ear, Throat and Nose surgery treatment
ENT Specialist in Nasik

MIHIR KAUSHIK said...

Computer & Software Courses Training Institute in Pitampura New Delhi

Certificate Course in professional Graphics Designin

Learn Computer Hardware from Scratch. Most suitable for Job and Self business starting

This is Govt. Certificate Course designed to impart knowledge at a basic and advance level in computers for the common man. This certificate is used in various Govt. and Pvt.sector jobs

Android App Development Programing is one of the most widely used technologies in mobile development. join Best Andriod Training Institute in Pitampura

MIHIR KAUSHIK said...

This is Govt. Certificate Course designed to impart knowledge at a basic and advance level in computers for the common man. This certificate is used in various Govt. and Pvt.sector jobs

Dheeraj Singh said...

A computer course can equip individuals with essential skills such as computer programming, data analysis, software development, and more, which are highly sought after in the job market.
What is digital marketing? Everything you need to know
Dics innovatives Query
Best Computer Training Institute in Delhi
Graphic Designing Classes in Pitampura, Delhi
How To Learn About in Digital Marketing Course?

Tally institute said...

Digital Marketing institute in Laxmi Nagar


Web designing institute in Laxmi Nagar


Grapgic institute in Laxmi Nagar


tally Institute in Laxmi Nagar

Dheeraj Singh said...

Best Computer institute in Pitampura

Computer Course in Pitampura

Digital Marketing institute in Pitampura

Tally institute in Pitampura

Advanced Excel institute in Pitampura

Java Training institute in Pitampura

Python Institute said...

Unleashing the Power of Python: Choosing the Best Training Institute in Rohini
Python Training Institute in Rohini