Developing SSRS Reports Using Report Data Provider in D365F&O

Developing SSRS Reports Using Report Data Provider in D365F&O

In this article, we will cover the Report Data Provider (RDP) class and the Report Contract class. We will also learn how to implement these classes for reporting needs in Dynamics 365 Finance & Operations and create a report using the RDP class via a step-by-step walkthrough.

---

Report Data Provider (RDP) Framework

A Report Data Provider (RDP) class is an X++ class used to access and process data for a report. An RDP class is an appropriate data source type when the following conditions are met:

• You cannot query directly for the data you want to render on a report.
• The data to be processed and displayed is complex and requires specific business logic.

Report Data Provider Class

This is an X++ class used to access and process data for an SSRS report. The RDP class processes business logic based on specified parameters and/or queries and returns a dataset to the reporting services.

To create an RDP class in D365F&O, you must extend the class with SRSReportDataProviderBase. This indicates that the class will be used by reporting services to process data.

Two important attributes are used in RDP classes:

1. SRSReportQueryAttribute: Specifies which Query will be used in this report. If the RDP class uses a defined Query to process data, define this attribute at the beginning of the class.
2. SRSReportParameterAttribute: Defines the data contract class that will be used by this report to prompt for parameter values. If the RDP class contains any parameters, define this attribute at the beginning of the class.

Note: Both attributes are optional. If the report does not use a query or does not require parameters to filter data, these attributes do not need to be used.

Data Contract Class

This class is used to define one or more parameters that will be used in an SSRS report. We can define any number of parameters using X++ statements of any data type, which can be passed to the RDP class.

A data contract class is an X++ class that contains parm methods with the DataMemberAttribute defined at the beginning of the method.

Table

A table is used as the dataset to store data for the report. The RDP class processes the data and stores it in the table, which is then used by the SSRS report to render data.

A table can be a temporary table (InMemory or TempDB) or a regular table, but it is a best practice to use a temporary table. TempDB is normally used for larger datasets to improve performance, while InMemory is used for smaller datasets.

---

Scenario

We need to display a list of customer transactions. It is a relatively simple report, but we will build it using the RDP framework to demonstrate the process.

Steps:

1. Create a temporary table.
2. Define the report parameters (Contract).
3. Add business logic for the report (RDP).
4. Create a reporting project.
5. Bind a report to the RDP class.

---

Step 1: Create a Project

Open Visual Studio as an administrator.

1. Go to File > New > Project.
2. Select Finance and Operations.
3. Create a new project and rename it CustTransactionRDPDemo.

Step 2: Create a Temporary Table

1. Right-click your project in Solution Explorer, select Add > New Item.
2. Select Table and name it CustTransactionTMP.
3. In the properties window, locate the TableType property and set it to TempDB.

Add Fields:
Decide which fields are required for the report and add them to the table. For this example, add the following fields (types based on CustTrans):

• AccountNum
• AmountMST
• Invoice
• TransDate
• TransType
• Txt
• Voucher
• CustName

Save the table.

Step 3: Create a Query

1. Right-click your project, select Add > New Item.
2. Select Query and name it CustomerTransactionQuery.
3. Open the query designer and drag the CustTrans table into the Data Sources node.
4. Save the query.

Step 4: Define Report Parameters (Data Contract)

We require three parameters: Customer Account, From Date, and To Date.

1. Create a new Class named CustomerTransactionContract.
2. Implement the code below. Note the use of [DataContractAttribute].

[DataContractAttribute]
class CustomerTransactionContract
{
    CustAccount CustomerAccount;
    TransDate FromDate;
    TransDate ToDate;

    [DataMemberAttribute(identifierStr(CustAccount)),
    SysOperationLabelAttribute ("Customer Account"),
    SysOperationHelpTextAttribute("Customer Account"),
    SysOperationDisplayOrderAttribute("1")]
    public CustAccount parmCustomerAccount(CustAccount _CustomerAccount = CustomerAccount)
    {
        CustomerAccount = _CustomerAccount;
        return CustomerAccount;
    }

    [DataMemberAttribute(identifierStr(FromDate)),
    SysOperationLabelAttribute ("From Date"),
    SysOperationHelpTextAttribute("FromDate"),
    SysOperationDisplayOrderAttribute("2")]
    public TransDate parmFromDate(TransDate _FromDate = FromDate)
    {
        FromDate = _FromDate;
        return FromDate;
    }

    [DataMemberAttribute(identifierStr(ToDate)),
    SysOperationLabelAttribute ("To Date"),
    SysOperationHelpTextAttribute("To Date"),
    SysOperationDisplayOrderAttribute("3")]
    public TransDate parmToDate(TransDate _ToDate = ToDate)
    {
        ToDate = _ToDate;
        return ToDate;
    }
}

Step 5: Add Business Logic (RDP Class)

Create the Data Provider class containing the business logic to populate the temporary table.

1. Create a new Class named CustomerTransactionDP.
2. Extend SRSReportDataProviderBase.
3. Add the SRSReportParameterAttribute and SRSReportQueryAttribute to link your Contract and Query.

[SRSReportParameterAttribute(classstr(CustomerTransactionContract)),
SRSReportQueryAttribute(queryStr(CustomerTransactionQuery))]
class CustomerTransactionDP extends SRSReportDataProviderBase
{
    CustTransactionTMP _CustTransactionTMP;

    [SRSReportDataSetAttribute("CustTransactionTMP")]
    public CustTransactionTMP getCustTransactionTMP()
    {
        select * from _CustTransactionTMP;
        return _CustTransactionTMP;
    }

    public void processReport()
    {
        TransDate _FromDate;
        TransDate _Todate;
        AccountNum _CustAccount;
        CustomerTransactionContract dataContract;
        Query query;
        QueryRun queryRun;
        QueryBuildDataSource queryBuildDataSource;
        QueryBuildRange queryBuildRange;
        QueryBuildRange TransDateFilter;
        CustTrans querycusttrans;

        // Get parameters
        query = this.parmQuery();
        dataContract = this.parmDataContract();
        _CustAccount = dataContract.parmCustomerAccount();
        _FromDate = dataContract.parmFromDate();
        _Todate= dataContract.parmToDate();

        // Add ranges dynamically based on contract
        queryBuildDataSource = query.dataSourceTable(tablenum(CustTrans));
        
        if (_CustAccount)
        {
            queryBuildRange = queryBuildDataSource.findRange(fieldnum(CustTrans, AccountNum));
            if (!queryBuildRange)
            {
                queryBuildRange = queryBuildDataSource.addRange(fieldnum(CustTrans, AccountNum));
            }
            queryBuildRange.value(_CustAccount);
        }

        TransDateFilter = SysQuery::findOrCreateRange(query.datasourceTable(tableNum(CustTrans)), fieldNum(CustTrans, TransDate));
        TransDateFilter.value(SysQuery::range(_FromDate, _Todate));

        // Execute query and populate temp table
        queryRun = new QueryRun(query); 
        
        ttsbegin;
        while(queryRun.next())
        {
            _CustTransactionTMP.clear();
            querycusttrans = queryRun.get(tablenum(CustTrans));
            
            _CustTransactionTMP.AccountNum = querycusttrans.AccountNum;
            _CustTransactionTMP.AmountMST = querycusttrans.AmountMST;
            _CustTransactionTMP.Invoice = querycusttrans.Invoice;
            _CustTransactionTMP.TransDate = querycusttrans.TransDate;
            _CustTransactionTMP.TransType = querycusttrans.TransType;
            _CustTransactionTMP.Txt = querycusttrans.Txt;
            _CustTransactionTMP.Voucher = querycusttrans.Voucher;
            _CustTransactionTMP.CustName = CustTable::find(querycusttrans.AccountNum).name();    
            
            _CustTransactionTMP.insert();
        }
        ttscommit;   
    }
}

Build the Solution: Right-click your project in Solution Explorer and select Build. Ensure there are no errors.

Step 6: Create the Report

1. Right-click your project, select Add > New Item.
2. Select Report and name it CustTransRDPReport.

Dataset Configuration:

1. In the Report Designer (solution explorer), right-click Datasets and select Add Dataset.
2. Name it CustTransaction.
3. In the Properties window, change the Data Source Type to Report Data Provider.
4. Click the ellipsis (...) on the Query property field.
5. In the selection dialog, find and select the CustomerTransactionDP class created in Step 5.

Parameters:

Expand the Parameters node of the report. Open the properties of the CustAccount parameter. Set Allow Blank to True and Nullable to True. This ensures that if no customer is selected, the report runs for all customers.

Design:

There are two types of designs:

• Auto Design: Visual Studio creates a design based on the dataset.
• Precision Design: Allows custom placement and complex layouts.

For this demo, we will use Precision Design:

1. Right-click the Designs node and select Add > Precision Design. Name it Report.
2. Double-click the design to open the SQL Report Designer canvas.
3. Open the Toolbox and drag a Table element onto the design surface.
4. Drag and drop fields from your dataset (on the left) into the cells of the Table control.

Save and Build the solution again to deploy the report.

Step 7: Create a Menu Item

To open the report in D365F&O, a menu item is required.

1. Right-click your project, select Add > New Item.
2. Select Output Menu Item.
3. Name it CustomerTransactionReportRDP.
4. Set the following properties in the property pane:
   • ObjectType: SSRSReport
   • Object: CustTransRDPReport (The name of your report)
   • ReportDesign: Report (The name of your precision design)

Running the Report

1. Right-click the Menu Item and select Set as Startup Object.
2. Click Start in Visual Studio to launch the D365F&O client.
3. The Report Dialog will appear automatically once the browser loads.

Set values for "From Date" and "To Date" and run the report. The report will generate based on the logic defined in your RDP class.