You want to have a report in Dynamics CRM reference a database other than the Dynamics CRM database. Maybe you want to join external data, like data from your Dynamics AX ERP system, to account data in Dynamics CRM.
In many cases, the best answer is to use a data warehouse, Write a report that points to the data warehouse from an external reporting server, then display the report in Dynamics CRM using the link to URL report feature to point to another report server.
However, sometimes this is not practical. Maybe you don’t have a data warehouse or separate reporting server set up, and you simply want to include data from another database in your Dynamics CRM reports. This can be done.
First thing you need to know is that when you upload a SQL based SSRS report to your on premises CRM environment, the application overwrites the data source to use the shared MSCRM data source. This means that you cannot just add a data source pointing to another system in your SSRS report. It will fail to upload.
- Create your SSRS report in SQL data tools/BIDS and create an embedded data source pointing to your Dynamics CRM database.
- Make sure that the service account that your MSCRM services runs as has permission to read the other database. This database should either be on the same server as the CRM database, or a linked server should be created so both databases can be queried from the same SQL server.
- Write your data set query, be sure to specify the fully qualified table name of the external system database. You can join this table to the CRM database in your query to include data from both CRM and another system in the same report.
Using this approach you can also use CRM specific report functionality like prefiltering against other data sources. If you query the MSCRM database filtered views using the CRMAF alias prefix and join to another database using the fully qualified table name, prefiltering will still work in the report.