By Nikhil Rajendran | Reading time 6 minutes
Part 1 – Establishing connection
I recently came across a requirement where I had to create an SSRS report for Dynamics 365 for a client. This doesn’t sound that easy. Even being in the Dynamics CRM field for quite some time, I had faced several issues while creating the report, so I decided to document the process of creating the report that can be helpful for others.
I used Visual Studio 2015 for creating the report. However, you can use the 2017/19 versions as well. Make sure that the SQL Server Data Tools is installed. We also need to install the Dynamics 365 Report Authoring Extension. You can download it from here:
The environment for which I am creating the report is Dynamics 365 9.0 online instance.
Create the project in Visual Studio :
- Open your Visual Studio environment.
- If the report authoring extension was installed correctly, you should see a screen similar to this:
- Under Templates, you will see Business Intelligence under which Reporting Services. Select the Report Server Project. Give a Name for the project and click Ok.
Connecting to Dynamics 365:
- After creating the project, the first thing you need to do is add a report to your project. To do that, right-click on the reports folder, select Add/New Item.
- Select “Report”, give a name for the report, and click Add.
- Make sure the report is selected (there is a black fill around the name). Click on View/Report Data.
- Now you will see the Report Data bar. The first thing you need to do is add the data source for the report. To do that right-click on Data Sources and click Add Data Source.
- You will now see the data source properties window. Give a name for the Data Source.
- Ensure that the “Embedded connection” radio button is selected and the type is “Microsoft Dynamics 365 Fetch.”
- Now we need to enter the Connection String. The connection string will look something like the format of https://your_organization_name_which_is_in_URL.crm.dynamics.com;uniquename
- After entering the data, the screen will look something like this:
NOTE: There are 2 parts in the connection string separated by the semicolon( ; ) The first part is the organization URL. This is the URL you enter in the browser to connect to the Dynamics 365 environment. The second part is the Organization name. To get the organization name, go to Settings/Advanced Settings/Customizations/Developer Resources. The unique name is the one highlighted below. Copy that and paste it in the connection string.
- Next, you need to enter the credentials. Choose the “Use this user name and password” and enter the credentials you use to connect to your Dynamics 365 environment and Click Ok.
For any questions related to this blog or Dynamics 365, connect with us.
Nikhil has been with AhaApps since 2017 as a Microsoft Dynamics CRM Developer. He is a driven CRM expert who is ready to fight off the challenges in the Dynamics world with his technical know-how and prowess. He is a sports enthusiast and loves to play tennis when he gets time. He is also a voracious reader and enjoys reading philosophical books.