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.

Developing SSRS Reports Using a Query in D365F&O

Developing SSRS Reports Using a Query in D365F&O

In this tutorial, we will learn how to develop a report based on a predefined Query in the Application Object Tree (AOT) within Visual Studio. This method is often faster than using a Report Data Provider (RDP) class when complex business logic is not required.

Scenario: We will create a report that prints a simple list of customer transactions directly from the CustTrans table without writing X++ business logic.

---

Step 1: Create the Project and Query

Unlike previous versions of Dynamics, all development for D365F&O occurs inside Visual Studio.

1. Open Visual Studio as an administrator.
2. Create a new Finance and Operations project.
3. In the Solution Explorer, right-click the project node.
4. Select Add > New Item.
5. Select Query from the list and name it CustomerTransactionQuery. Click Add.

Configuring the Data Source:

• Open the Query designer.
• Find the CustTrans table in the Application Explorer (AOT).
• Drag CustTrans into the Data Sources node of your new query.
• Expand the Fields node of the CustTrans data source you just added.
• In the Properties window, locate the Dynamic property and set it to Yes.
  (Note: Setting this to Yes automatically adds all fields from the table to the query, ensuring they are available for the report).
• Save the query (Ctrl+S).

Step 2: Create the Report

1. Right-click your project in Solution Explorer.
2. Select Add > New Item.
3. Select Report and name it CustTransactionReport.
4. Click Add.

Step 3: Configure the Dataset

Datasets retrieve data from the query defined in Step 1. The dataset acts as the bridge between the D365F&O database and the SSRS report layout.

1. Inside the Report Designer (which opens when you double-click your report), look for the Datasets node.
2. Right-click Datasets and select New Dataset.
3. Name: Enter CustTransaction.
4. Data Source Type: In the Properties window, verify this is set to Query.
5. Query: Click the ellipsis (...) button in the Query property field.
6. In the dialog box that appears, locate and select the CustomerTransactionQuery we created in Step 1.
7. Click Next. Select the fields you wish to display (or check "All").
8. Click OK. You should now see the fields listed under your new Dataset.

Step 4: Design the Report Layout

We will use a Precision Design, which allows for custom field placement.

1. In the Report Designer, right-click the Designs node.
2. Select Add > Precision Design.
3. Rename the new design to Report.
4. Double-click Report to open the SQL Report Designer canvas.

Adding Data to the Layout:

• Open the Toolbox pane (usually on the left) and drag a Table control onto the white design surface.
• Open the Report Data pane (usually on the left). You will see your CustTransaction dataset there.
• Drag fields (like AccountNum, TransDate, AmountMST) from the Dataset directly into the cells of the Table on the design surface.
• Adjust the column widths and header text as desired.

Once designed, Build the solution (Ctrl+Shift+B) to ensure the report is deployed to your local development environment.

Step 5: Create the Menu Item

To run the report from the D365F&O browser client, we need an Output Menu Item.

1. Right-click your project, select Add > New Item.
2. Select Output Menu Item.
3. Name it CustTransactionReportMI.
4. In the Properties window for the menu item, set the following:
   • Object Type: SSRSReport
   • Object: CustTransactionReport (The name of your report)
   • Report Design: Report (The name of your precision design)

Step 6: Run the Report

1. Right-click the CustTransactionReportMI Menu Item in Solution Explorer.
2. Select Set as Startup Object.
3. Click Start in the top toolbar (or press F5) to launch the D365F&O client.

The browser will open D365F&O. It may take a moment to load. Once loaded, the Report Dialog will appear automatically. Click OK to generate and view the report preview on screen.