Update: Its 2020, is this post still relevant? YES!
This has been one of my most popular posts. The fact that it was written in 2018 might turn people off, but the info is still valid and is applicable to the latest version of Dynamics 365 (Customer Engagement) and Model-Driven Power Apps.
The number one issue/question many times is because people are trying to use a more recent version of Visual Studio (SQL Server Data Tools) when the requirements (even for the latest Report Authoring Extensions) is SQL Server Data Tools 2015. That is not a typo. Yes, that is 5 year old version. Yes it still works. No, Power BI paginated reports is not the replacement. Note there is a new download link.
If you want more in-depth information and become a SSRS wizard, then take a look at Megan Walker’s SSRS REPORTS FOR CDS & D365 online class.
On to the post:
- The process to set up a workstation to create SQL Server Reporting Services (SSRS) reports for Dynamics 365 involves a lot of steps and causes some confusion.
- The report authoring tool is Visual Studio, but a full licensed version of Visual Studio is not required.
- The Dynamics 365 Report Authoring Extensions has been recently updated to address TLS 1.2 requirements of Dynamics 365 v9
A couple of years ago I wrote a posting on how to set up a workstation to create SSRS reports for Dynamics 365/CRM. It turned out to be one of my more popular posts. If you are writing reports for older versions of Dynamics 365/CRM, those instructions are still very relevant.
Since that post, there have been a couple of updates and also an issue with connecting to Dynamics 365 v9 in regards to TLS 1.2 when using an older version of the Report Authoring Tool.
Recently, Microsoft has released Dynamics 365 Report Authoring extensions for Dynamics 365 v9.
This post is updated to reflect the update tools and processes to get you creating SSRS reports for Dynamics 365 in no time!
Note that these instructions will also work if you want to create SSRS reports for Model Driven PowerApps on a Common Data Service environment.
Visual Studio Data Tools
The first step is that you will need to install Visual Studio Data Tools 2015. You DO NOT need to have a fully licensed version of Visual Studio 2015 Professional or Enterprise. However, if you do have Visual Studio 2015, you will be able to write SSRS reports from that version after following these steps.
Note that the Visual Studio Data Tools are FREE.
UPDATE (August 2020) get the SSDT tools here instead:
This will download the full ISO image. I got a new laptop and had to install the tools. I ran into a bunch of errors and found that having the full ISO image worked much better.
When the download is complete, run the SSDTSetup.exe (as administrator) program to install the tool. The rest of the instructions should be the same as before.
When prompted, choose to install SQL Server Reporting Services. Click Next.
Read the license terms carefully, and if you don’t find anything objectionable, click the box to agree to the terms and conditions and click Install.
The process will install the SQL Server Data Tools on your environment.
Eventually, the setup will complete. Click Close.
This is all you need to write SSRS Reports if you are able to access a SQL Server database directly and use Transact-SQL as the query language. This would be the case for Dynamics 365 on-premise systems or if you want to write reports based on Azure SQL (If you utilize Dynamics 365 Data Replication). However, even if you run Dynamics 365 on-premise, it would be a good practice to write reports using FetchXML when you will eventually move to Dynamics 365 Online. To write FetchXML based reports, you will need to load the Dynamics 365 Report Authoring Extensions.
Dynamics 365 Report Authoring Extension
The Dynamics 365 Report Authoring Extension is an add-on for SQL Server Data Tools that will allow you to write reports using the FetchXML language. This currently is the only method to write SSRS reports that can access Dynamics 365 Online data directly.
Click on the link below to get to the download page.
IMPORTANT! Make sure you download version 9.0! You will be able to write reports for Dynamics 365 Online 8.2, but it’s better to have up-to-date tools. You can still access previous versions but will require some workarounds to work with Dynamics 365 Online v9 due to Dynamics 365 Online v9 enforcing the TLS 1.2 standard.
You should have now downloaded the file “CRM9.0-Bids-ENU-i386.exe” (name may change slightly if you are downloading a different language)
Extract the package to a folder on your workstation and continue the install. I would recommend you choose to get updates to make sure you have the latest version and patches. Click Next.
There may or may not be updated files to install. Click Next.
Read the license agreement and click on the accept checkbox and click I Accept to continue.
You may (or may not) be prompted to install some additional updates. Choose to Install to update these components.
Choose whether or not you want to use Microsoft Update. Click Next.
Choose the installation location. Click Next.
The installation process will check the system. Click Next to continue.
Confirm the installation directory and click Install to initiate the process.
After a few minutes, the installation process will complete. Click Finish.
The Dynamics 365 Report Authoring extension and SQL Server Data Tools are now installed on your workstation and you can begin to author SSRS reports for Dynamics 365.
If you aren’t familiar with writing Dynamics 365 SSRS reports, check out my post to get started.
Nick Doelman is a Microsoft Business Application MVP and used to write Dynamics CRM Crystal Reports back in version 1.0/1.2. We have come a long way!