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

Introduction

SSRS (SQL Server Reporting Services) reports are a fundamental feature in D365FO, enabling businesses to generate insightful reports from various data sources. If you're looking to create a custom SSRS report from scratch in D365FO, 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, and building the RDP class to retrieve and display data.

---

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

2. 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 TmpSalesOrderReport.

4. After creating the table, you’ll need to define the fields that correspond to the data you wish to display in the report (e.g., SalesOrderId, CustomerAccount, SalesAmount).

5. Set the Table Type property to TempDB to store the data temporarily.

6. Save and synchronize the table.

---

3. Develop the Data Contract Class

The Data Contract (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 Data Contract Class (with new buffer names):

x++
[DataContractAttribute, SysOperationGroupAttribute('Sales Order Report')]
public class SalesOrderReportDC
{
    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;
    }
}

4. 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 for the report.

Sample Code for UIBuilder Class:

x++
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));
    }
}

---

5. 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:

x++
[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();
    }
}

---

6. 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. Stay tuned for part 2 of this blog series, where we will cover the deployment and testing of the SSRS report.