How to Develop a Custom SSRS Report Using RDP in Dynamics 365 Finance and Operations (D365F&O)

SSRS (SQL Server Reporting Services) reports are a fundamental feature in D365F&O, enabling businesses to generate insightful reports from various data sources. If you’re looking to create a custom SSRS report from scratch in D365F&O, this tutorial will guide you through the process using RDP (Report Data Provider), DataContract, Controller, and UIBuilder classes.

In this part of the series, we’ll cover the essential steps for building a custom SSRS report, including:

• Defining a temporary table
• Creating a DataContract class
• Setting up the UI for report parameters
• Building the RDP class to retrieve and display data

---

Define Report Requirements

Our example report will focus on the “Sales Order Data” report, pulling data from SalesTable and its related records. The report will display the list of sales orders within a given date range, filtered by specific parameters such as customer account and sales status.

---

Create Temporary Table for Report Data

The first step in report creation involves defining a temporary table that will hold the data for the report. Temporary tables are used to store data during the report generation process and are cleared when the report session ends.

Steps to Create the Temporary Table:

1. Open Visual Studio and connect to your development environment (Model).
2. In Solution Explorer, right-click on your report project and select Add > New Item.
3. Choose Dynamics 365 Items > Data Model > Table, then name it:
   E3_TutorialSSRSReportTmp

1. After creating the table, define the fields corresponding to the data you wish to display in the report (e.g., SalesOrderId, CustomerAccount, SalesAmount).
2. Set the Table Type property to TempDB to store the data temporarily.
3. Save and synchronize the table.

---

Develop the DataContract Class

The DataContract (DC) class defines the input parameters that users will provide when running the report, such as date range, customer account, and sales status. This class also handles validation logic.

Sample Code for DataContract Class:

[DataContractAttribute, SysOperationGroupAttribute('Sales Order Report')]
public class SalesOrderReportDataContract
{
    TransDate          startDate, endDate;
    CustAccount        customerAccount;

    [DataMemberAttribute('Start Date')]
    public TransDate parmStartDate(TransDate _startDate = startDate)
    {
        startDate = _startDate;
        return startDate;
    }

    [DataMemberAttribute('End Date')]
    public TransDate parmEndDate(TransDate _endDate = endDate)
    {
        endDate = _endDate;
        return endDate;
    }

    [DataMemberAttribute('Customer Account')]
    public CustAccount parmCustomerAccount(CustAccount _customerAccount = customerAccount)
    {
        customerAccount = _customerAccount;
        return customerAccount;
    }

    public boolean validate()
    {
        boolean isValid = true;

        if (startDate && endDate)
        {
            if (startDate > endDate)
            {
                isValid = checkFailed("End date must be later than start date.");
            }
        }
        return isValid;
    }
}

---

Create the UIBuilder Class

The UIBuilder class customizes the user interface for report parameters, such as the date range and customer account. It enhances the user experience by adding dynamic fields and controls.

Sample Code for UIBuilder Class:

class SalesOrderReportUIBuilder extends SrsReportDataContractUIBuilder
{
    SalesOrderReportDC contract;
    DialogField fromDateField, toDateField, customerAccountField;

    public void build()
    {
        contract = this.dataContractObject() as SalesOrderReportDC;

        fromDateField = this.addDialogField(methodStr(SalesOrderReportDC, parmStartDate), contract);
        toDateField = this.addDialogField(methodStr(SalesOrderReportDC, parmEndDate), contract);
        customerAccountField = this.addDialogField(methodStr(SalesOrderReportDC, parmCustomerAccount), contract);
    }

    public void postBuild()
    {
        Dialog localDialog = this.dialog();
        super();

        fromDateField = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(SalesOrderReportDC, parmStartDate));
        toDateField = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(SalesOrderReportDC, parmEndDate));
        customerAccountField = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(SalesOrderReportDC, parmCustomerAccount));
    }
}

Develop the Report Data Provider (RDP) Class

The RDP class queries data based on the parameters from the DataContract class, processes it, and inserts it into the temporary table for report generation.

Sample Code for RDP Class:

[SysEntryPointAttribute(false)]
class SalesOrderReportDP extends SRSReportDataProviderBase
{
    SalesOrderReportDC reportDC;
    TransDate startDate, endDate;
    CustAccount customerAccount;
    TmpSalesOrderReport tmpSalesOrder;

    [SRSReportDataSetAttribute(tablestr(TmpSalesOrderReport))]
    public TmpSalesOrderReport getTempTable()
    {
        select * from tmpSalesOrder;
        return tmpSalesOrder;
    }

    public void processReport()
    {
        Query query = new Query();
        QueryRun queryRun;
        QueryBuildDataSource qbds;
        SalesTable salesOrder;

        reportDC = this.parmDataContract() as SalesOrderReportDC;
        startDate = reportDC.parmStartDate();
        endDate = reportDC.parmEndDate();
        customerAccount = reportDC.parmCustomerAccount();

        qbds = query.addDataSource(tableNum(SalesTable));

        if (startDate && endDate)
        {
            qbds.addRange(fieldNum(SalesTable, OrderDate)).value(queryRange(startDate, endDate));
        }

        if (customerAccount)
        {
            qbds.addRange(fieldNum(SalesTable, AccountNum)).value(queryValue(customerAccount));
        }

        queryRun = new QueryRun(query);

        while (queryRun.next())
        {
            salesOrder = queryRun.get(tableNum(SalesTable));
            tmpSalesOrder.OrderDate = salesOrder.OrderDate;
            tmpSalesOrder.SalesOrderId = salesOrder.SalesId;
            tmpSalesOrder.CustomerAccount = salesOrder.AccountNum;
            tmpSalesOrder.SalesAmount = salesOrder.SalesAmount;
            tmpSalesOrder.insert();
        }
    }

    public static void main(Args args)
    {
        SalesOrderReportDP reportDP = new SalesOrderReportDP();
        reportDP.processReport();
    }
}

Add a Report Design

Now that we’ve specified what data to show, we need to define where to show the data. To do that, add a report design:

1. Right-click on the Designs node
2. Click New > Precision Design
3. Double-click on the design node to open the layout

Conclusion

In this part of the series, we have created a custom SSRS report in D365FO using RDP. The report allows users to filter sales orders based on date and customer account. You can extend this approach to include more complex business logic or integrate additional data sources as needed.

---

Frequently Asked Questions (FAQs)

1. What is the purpose of using RDP for SSRS reports in D365FO?

RDP is used when you need to process business logic in X++ before rendering data in SSRS. It’s ideal for complex reports that cannot be generated using query-based reports alone.

2. Why do we use a temporary table in an RDP report?

It stores processed data retrieved by the RDP class. This data is isolated per report execution and does not persist in the database.

3. Can I reuse the same DataContract for multiple reports?

Yes, if parameters are applicable across reports. Otherwise, use separate contracts for clarity.

4. What is the difference between TempDB and InMemory table types?

• TempDB: SQL-based, supports joins and sorting
• InMemory: RAM-based, limited features and scalability

5. Is it mandatory to create a UIBuilder class?

No, only if you need to customize the report dialog or display dynamic fields.

6. How do I deploy the SSRS report to D365FO?

1. Build the project
2. Right-click the report design > Deploy
3. Go to D365FO > System Administration > Report Catalog

7. How do I make the report accessible from the D365FO UI?

• Create an Output Menu Item pointing to the controller class
• Add it to a relevant menu/workspace

8. What if the report returns no data?

Check filter criteria, RDP logic, and user permissions.

9. How do I debug the RDP class?

Use breakpoints in Visual Studio. Ensure “Enable Debugging” is turned on.

10. Can I extend the report to include more data sources like SalesLine?

Yes, by updating the RDP logic, temporary table, and report design.

11. How do I handle localization in report labels?

Use @LabelId for all static text and captions.

12. What permissions are needed to view custom reports?

Users need access to:

• Data tables
• The report’s menu item
• Any security role-linked duties or privileges

---

Have questions or need help with your SSRS Reporting with RDP in D365FO? I’m here to assist!

Daya Kushawah
Microsoft AX Technical Consultant

Let’s connect and solve your implementation challenges together.