Get started writing reports

 

Updated: November 28, 2016

Applies To: Dynamics 365 (online), Dynamics 365 (on-premises), Dynamics CRM 2016, Dynamics CRM Online

Microsoft Dynamics 365 uses Microsoft SQL Server Reporting Services report definition language (RDL) reports to query Dynamics 365 data and return refined results back to the report user. For more information about RDL, see TechNet: Report Definition Language (SSRS).

To create or modify existing RDL reports that can be used with Microsoft Dynamics 365, use either T-SQL or FetchXML, which is then converted to RDL by using report authoring tools. The following table lists the differences between SQL-based and Fetch-based reports in Microsoft Dynamics 365.

Area

SQL-based report

Fetch-based report

Supported Microsoft Dynamics 365 Versions

Dynamics 365 (on-premises)

Microsoft Dynamics 365 (online) and Dynamics 365 (on-premises)

Report Query Language

Uses Transact-SQL (T-SQL)—a set of programming extensions that provide comprehensive transaction control by using Structured Query Language (SQL). More information: TechNet: Transact-SQL Reference (Database Engine)

Uses FetchXML—an extensible markup language (XML) designed specifically for Microsoft Dynamics 365 queries. More information: MSDN: FetchXML schema

Requires Report Authoring Extension?

No

Yes

.RDL file Data Provider

The <DataProvider> element value in the .rdl file is set to SQL. For example:

<DataProvider>SQL</DataProvider>

The <DataProvider> element value in the .rdl file is set to MSCRMFETCH. For example:

<DataProvider>MSCRMFETCH</DataProvider>

.RDL file Report Query

The query specified for retrieving data is in the <CommandText> sub-element under the <Query> element in the report definition (.rdl file) and is a SQL query. For example, the query for retrieving all account names for a SQL-based report will be:

<CommandText>SELECT name FROM FilteredAccount;</CommandText>

The query specified for retrieving data is in the <CommandText> sub-element under the <Query> element in the report definition (.rdl file) and is a FetchXML query. For example, the query for retrieving all account names for a Fetch-based report will be:

<CommandText>&lt;fetch version="1.0" output-format="xml-platform" mapping="logical"&gt;     &lt;entity name="account"&gt;        &lt;attribute name="name" /&gt;    &lt;/entity&gt;&lt;/fetch&gt;</CommandText>

If you want, you can use a third-party tool, SQL2FetchXML, to convert your SQL scripts to FetchXML, and then use the FetchXML query in your Fetch-based reports. More information: SQL2FetchXML Help

In This Section

This section covers what you need to create a new Microsoft Dynamics 365 report.

Report writing environment using SQL Server Data Tools

Create a new report using SQL Server Data Tools

© 2016 Microsoft. All rights reserved. Copyright

Community Additions

ADD
Show: