Lesson 5: Filling a Report with Content

This lesson explains how to fill reports with content.

Report Content: General Information

The content of a report designed in the Acumatica Report Designer includes visual elements, which can contain text, data, and images. The visual elements are placed within the report sections, and their appearance and behavior are determined by both the properties of the visual elements themselves and the properties of the report section where they are placed.

Learning Objectives

In this chapter, you will learn how to do the following in the Report Designer:

Add, delete, and move elements in the report layout
Specify the properties of the elements added to the report layout

Applicable Scenarios

You may want to add content to reports you develop in the Acumatica Report Designer in the following circumstances:

You are responsible for the customization of Acumatica ERP in your company, including developing and modifying reports to give users the information they need to do their jobs.
You need to add content—that is, data, images, charts, and subreports—to predefined reports or to reports you create from scratch.

Types of Visual Elements in a Report Layout

You can add to the report layout the following visual elements:

TextBox: You can use a text box to display text or data from the database in the report.
PictureBox: You can use a picture box to display an image in the report. For example, you can place your company logo on the report, as you do on other company materials. You can also use the PictureBox element to add a QR code or barcode to your report.
Panel: A panel is a container of elements that are processed together. You can place multiple elements on a panel and specify a background color, text alignment, or visibility property for the panel.
Line: A line can be used to divide the report space, direct the eye, or visually separate elements in the report. You can add lines to improve the look and readability of the report.
Chart: A chart is a graphical representation of data in the report. You can use diﬀerent types of charts—for example, line, bar, or pie—to present information, such as sales by month and by sales manager.
SubReport: You can use a subreport to include data from other reports in the current report. For example, you can define as a subreport the list of change orders in a project. By using subreports, you can reduce the time it takes to form a complex report that extracts data from multiple related tables.

Operations with Visual Elements in a Report

When you add content to a report, you generally perform the following steps: Lesson 5: Filling a Report with Content | 44

Adding visual elements to the report
Optional: Linking the visual elements with the data to be displayed in the report
Specifying the visual elements' properties
The visual elements are located in the Tools pane of the Report Designer main window. To add a visual element to a report section, you click the element in the Tools pane, and drag it to the desired position within the report. You can resize the element by dragging its borders. You can also move the element within the section or to another section. When you place elements in a report layout, you need to make sure that the elements do not overlap. The Report Designer crosses out all visual elements that overlap. Overlapping elements will not cause a runtime error in the report; but only one of these elements will be shown in the report. To fix the overlapping visual elements, you have to adjust the layout and move or resize the elements. Aer you add a visual element to the report, you can do the following:

Define the style of the element
Copy and paste the style of visual elements
Define the order of visual elements on the screen by bringing them to the front or sending them to the back
Cut, copy, and paste visual elements and their content to other areas within the report To perform these actions with a visual element, you can do the following:
Use the actions available on the Report Designer window toolbar or menu bar
Right-click the visual element and then select the relevant command from the shortcut menu
Use Windows-standard combinations of keys to perform common actions, such as cutting or pasting elements
```
        You can delete a selected visual element by pressing the Delete key.
```
For more information about the style of elements, see Report Style: General Information.

Text and Data in a Report

For text and data in a report, you use the TextBox element. For this element, you use the Appearance > Value property to specify the contents of the text box. You can specify text, a data field of the database table, or an expression. For more details about expressions, see Variables and Expressions: General Information. You can add a TextBox element with a field name as its value in one action by dragging the field from the list that is located on the Fields tab of the Properties pane. All parts of the text in a text box have the same formatting. If you need to have diﬀerent formatting for diﬀerent parts of the text, you should divide the text and use multiple text box elements, each of which has its own formatting.

Images in a Report

You use the PictureBox element to add an image to an Acumatica ERP report. You can do any of the following:

Embed an image file in the report. Embedded images are stored along with the report file, and you include them in the report as its inner elements. You can add images of the following types to the collection of embedded images: bmp, gif, jpg, and png. You first specify the name of the image that you are embedding as the Data > EmbeddedImages property for report1 Report. Then you specify the following properties for the PictureBox element:
Data > Source: Embedded
Data > Value: The file name of the image Lesson 5: Filling a Report with Content | 45
Select an image from an external file. External files are stored on external resources, such as websites or local hosts, that are accessible from the Acumatica ERP application site where the reports are published. The report stores only the link to the external file where the image file is located. For an external image, you specify the following properties for the PictureBox element:
Data > Source: External.
Data > Value: The URL of the image. For example, suppose that you have uploaded the MyImage.png file to the /Site/Images/ folder. In the Data > Value property, you should enter the following URL: Images/MyImage.png.
Select a data field and load an image from it. In Acumatica ERP, all images are stored in the UploadFile table. For an image inserted in a report from the database, you specify the following properties for the PictureBox element:
Data > Source: Database
Data > Value: =UploadFile.Name

QR Codes and Barcodes

The Report Designer considers QR codes and barcodes to be pictures. To add a QR to an Acumatica ERP report, you add a PictureBox element and specify the following properties for it:

Behavior > QRCodeSettings: You use the parameters of this group to specify size and margins of the code.
Data > BarcodeType: You select the QRCode option.
Data > Source: You select the Barcode option.
Data > Value: You specify a URL or a formula to generate a QR code. To add a barcode to an Acumatica ERP report, you add a PictureBox element and specify the following properties for it:
Behavior > BarcodeSettings: You use the parameters of this group to specify size and margins of the code.
Data > BarcodeType: You select a type of the needed barcode.
Data > Source: You select the Barcode option.
Data > Value: You specify a URL, a human-readable value, or a formula to generate a barcode. With the Layout > Sizing property, you can adjust the sizing of an image so that the image can keep its proportions, fill the whole space of the picture box, or be placed in the center of the picture box. By using the Report Designer, you can generate the GS1-128 (previously known as UCC-128 and EAN-128) barcodes from the GS1 family. To generate a GS1-128 barcode, you select the UCC128 option in the Data > BarcodeType box. You should use brackets as a separator for GS1 codes. Suppose that you manufactured new items and need to print labels to receive the new items. You may use the Inventory Item Labels (IN619200) report to print the labels. You add a PictureBox element to the report and specify settings needed for barcode printing. In the Data > Value box you add the following human-readable value: (01)3333(17)230909(10)LREX50(37)15. The value is read as follows:
(01) is the GS1 code for the item ID,
3333 is the item ID,
(17) is the GS1 code for the expiration date,
230909 is the expiration date in the YYMMDD format,
(10) is the GS1 code for the lot/serial number,
LREX50 is the lot number,
(37) is the GS1 code for the quantity,
15 is the quantity of the item in the lot. For more details on GS1-128 barcodes, see GS1 Barcodes in Acumatica ERP. Lesson 5: Filling a Report with Content | 46

Charts in a Report

You use the Chart element to define a chart in your report. For the element, you specify properties in the Chart and ChartData groups of properties.

Subreports

You can add any number of subreports to a single main report by using the SubReport element. If a SubReport element is placed in any report section, the entire subreport will be printed in that section, except for the page header and page footer sections.

          Although you can insert a subreport into any report, we do not recommend that you insert a
          subreport into another subreport because it can degrade the performance of the server.

You specify the following properties for the SubReport visual element:

Behavior > ReportName: The file name of the subreport
Behavior > Parameters: The parameters that are passed from the main report to the subreport A subreport can be either unlinked or linked to the data in the main report. Unlinked subreports are standalone reports, and their data is not linked to the data in the main report. An unlinked subreport can use the same data source or a diﬀerent data source entirely. Linked subreports use data that is coordinated with data in the main report. The Report Designer matches up the data in the subreport with the data in the main report. If you create a main report with customer information and a subreport with order information and then link them, the Report Designer creates a subreport for each customer that includes all of this customer’s orders. Before you insert a subreport of either type into the main report, you must be sure that the subreport can be run as an independent report. You do not need to publish the subreport—that is, to add it to the Site Map (SM200520) form —to make sure it can be run. You can preview the subreport on the Preview tab of the Report Designer.
```
        The main report and the subreport must be saved on the same server.
```

Report Content: To Add QR Codes to a Report

In the following activity, you will learn how to add QR codes to reports.

          This activity is based on the U100 dataset. If you are using another dataset, or if any system settings
          have been changed in U100, these changes can aﬀect the workflow of the activity and the results of
          the processing. To avoid any issues, restore the U100 dataset to its initial state.

Story

Suppose that you are a technical specialist in your company who is working on simple customizations. A sales manager has requested a report that displays the details of a particular invoice or memo, including the QR codes of the included items. You have decided to use the predefined Invoice/Memo (AR641000) report as a base and modify it by adding the QR code of each item. Lesson 5: Filling a Report with Content | 47

Process Overview

In the Report Designer, you will open the AR6410C2.RPX report, which is a copy of the Invoice/Memo (AR641000) report, and add the PictureBox element to the report layout. You will specify the properties of this element to display a QR code for each item in the report.

System Preparation

Before you begin performing the steps of this activity, launch the Acumatica ERP website with the U100 dataset preloaded, and sign in as a system administrator Kimberly Gibbs by using the gibbs username and the 123 password. Also, to prepare for use the file that you will need for this activity, do the following:

Download the AR6410C2.rpx file.
Open the downloaded file in the Report Designer.
On the Report Designer menu bar, select File > Save To Server, which opens the Save Report on Server dialog box.
In the dialog box, specify the connection string and sign-in credentials of your Acumatica ERP instance, type AR6410C2 as the report name, and click OK. The report is saved on the server.

Step 1: Adding the QR Code of Each Item To add the QR code of each item in the AR6410C2 report, do the following:

In the Report Designer, make sure that the AR6410C2 report (which you have saved to the server) is open.
Select the detailSection1 section and specify the 3.5cm value for the Appearance > Height property to increase the height of the section.
In the Tools pane, drag the PictureBox element to the le side of the detailSection1 section, below the text boxes.
To show the QR code of the item, while the PictureBox element is selected, specify the following properties in the Properties pane:

Data > Source: Barcode.
Data > BarcodeType: QRCode. This value indicates that the image will be a QR code.
Data > Value: ='http:///Main? ScreenId=AR301000&InventoryCD='+ARTran.InventoryID For example, Data > Value can be ='http://my-server.acumatica.com/InstanceName/ Main?ScreenId=AR301000&InventoryCD='+ARTran.InventoryID. Based on this value, each item will contain the QR code that is linked to the appropriate item. Notice that the link contains the application URL explicitly.
Behavior > QRCodeSettings > CodeSize: 64. The default CodeSize of any QR code image is 50. If the length of the string you have specified as the Value is too long, the QR code will disappear from the image preview on the report layout. If the user runs this report, the QR code will be invisible too. The larger the string whose value you have specified for the barcode, the larger the CodeSize you should specify to make the image visible.
Layout > Size: 64px; 64px.

On the Report Designer window toolbar, click Save. Lesson 5: Filling a Report with Content | 48

Step 2: Viewing the Report To view the report, do the following:

In Acumatica ERP, open the S150 Invoice/Memo (AR6410C2) report form by searching for its identifier.

              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 Acumatica
              ERP.

On the Report Parameters tab, in the Document Type box, make sure Invoice (the default value) is selected.
In the Reference Number box, select 000058.
On the report form toolbar, click Run Report.
Make sure that QR code is displayed for each item in the invoice, as shown in the following screenshot. Lesson 5: Filling a Report with Content | 49
Figure: The S150 Invoice/Memo (AR6410C2) report with the QR codes of items

Report Content: To Embed an Image in a Report

In the following activity, you will learn how to embed an image in a report.

          This activity is based on the U100 dataset. If you are using another dataset, or if any system settings
          have been changed in U100, these changes can aﬀect the workflow of the activity and the results of
          the processing. To avoid any issues, restore the U100 dataset to its initial state.

Story

Suppose that you are a technical specialist in your company who is working on simple customizations. A sales manager has requested that you add an image of his signature to a report that displays the details of an invoice or memo. You have decided to use the predefined Invoice/Memo (AR641000) report as a base and modify it by adding the image of the signature, which the sales manager has sent to you as a PNG file. Lesson 5: Filling a Report with Content | 50

Process Overview

In the Report Designer, you will open the AR6410C3.RPX report, which is a copy of the Invoice/Memo (AR641000) report, and add the PictureBox element to the report layout. You will specify the properties of this element to display the facsimile.png image.

System Preparation

Download the AR6410C3.rpx file.
Open the downloaded file in the Report Designer.
On the Report Designer menu bar, select File > Save To Server, which opens the Save Report on Server dialog box.
In the dialog box, specify the connection string and sign-in credentials of your Acumatica ERP instance, type AR6410C3 as the report name, and click OK. The report is saved on the server.
Finally, you should download the facsimile.png file and save it to any folder on your computer.

Step 1: Embedding the Image in the Report To embed the image in the AR6410C3 report, do the following:

In the Report Designer, make sure that the AR6410C3 report (which you have saved to the server) is open.
On the Properties tab of the Properties pane, select the report1 Report object from the drop-down list to select the report form, and do the following: a. In the Data > EmbeddedImages property, click the More button to open the Embedded Images dialog box. b. In the dialog box, click the New image ... button, which is shown in the following screenshot, and select the facsimile.png file, which contains the needed image. (The Name and Mime Type of the selected file are filled in automatically.)
```
       Figure: The embedded image in the Embedded Images dialog box
```
Click OK to add the image that you have selected to the embedded images collection of the report and to close the Embedded Images dialog box. Now you can use this image in the report. Lesson 5: Filling a Report with Content | 51
Select the groupFooterSection2 (Footer of groupInvoice) section and specify the 2.5cm value for the Appearance > Height property to increase the height of the section.
In the Tools pane, drag the PictureBox element to the le side of the groupFooterSection2 (Footer of groupInvoice) section.
While the element is selected, specify the following properties for the embedded image:

Data > Source: Embedded
Data > Value: facsimile
Layout > Size: 2cm; 2cm
Layout > Sizing: Scale By specifying the Scale value for the Layout > Sizing property, you adjust the sizing of the image so that the image keeps its proportions.

On the Report Designer window toolbar, click Save.

Step 2: Viewing the Report To view the report, do the following:

In Acumatica ERP, open the S150 Invoice/Memo (AR6410C3) report form by searching for its identifier.

              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 Acumatica
              ERP.

On the Report Parameters tab, in the Document Type box, make sure Invoice (the default value) is selected.
In the Reference Number box, select any number, for example, 000058.
On the report form toolbar, click Run Report.
Make sure that the image you have added is displayed below the list of items in the invoice. The following screenshot displays the report. Lesson 5: Filling a Report with Content | 52
Figure: The Invoice/Memo (IN6410C2) report with the image added

Report Content: To Add a Subreport

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

          This activity is based on the U100 dataset. If you are using another dataset, or if any system settings
          have been changed in U100, these changes can aﬀect the workflow of the activity and the results of
          the processing. To avoid any issues, restore the U100 dataset to its initial state.

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 Acumatica ERP 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. Lesson 5: Filling a Report with Content | 53

System Preparation

Download the PM6240C1.rpx file.
Open the downloaded file in the Report Designer.
On the Report Designer menu bar, select File > Save To Server, which opens the Save Report on Server dialog box.
In the dialog box, specify the connection string and sign-in credentials of your Acumatica ERP 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:

On the Report Designer menu bar, click File > New. An empty report with the default sections opens.
On the Report Designer menu bar, click File > Build Schema. The Schema Builder opens with the Tables tab selected.

In the Site URL to Load Database Tables box of the tab, enter the URL of your Acumatica ERP website, which can be your local website or an external URL of Acumatica ERP.

               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.

Type gibbs as the username and 123 as the password.
Click the Load Schema button.
In the Search box, type ChangeOrder, and in the 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.
To link the subreport with the main report, do the following: a. On the Parameters tab of the Schema Builder, click Add to add a parameter. b. In the Name box, type Project. c. On the Filters tab of the Schema Builder, add a row with the following settings:

Data Field: PMChangeOrder.ProjectID
Condition: Equal
Value1: @Project

In the Schema Builder, click Apply to apply the changes and OK to close the window.

In the Tools pane, drag the TextBox element to the detailSection1 section, on the le. 10.As the Appearance > Value property of the added text box, specify =PMChangeOrder.RefNbr. Lesson 5: Filling a Report with Content | 54

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:

Site URL to Load Database Tables: The URL of your Acumatica ERP website
Login: gibbs
Password: 123
Name of Report 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.
Figure: The subreport that lists the change orders of the specified project

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 aer you add the created subreport to this section. To insert the subreport in the PM6240C1 report, do the following:

In the Report Designer, open the PM6240C1 report (which you have saved to the server).
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.
In the Tools pane, drag the SubReport element to the groupHeaderSection1 (Header of ProjectGroup) section, below the text boxes.
While the SubReport element is selected, in the Behavior > ReportName property of the Properties tab, type the name of the subreport: COProfit.rpx. Lesson 5: Filling a Report with Content | 55
To the right of the Behavior > Parameters property, click the More button.
In the ExternalParameter Collection Editor, which opens, in the Members pane, click Add.
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.
Click OK to close the ExternalParameter Collection Editor.
On the Report Designer window toolbar, click Save.
The PM6240C1 report with the COProfit subreport is shown in the following screenshot.
Figure: The report with the subreport

Step 3: Viewing the Report with the Subreport To view the report, do the following:

In Acumatica ERP, open the S150 Project Profitability (PM6240C1) report form by searching for its identifier.

              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 Acumatica
              ERP.

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. Lesson 5: Filling a Report with Content | 56

Figure: The Project Profitability (PM6240C1) report with change orders Lesson 6: Using Variables and Expressions | 57