We’ve probably all dealt with CRM reports in the past. You know, the built-in FetchXML ones? But how many of us have dealt with a SQL report for an online CRM instance? Sometimes the client might ask for a report that is a lot more demanding than what the standard Fetch report is capable of. Unfortunately, CRM does not allow reports using anything other than FetchXML.
Imagine we need to create a complicated SQL report that is outside of the CRM instance, but needs to be accessible through CRM. This would preferably be done with the least amount of clicks and hacky solutions possible so the everyday users of the instance could navigate to the report without any additional training. To do this, we will need two things: A local read only copy of the CRM database and an SSRS server.
In my case, I was lucky to already have the first. My client had custom replicator software running in the background, updating a local read only database with data from CRM. This does not mean you have to write one for yourself from scratch, you could simply use an existing data integration tool with SSIS. As long as you have a dedicated database server (Preferably two: Test and Prod) to house the local DB and keep it frequently updated throughout the day. The second is the SSRS server, where the reports are hosted. The users are going to access the reports on here, so make sure the IT team has allowed a certain level of access to the server via a proxy server or something along the same lines.
Now that you have a local copy of the CRM Database and an SSRS server, you can start writing your SQL reports and uploading/testing them on the SSRS server.
Once the reports are complete and you are generating results on the SSRS server, it’s time to give them a home on the CRM instance.
First, create a simple HTML file with links to the SSRS reports. You can get the proper links to the reports from the Reporting Services Configuration Manager on your SSRS server.
Select the “Web Service URL” tab. Then click on the link under the “Report Service Web Service URLs”. This will lead you to a webpage containing links to all your uploaded reports:
Just click on the report you want on your custom webpage and copy its URL to include it in your HTML. These links connect to a specific report and nothing more, which means the user will not be able to click on a “Home” button, for instance, that would lead them to the SSRS configuration/management page.
An example of a simple yet visually appealing custom reports page. You can play around with the colors to match the CRM/Client color theme.
Next, upload the custom reports webpage you created as a web resource to CRM. After that you must decide how the user can access this webpage. I would suggest adding a button to the ribbon which links to your webpage. Thus, the result will look something like this:
A report page right inside your CRM!