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.