Report Content: To Add a Subreport

In the following activity, you will learn how to create a subreport that is placed within a report.

Story

Suppose that you are a technical specialist in your company who is working on simple customizations. A project manager has requested a report that displays project profitability by project manager. The project manager has also asked you to display the list of change orders for each project. You have looked through the reports in MYOB Acumatica and decided to use a copy of the Project Profitability (PM624000) report and make modifications to it.

Process Overview

In the Report Designer, you will create the COProfit report, a subreport that will display a list of change orders related to projects in the main report. As the main report, you will use the PM6240C1.RPX report, which is a copy of the Project Profitability (PM624000) report. You will insert the COProfit report (subreport) into the main report.

System Preparation

Before you perform the steps of this activity, make sure that the following tasks have been performed:

1. You have installed the MYOB Acumatica Report Designer, as described in Report Designer: To Install the MYOB Acumatica Report Designer.
2. You have installed an MYOB Acumatica instance with the U100 dataset, or a system administrator has performed this task for you.
3. You have signed in to MYOB Acumatica as the system administrator by using the gibbs username and the 123 password.
   Tip:

   The gibbs user is assigned the Administrator role and the Report Designer role. Thus, this user has sufficient access rights to manage system configuration and to preview, save, and publish reports.

Also, to prepare for use the file that you will need for this activity, do the following:

1. Download the PM6240C1.rpx file.
2. Open the downloaded file in the Report Designer.
3. On the Report Designer menu bar, select File > Save To Server, which opens the Save Report on Server dialog box.
4. In the dialog box, specify the connection string and sign-in credentials of your MYOB Acumatica instance, type PM6240C1 as the report name, and click OK.

   The report is saved on the server.

Step 1: Creating a Subreport

To create a subreport that lists change orders, do the following:

1. On the Report Designer menu bar, click File > New.

   An empty report with the default sections opens.

2. On the Report Designer menu bar, click File > Build Schema.

   The Schema Builder opens with the Tables tab selected.

3. In the Enter Web Service URL to Load WSDL Document box of the tab, enter the URL of your MYOB Acumatica website, which can be your local website or an external URL of MYOB Acumatica.
   Tip:

   The history of successful connections is automatically saved in the drop-down list of the URL box. For example, if you have already opened a report from the server, you can select the URL from the drop-down list.
4. Type gibbs as the username and 123 as the password.
5. Click the Load Schema button.
6. In the Search box, type ChangeOrder, and in the Select Report Tables list, select PMChangeOrder and click >.

   The PMChangeOrder data access class is placed in the right pane of the Schema Builder. This data access class contains information about change orders.

7. To link the subreport with the main report, do the following:
   1. On the Parameters tab of the Schema Builder, click Add to add a parameter.
   2. In the Name box, type Project.
   3. On the Filters tab of the Schema Builder, add a row with the following settings:
      • Data Field: PMChangeOrder.ProjectID
      • Condition: Equal
      • Value1: @Project
8. In the Schema Builder, click Apply to apply the changes and OK to close the window.
9. In the Tools pane, drag the TextBox element to the detailSection1 section, on the left.
10. As the Appearance > Value property of the added text box, specify =[PMChangeOrder.RefNbr].
11. Adjust the height of the detailSection1 section to be the same as (or a bit taller than) the height of the added text box.
12. Right-click any empty space in the report layout, and click the Add New Group command to add a new group.

    The groupHeaderSection1 (Header of group1) and groupFooterSection1 (Footer of group1) sections are added to the report layout.

13. In the groupHeaderSection1 (Header of group1) section, add a text box, and for its Appearance > Value property, type Change Order Reference Number. This text box will display the header of the list of change orders for a project.
14. Adjust the height of the groupHeaderSection1 (Header of group1) section to be the same as (or a bit taller than) the height of the added text box.
15. Optional: Right-click the header of the pageHeaderSection1 section, and click Delete to delete the section. Delete the pageFooterSection1 section in the same way.
16. Select the groupFooterSection1 (Footer of group1) section, and for its Behavior > Visible property, select False to make the section invisible.
17. On the Report Designer menu bar, click File > Save To Server to save the report.
18. In the Save Report on Server dialog box, fill in the following boxes:
    • Enter Web Service URL to Load WSDL Document: The URL of your MYOB Acumatica website
    • Login: gibbs
    • Password: 123
    • Enter Report Name to Save: COProfit
19. Click OK to save the report and close the window.

    COProfit.rpx is saved on the server.

The subreport that you have created is shown in the following screenshot.

Step 2: Adding the Subreport to the Report

The groupHeaderSection1 (Header of ProjectGroup) section of the PM6240C1 report contains the detailed information about a project. The section will also display information about change orders for each report after you add the created subreport to this section.

To insert the subreport in the PM6240C1 report, do the following:

1. In the Report Designer, open the PM6240C1 report (which you have saved to the server).
2. Select the groupHeaderSection1 (Header of ProjectGroup) section and specify the 1.4cm value for the Appearance > Height property to increase the height of the section.
3. In the Tools pane, drag the SubReport element to the groupHeaderSection1 (Header of ProjectGroup) section, below the text boxes.
4. While the SubReport element is selected, in the Behavior > ReportName property of the Properties tab, type the name of the subreport: COProfit.rpx.
5. To the right of the Behavior > Parameters property, click the button.
6. In the ExternalParameter Collection Editor, which opens, in the Members pane, click Add.
7. In the right pane of the ExternalParameter Collection Editor, for the Name parameter, enter Project, and for the ValueExpr parameter, enter =[PMProject.ContractCD].

   The Project parameter will be passed from the main report to the subreport.

8. Click OK to close the ExternalParameter Collection Editor.
9. On the Report Designer window toolbar, click Save.

The PM6240C1 report with the COProfit subreport is shown in the following screenshot.

Step 3: Viewing the Report with the Subreport

To view the report, do the following:

1. In MYOB Acumatica, open the S150 Project Profitability (PM6240C1) report form by searching for its identifier.
   Tip:

   This report, which you have modified in this activity, has been published in the U100 dataset. That is, it has been added to the Site Map (SM200520) form, and you can access it in MYOB Acumatica.
2. On the report form toolbar, click Run Report.

In the report (shown in the following screenshot), make sure that the change orders displayed for the project are related to the correct project. To do this, you can open Projects (PM301000) form and select the project identifier that is displayed in the report. On the Change Orders tab, the list of change orders should match the list of change orders in the report.