Microsoft Dynamics CRM Team Blog: Including External Data in Microsoft Dynamics CRM 4.0 Reports

[Blog bot]

Источник: 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