AXForum  
Вернуться   AXForum > Microsoft Dynamics CRM > Dynamics CRM: Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 12.06.2009, 04:31   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
Microsoft Dynamics CRM Team Blog: Including External Data in Microsoft Dynamics CRM 4.0 Reports
Источник: http://blogs.msdn.com/crm/archive/20...0-reports.aspx
==============

CRM MVP Joel Lindstrom, a Solutions Consultant for CustomerEffective, blogs about BI solutions.

So you want to create a report that references data from another database.  Perhaps you want to correlate your CRM data with your ERP data to see how CRM activities are affecting your orders in financials.  Of course, you could do an integration and bring the ERP data into CRM.  However, an integration can require a big investment of time and money, and a report can be an affordable alternative if you don’t need the data for other purposes.

Another reason for doing this is if you want to have reports reference a mirrored copy of your CRM database, so heavy reporting doesn’t impact CRM performance.

The Challenge

To understand the challenge of reporting from external databases from CRM, we need to discuss the way that CRM reports data sources work.  When you upload a SSRS report to CRM, the data source of the report is modified to point to the CRM environment to which the report is uploaded.  For example, if your datasource referenced in your SSRS report is Contoso_MSCRM and you upload the report to a different environment with database ACME_MSCRM, when you upload the report, the data source will be changed to ACME_MSCRM.

This is typically a good thing, as it enables us to write a report for one environment and upload it to multiple other environments without having to modify the data source.  However, in this case, it creates some challenges.

Say I have a report where my Microsoft Dynamics GP database is the data source.  If I upload the report to CRM, it will change the data source to CRM, and the report will not work.

One option is to upload the report directly to the report server, bypassing CRM reporting.  When you upload the report directly to the report server, the report retains the original data source.  You can then make the report accessible to CRM users by creating a new report record and selecting “Link to Web Page” and adding the URL of your report on the report server as the “Web Page URL.”

This is not always a great option.  You lose certain CRM report functionality, like prefiltering, and users who don’t have access to the Report Server (such as users who access CRM via Internet Facing Deployment) will not be able to run the report.

The Solution

In this example, my Microsoft Dynamics CRM database is called CRM_MSCRM, and I have a copy of my CRM database called Mirror_MSCRM
1.  Create SSRS report, selecting your production MSCRM database as the data source.  In this case I selected CRM_MSCRM as my data source


2. Write your query referencing the external database.  For example, in this case, I want to select my accounts from the mirrored database.  This is how I would write the query :

SELECT * FROM MIRROR_MSCRM.DBO.ACCOUNT .csharpcode, .csharpcode pre{ font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/}.csharpcode pre { margin: 0em; }.csharpcode .rem { color: #008000; }.csharpcode .kwrd { color: #0000ff; }.csharpcode .str { color: #006080; }.csharpcode .op { color: #0000c0; }.csharpcode .preproc { color: #cc6633; }.csharpcode .asp { background-color: #ffff00; }.csharpcode .html { color: #800000; }.csharpcode .attr { color: #ff0000; }.csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em;}.csharpcode .lnum { color: #606060; } The result is that our report data source is our production database, but the query will return data from the external database.  This is a good start, but there is one more thing we will want to do.

3.  Join your query to the Filtered Views of your CRM database.  This will allow us to use prefiltering, and in the case of your mirrored database, this will prevent security issues, as users will only return records that they have a right to view.

SELECT * FROM MIRROR_MSCRM.DBO.ACCOUNT inner joinFilteredAccount AS CRMAF_FIlteredAccount ONMirror_MSCRM.DBO.Account.accountid=CRMAF_FilteredAccount.accountid
Now you can upload your report through CRM, use CRM prefiltering, reflect data from your external database, and your CRM data will be secure. 

One consideration is that the user that is running the report will need to have database read permissions on the external database to be able to read the report.  Also, if you are running the SRS Data Connector, you will need to give the NT AUTHORITY\NETWORK SERVICE read/select permissions on the external database.  After you do that, IFD users will be able to run the report.

Cheers,

Joel Lindstrom

CustomerEffective blog



Источник: http://blogs.msdn.com/crm/archive/20...0-reports.aspx
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
Microsoft Dynamics CRM Team Blog: List Web Part for Microsoft Dynamics CRM 4.0 Deployment Scenarios Blog bot Dynamics CRM: Blogs 0 30.01.2009 22:05
Microsoft Dynamics CRM Team Blog: Microsoft Dynamics CRM 4.0 Bookshelf Blog bot Dynamics CRM: Blogs 1 22.01.2009 04:46
Microsoft Dynamics CRM Team Blog: Reports for CRM 4.0 using SQL Server 2008 and Report Builder 2.0 Blog bot Dynamics CRM: Blogs 0 11.11.2008 08:05
Microsoft Dynamics CRM Team Blog: Data Migration Manager Tips and Tricks Blog bot Dynamics CRM: Blogs 0 02.09.2008 22:05
Microsoft Dynamics CRM Team Blog: Top 14 Microsoft Dynamics CRM Sites and Downloads Blog bot Dynamics CRM: Blogs 0 14.07.2008 13:05
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 22:29.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.