Dynamics CRM - Developing SSRS Rreports Using Fetch

Brief Description

This article explains how to develop custom reports in Dynamics CRM using FetchXML, including:

Preparing the environment
Practical example

Preparing the Environment

P.S. Please follow the steps for the software listed below in order;

otherwise, unexpected issues may arise.

Visual studio 2019
SQL Server Data Tools
Microsoft Reporting Services Projects
Restart the computer
Install Dynamics 365 Report Authoring Extension

Here is the download link:

Date: 2024-01-22

I attempted to use Visual Studio 2022 for report development, but I encountered a component exception that I couldn’t resolve.Therefore, I still recommend continuing to use Visual Studio 2019

Development Example

Create a Project

First, open Visual Studio 2019 and create a report project:

Create a new project
Search for “Report” and select “Report Server Project,” then click “Next”

Fill in the “Project Name” and click “Create”

At this point, we have completed the creation of the report project, and the structure is as follows:

Create a Report

Select the “Reports” folder and right-click on it.
Choose “Add” , then select “New Item”.
Select “Report” , enter the report name, and click “Add”.

At this point, the report has been added, and the interface is as follows:

Add Data Source

Select the “Data Sources” folder and right-click on it, then choose “Add Data Source”.

If the “Report Data” tab has been accidentally closed, you can reopen it using the shortcut Ctrl + Alt + D.

Fill in the “Name for Data Source”.

You can name the data source according to your personal preference; I usually name it “DynamicsCRMDataSource”.

This name is not critical; you can use the default name as well, and it can be changed later.

For Type, select Microsoft Dynamics 365 Fetch
Enter the “Connection String” and click “OK.”

To learn how to obtain the connection string,

please refer to the “Getting the Connection String” section of this article.

Prepare Fetch

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


<fetch aggregate="true">
  <entity name="audit">
    <attribute name="createdon" alias="LastLoginDate" aggregate="max" />
    <filter>
      <condition attribute="operation" operator="eq" value="4" />
    </filter>
    <link-entity name="systemuser" from="systemuserid" to="objectid" link-type="inner" alias="SystemUser">
      <attribute name="fullname" alias="FullName" groupby="true" />
      <attribute name="domainname" alias="DomainName" groupby="true" />
      <attribute name="isdisabled" alias="IsDisabled" groupby="true" />
      <attribute name="accessmode" alias="AccessMode" groupby="true" />
      <attribute name="userlicensetype" alias="UserLicenseType" groupby="true" />
    </link-entity>
  </entity>
</fetch>

Add Data Set

Fill in the “Data Set Name”, select “Use a dataset embedded in my report”, and choose the DynamicsCRMDataSource data source we just added.
For Query Type, select “Text”, fill in the Fetch, and finally click “OK”.

Note: If a login prompt appears after clicking “OK,” log in with your development account.

Report Design

Report Title

Select the “Text Box” from the toolbox, drag it to the report design area, and then fill in the title.

Select the “Table” from the toolbox, drag it to the report design area, and enter the column headers.

P.S. The styles can be adjusted according to your needs.

Data Binding

Using the “User Name” column as an example: right-click the empty space below the user name and select “Expression”.

Select “Fields,” then double-click “FullName.” The expression will automatically update after double-clicking.

Of course, you can also manually enter the expression. Finally, click “OK”.

Then bind data for the other columns in sequence.

Test (Preview) the report

Effect:

Publish report

Enter Power Apps and create a new solution (you can also use an existing one, as long as it is unmanaged).

Click “New”, then select “Report”.

For the report type, choose “Existing File”, and then select the completed report: UserLastLoginTime.rdl.

Fill in the “Report Name”, and finally click “Save”.

At this point, the example is complete. You can open and preview it from the Reports section in the menu bar.

In Dynamics CRM, reports that typically require custom development can generally be categorized into two types:

“Global Reports”: The report created in this example falls into this category. These reports are usually executed from the report entity or the button bar of a specific business entity’s view.

“Single Record Reports”: These reports are typically placed on the business record forms, with parameters mainly taking the GUID of a specific business record.

Get the Connection String

The format of the connection string is:

{Environment URL}/{Environment Unique Name}

For example

Environment URL：https://sample.crm.dynamics.cn
Environment Unique Name：123456789
Connection String：https://sample.crm.dynamics.cn/123456789

How to obtain the Environment Unique Name?

UCI Interface

Open Power Apps https://make.powerapps.com/

Click the settings button in the upper right corner and select “Developer Resources”

Classic UI

Settings –> Customizations

Select “Developer Resources”