Custom SSRS reports added to CRM receive the benefit of certain functionality within Dynamics CRM. This includes pre-filtering, which gives you the ability to specify the scope of the report in order to return results quicker or focus only on relevant information as well as restrict access to data from records users who might not have rights to view based on your Security Roles. Additionally, placing reports in CRM extend the ability to access the reports. A user, who is outside the network, might not have access to the report server; but if the report is in CRM and an Internet Facing Deployment is established, the user would then be able to view the report.
With Dynamics CRM, we can apply these same benefits to reports with external data. Perhaps you wish to provide your Sales Reps with the Customer Statement from Dynamics GP or a report which summarizes order history. We would be able to use pre-filtering to only pull orders in GP for the Account in question or if your Sales Rep is on the road, still allow them to print out a Customer Statement without having to us a VPN to access GP. Great! So, I’ll just upload the report I created into CRM and be able access my external data. Not so fast. Even if you set your data source in SSRS to the external database, once it is uploaded to CRM the data source will be switched to CRM and the report will fail. Well, here is a method I have successfully used on multiple occasions to access information from an external database from within CRM.
Create a report in SSRS with Dynamics CRM as the data source. Then add a data set and query the field which is used as the unique ID of the record you will be referencing in your external data. Be sure to create an alias for the entity table you are querying and use “CRMAF_” as the prefix to enable prefiltering. For example, if you are reporting on data based on an Account then your query will look like this:
SELECT accountnumber
FROM FilteredAccount AS CRMAF_FilteredAccount
Add a sub-report on the report and pass your queried field to the sub-report.
Create another report to be used as the sub-report and add the external database as the data source. In your data set, query the information needed to build your report and add the parameter from the first report as part of the WHERE condition, like this:
SELECT * FROM RM00101
WHERE RM00101.CUSTNMBR = @accountnumber
After you have built your report, load both the main report and sub-report into CRM and associate the main report with the entity it is to be run against. Now, no matter if you are on the network or remote, your report will be able to access external data filtered against a specific Dynamics CRM record!
Questions about Scott’s method? Want to know more about SSRS reports? Contact Scott at sales@ktlsolutions.com or call 301.360.0001.
SCOTT FLORANCE | CRM Business Software Consultant
Scott Florance is one of the CRM Consultants at KTL and has proven his value as a member of the team since September 2013. Whether implementing a new CRM organization or adding to existing configurations, Scott has engaged clients with a positive and enthusiastic demeanor to help them meet their organizational needs. With four plus years of experience, Scott is familiar with CRM as both a power user and administrator. Scott received his bachelor’s degree in business administration from the University of Central Florida. He is a Microsoft Certified Technology Specialist for Dynamics CRM as well as a Certified Scribe Technician.
