04/15/2021

By Nikhil Rajendran | Reading time 6 mins

Create an SSRS Report for Dynamics 365 – Part 2

In the first part, we spoke about how to add the data source. Now, after adding that, we now have to add the dataset. The dataset is what we are going to be creating the report on. Let us take an example of creating a report on an account and its corresponding contacts. Account has a 1:N relationship with Contacts. One Account has many contacts.

To do that, in the report data right-click on the folder “Datasets” and click “Add Dataset..”

For the dataset, we need to have a Fetch XML query. To help with this, you can do an advanced find of what you need. For example, search for the accounts and corresponding contacts and click on Download Fetch XML.

 

Create an SSRS Report for Dynamics 365 – Part 2

 

Copy-paste the downloaded Fetch XML to the dataset window.

CRM integrated field service management - AhaApps

15 Questions to Identify the Gaps in Your CRM

Download Now
CRM Checklist - AhaApps

The Fetch XML before any modification will look similar to this:

SSRS report code - AhaApps

There are few things to modify before having the final version of the Fetch XML.

  • The first thing to do is, add a property called “enableprefiltering=1”. Adding this property will ensure that the report runs only on that particular record from which you are clicking on “Run Report” and its corresponding contacts. Not adding this property will run it on all the accounts and all the corresponding contacts. This will lead to performance issues and the report will not be accurate. The requirement in these kinds of scenarios would be in general to run it on that particular record and its corresponding child records.
  • Next, we need to correct the link entity part of the query. The way the query is when you download it from advanced find, there will not be any data from the link entity. We need to manually add the attributes which we need from the link entity.
  • We also need to remove the “/” next to the alias in the link-entity declaration. The corresponding closing link-entity tag should be below all the attributes of the child entity(contact).
  • I would also recommend changing the alias name to something more “understandable” and what it truly represents. It will be easier to use. For example, in this example, the alias for contact would be better if it’s “con” instead of “ac”. You can also keep the full name (“contact”) also if you prefer that. Just ensure that the alias name is not too long.

After making these modifications, the Fetch XML will look like this:

SSRS report code - AhaApps

Now you can go ahead and paste this data source.

 

Create an SSRS Report for Dynamics 365 – Part 2 - AhaApps

 

 

 

There are few things to consider in this window:

  • Ensure that you give an appropriate name for this dataset.
  • Choose the “Use a dataset embedded in my report” radio button and choose the data source you created.
  • Paste the modified Fetch XML.
  • Click on refresh fields. This will ensure that the fields are synced up.
  • Click Ok.

 

Now, all the fields you added appear on the left under the data set.

SSRS Report Dynamics 365– Part 2

Now we can start adding our components to the report. First, ensure that the toolbox is visible. If it’s not, press Ctrl Shift X at the same time. The toolbox looks like this:

SSRS Report Dynamics CRM– Part 2

To add the components to the report, just drag and drop the component. For example, let’s say you want to add a text box, just click on Text box and drag and drop it at the exact location you want on the report.

SSRS Report Microsoft Dynamics CRM– Part 2

Now you can enter some text inside the textbox. Let’s say you want to add the account name inside the textbox. It should look something like “Name:<insert account name here>”.The account name is going to be dynamic varying from report to report.

 

So just enter the text “Name:”.

 

After doing that, drag and drop “name” from under the data set right next to the “:” after “Name” in the text box. Now the text box will look like the following:

SSRS Report Microsoft Dynamics 365– Part 2

You can add more components to the textbox. Increase the length, the width of the textbox according to your components.

 

I have also added the Phone number of the account.

 

Next, let’s say we want to add data from the related contacts. In general, the data from the link entity is put on a table. So drag and drop the table component.

 

In general, the first row of the table is the “header” and the data is below that.

SSRS Report for Microsoft Dynamics 365– Part 2

Similar to how we added the name for the account, we need to do it for the contact fields. Just drag and drop the necessary fields. (Note: drag and drop the fields with the prefix of the alias. In my case it will be con_fullname, con_emailaddress1..)

 

So after dragging and dropping, my table will look similar to this:

SSRS Report for Microsoft Dynamics CRM – Part 2

Now just change the header names to what it truly represents. After changing my headers, my table looks like this:

SSRS Report for Dynamics CRM – Part 2
  • Now the report is ready. Save the solution.
  • Now go to the CRM environment, Go to the Default solution, or the solution you created for this report work.
  • Go to the Reports section and click on New.
  • Choose the report type as “Existing file”
  • Choose the RDL file(It will be inside the solution folder with a format of reportname. rdl)
SSRS Report for Dynamics 365 – Part 2
  • You can choose a corresponding display name by changing the name.
  • Choose the parent record type in “Related record types”. In my example, it is “Accounts”
  • For the “Display In” – choose what is best applicable. In my case, it will be best when users open a particular account record and click on “Run Report”. For this choose “Forms for related record types”. If you want it to appear on the home page of the entity(that is in the view of an entity) choose “ Lists for related record types”. If it needs to appear under the report section of the environment, choose “Reports”.
  • Click on Save.

 

Now the report is ready. To run this report in my example, I have to open a particular account record, click on the “Run report” button, and click on the report name.

Create an SSRS Report for Dynamics 365 – Part 2
The report looks like this:
Create an SSRS Report for Dynamics 365 – Part 2

Author’s bio:

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.

Attaching an SSRS Report In Dynamics 365

When the client wants changeable documents to be attached in the D365 ecosystem, what do you do?

Read Me