Create an SSRS Report for Dynamics 365 – Part 2

Part 2 – Building The Report

 

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.

SSRS Report for Dynamics 365 - Fetch XML query

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

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

 

downloaded Fetch XML to the dataset window - 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:

 

Fetch XML - SSRS report Dynamics CRM

Now you can go ahead and paste this data source.

SSRS Report data source - 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 data set - AhaApps

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 reports adding components - AhaApps

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.

Creating SSRS report for dynamics 365 - AhaApps

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:

Dynamics 365 support - AhaApps

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.

Creating SSRS report for dynamics CRM - AhaApps

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:

Creating SSRS report for dynamics CRM - AhaApps

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

Create SSRS report for dynamics CRM - AhaApps

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

Dynamics crm Power Apps - AhaApps

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

Run Report - SSRS reports for dynamics 365

 

The report looks like this:

SSRS reports - AhaApps

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

Download our 15 Questions to Identify the Gaps in Your CRM

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Show Buttons
Hide Buttons