Create an SSRS Report for Dynamics 365 – Part 2

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

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:

Now you can go ahead and paste this data source.

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.

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:

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.

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:

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.

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:

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

• 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)

• 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.

The report looks like this:

For any questions related to this blog or Dynamics 365, connect with us.