There is a new release of the Report Authoring Tools that will work on SSDT 2019.
Check out the updated blog post here:
Set Up Your Workstation to Create SSRS Reports for Dataverse, Model-Driven Power Apps and Dynamics 365
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.
The following post is kept here for historical purposes.
- 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.
Click here for the original post.
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.
Click here to get to the Visual Studio Data Tools download page.
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.
Dynamics 365 Report Authoring Extension
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.
How to Create Your First Dynamics 365 SSRS Report
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!
24 thoughts on “Set Up Your Workstation to Create SQL Server Reporting Services Reports for Dynamics 365 Revisited”
Reblogged this on Nishant Rana's Weblog and commented:
Step by step guide for Report Authoring in Dynamics 365 V9
Thanks Nick for this article, you have explained it so nicely. It always confusing with different versions of different products.
Thanks Nishant! I am glad you found it helpful. Cheers, Nick
Don’t know if you’ve run into it but coincident with the latest W10 Update 2019-05 I can no longer edit reports. I now get the message “Unknown Report Version: 9.0”. It’s been working for months before this update.
Don’t know if you’re run into this or not but coincident with the 2019-05 Windows 10 update I can no longer edit reports in VS 2015. I receive the error “Unknown Report Version: 9.0” and cannot save the report in VS. This has been working for months prior to this update.
Thanks, I haven’t run into it but will check it out. I would suggest opening a ticket with MS if its a blocker.
Hi – I’ve already got Visual Studio 2017 on my pc – do I still need to install the 2015 version? and do I need to uninstall the 2017 version?
I am not sure if SQL Server Data Tools 2017 works with the Dynamics 365 Report Authoring Extensions… I don’t think it does but haven’t tested it. You can have multiple versions on your PC (I do). Hope that helps!
For anybody else that runs into this, even if you already have VS2017 with SQL Data Tools installed, you still need to install the stand-alone SQL Data Tools 2015. The Dynamics 365 Report Authorizing Extensions don’t work with VS2017. Even though I had the Extensions installed, the correct type of data source would not appear in my VS2017. After installing the Data Tools 2015 and then launching that (as written in this blog), did everything work correctly.
I hope this helps.
Reblogged this on MG and commented:
Setup for your environment to work with SSRS Reports Using FETCH XML
Thanks a ton for the recent update regarding the ISO…I was completely stuck at that point.
Yeah, that was driving me crazy as well!
Hi, I have VS2017 installed and planning to install stand-alone SQL Data Tools 2015. But suggested Dynamics 365 Report Authoring extension version is strictly for the 32-bit version. I’m wondering if there is a 64-bit version. And what would be a workaround for that case?
Hi Yuliya, You are correct you need SQL Data Tools 2015. You can install 32-bit software on Windows 64-bit. That is how I have mine setup. I hope that helps!
Thanks for this post. I am working on a Dynamics 365 on-premises version 8.2 project that has been made to have to enforce TLS 1.2 due to compliance rules. The virtual machines used for development in a secure environment have the old version of the report authoring extension installed and the preview functionality for SSRS reports will now not work with the change in TLS 1.2 setting. Is this version of the tool backwards compatible with version 8.2 on-premises? I couldn’t find any documentation to confirm compatibility.
Hi Adam, The Dynamics 365 Report Authoring Extension v9 *should* be OK to create reports for 8.2, but the download link https://www.microsoft.com/en-us/download/details.aspx?id=56973 specifies Dynamics 365 v9 or later. In past, I used one version of the report authoring extensions for different versions of Dynamics 365 with no issues. That being said, if you do use 8.2 version, please check my friend Andrew Butenko’s blog on using fiddler to get around TLS 1.2 issue with Report Authoring Extensions, its a bit of pain but it works. https://butenko.pro/2018/02/14/microsoft-dynamics-365-v9-0-how-to-fix-connectivity-issues-between-ssdt-report-authoring-extension-and-dynamics-365/
Thanks a lot for the detailed post. When I am trying to extract the “Dynamics 365 Report Authoring Extension” after downloading it, I am seeing this error repeatedly “Setup cannot continue because there is a pending restart required. Restart the computer and then try running Setup again” even after restarting the system.
Can you let me know how to solve this issue?
I would suggest making sure all the Windows updates and patches have been applied, then try re-installing again. Failing that, you might need to raise a support ticket with Microsoft. Hopefully applying the patches does the trick.