Lesson 11: Exporting Reports

In this lesson, you will learn how to export reports.

Report Export: General Information

Acumatica ERP reports can be exported as XLS and PDF files. In the Acumatica Report Designer, you can specify the settings to export a report as an XLS file.

Learning Objectives

In this chapter, you will learn how to export report results by using the Report Designer.

Applicable Scenarios

You may need to learn about the exporting of report results 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 export reports from Acumatica ERP to send them by email or to use them for further processing in Excel.

Export of Reports in Acumatica ERP

In Acumatica ERP, you can export any report, predefined or custom, that has been generated. The system has two options of export: as an XLS file, or as a PDF file. To export a report in Acumatica ERP, you run the report, and on the report toolbar, you select one of the following menu commands, based on the format you need:

Export > Excel: If you plan to export a predefined or custom report as an XLS file, then in the Report Designer, you can configure the report's settings in advance.
Export > PDF: The system exports the report to a PDF file as is; you cannot configure any settings of this report.

Excel Mode for Export

In the Report Designer, you manage the export settings of a report to XLS file by using the Layout > Excel Mode property. By default, for any newly created report, this property is set to Auto. With Auto, some reports are exported readably. Other reports exported to an XLS file can have displaced cells with data in the grid. These reports are diﬀicult or even impossible to read. For these reports, before report generation and export, you need to mark up cells and columns in the Report Designer. To do this, you set the Layout > Excel Mode property to Manual and then specify the needed settings. To prepare a report for export, you can perform the following actions (which are further described in the remaining sections of this topic) in the Report Designer:

Display the Excel column grid.
Adjust or define the list of Excel columns.
Define the row and column position of all report elements.
Adjust the position of each element. Lesson 11: Exporting Reports | 111

Specify additional settings for the margins between sections and the export status of elements.
Aer you specify all necessary settings, you save the report. It can be run and exported to Excel.

       For tabular reports, setting the Layout > Excel Mode property to Manual is not supported. The system
       generates columns automatically for such a report and cannot adjust them according to the settings
       that you specify manually in the Report Designer.

Display of the Excel Column Grid

To display the Excel column grid, on the menu bar of the Report Designer, you click View > Excel Grid > Show Columns. In this display, each element on the report layout is marked with two numbers divided by a colon (see the following screenshot).

Figure: Display of the Excel column grid for the report

These numbers mean the following:

The first number is the row in Excel worksheet to which the element content will be exported. This number is specified by the value of the Layout > Excel Cell > Row property.
The second number is the column to which the element content will be exported. This number is specified by the value of the Layout > Excel Cell > Column property. In each section, the numbering of rows starts from 1. You can change the numbers of rows and columns to define the order of elements within each exported section. You cannot change the order of the sections in the exported report, however. You can generate the list of columns automatically according to the number and width of elements of any section. For this purpose, you can use the section with the maximum number of elements. You right-click the header of this section, and then select Generate Excel Columns.
```
        If you need to refresh the Excel column grid, on the menu bar, you can click View > Excel Grid > Hide
        Columns, and then click View > Excel Grid > Show Columns.
```

Adjustment or Defining of the Excel Columns

To adjust the number, order, and width of columns to be exported to Excel, you select the report; then in the Layout > Excel Columns property, you click the More button to open the Int32 Collection Editor (that is, the Int32 Collection Editor dialog box), as shown in the following screenshot. Lesson 11: Exporting Reports | 112

Figure: The Int32 Collection Editor

In the Members pane of the Int32 Collection Editor, the first number corresponds to the order of the column in the Excel grid. 0 is the first column (A) of the Excel grid, 1 is the second (B), and so on. To specify the width of each column, you click the column in the Members pane; then you specify the Value property of the right pane of the Int32 Collection Editor. The column width corresponds to the element width in pixels in an approximate 1:6 ratio. For example, the width of a column with a Value of 10 is approximately equal to the width of a text box with a Size > Width of 60px. Based on the settings in the screenshot above, the report will be exported to Excel with the following settings:

The report will have columns from A (which corresponds to 0) to L (which corresponds to 11).
The A column will have a width of 10.

Row and Column Positions of Report Elements

To define the row and column position of all the report elements, on the Report Designer menu bar, you click Edit

Adjust Excel Layout. The Report Designer automatically changes the row and column position of all the report elements depending on how they fit the Excel columns. This functionality exports the report as expected for simple report layouts but may cause unexpected results for some complicated layouts. We recommend that you assign the row and column position for report elements automatically. You can adjust the positions manually later in Excel, if needed.

          Similarly, you can define the row and column position of the elements of any particular section. You
          right-click the section header and then select Adjust Excel Layout.

The Position of Each Element

In the Report Designer, you can adjust the position of each element in a report. To do this, you use the Layout > Excel Cell > Row and Layout > Excel Cell > Column properties.

Additional Settings

In the Report Designer, you can specify the following additional settings for a report:

Margins between sections: You specify this setting (in number of rows) in the Layout > Excel Margin > Top and Layout > Excel Margin > Bottom properties. Lesson 11: Exporting Reports | 113

Export status of an element: If you do not want to export a particular element of the report to Excel, you select the element; then in the Layout > Excel Visible property, select False. By default, the property is set to True.

                The export of SubReport, Chart, Line, and ImageBox elements to Excel is not supported. Also, if
                you want to export the contents of a Panel element to Excel, you should specify the position—a
                row and a column—of each element inside the panel.

Report Export: To Export a Report as an XLS file

In the following activity, you will learn how to export a report to an XLS file.

          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 of your company has requested the Vendor Summary (AP655000) report in XLS format. The sales manager plans to process the data further in Excel. You plan to use a copy of the report; aer checking its settings, you will run the report and export it.

Process Overview

In the Report Designer, you will open the AP6550C4.RPX report, which is a copy of the Vendor Summary (AP655000) report. You will make sure that the report is simple and can be exported without additional settings being specified. Then in Acumatica ERP, you will run the report and export it as an XLS file.

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 is intended for this activity, do the following:

Download the AP6550C4.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 AP6550C4 as the report name, and click OK. The report is saved on the server.

Step 1: Checking the Export Setting To check the report's export setting, do the following:

In the Report Designer, make sure that the AP6550C4 report (which you have saved to the server) is open. Lesson 11: Exporting Reports | 114
In the top le corner of the Design pane of Report Designer, click the icon to select the report, and set the Layout > Excel Mode property to Auto.
On the Report Designer window toolbar, click Save.

Step 2: Exporting the Report as an XSL File To export the report, do the following:

In Acumatica ERP, open the S150 Vendor Summary (AP6550C4) 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. The report is displayed.
On the report toolbar, click Export > Excel. The XLS file, whose name consists of the name of the report (Vendor Summary) and the current date, is uploaded to the default folder on your computer.
Open the XLS file to view the result of the export in Excel (see the following screenshot).
Figure: The exported Vendor Summary (AP6550C4) report opened in Excel Lesson 12: Localizing Reports | 115