This series of posts will show you how to use the Report Designer utility to build a project overview report. This tutorial assumes a working knowledge of the Acumatica Report Designer, but it is also designed to give those who don’t have that knowledge a glimpse of what Acumatica Cloud ERP Software can do to transform business processes.
Over the next few posts, we will build a project report designed to provide a project-at-a-glance view with drill-downs to several source documents that make up the project.
Demo Built in Acumatica Environment: 5.20.1227
Data: SalesDemo data from Acumatica’s Partner Portal
Time: Following this guide to create the full report from scratch will take about 2-3 hours, so we are breaking this process up into 3 technical tuesday posts. Report files are available for our partners (for demo purposes) with a presales request from the partner portal.
The Acumatica Cloud ERP project module is linked to almost all other modules in Acumatica. This makes the project module extremely powerful, but presents the need to create a report that shows an overall view of a project.
We will use the Sub-Report feature in the Acumatica Report Designer to create a project report that includes:
- An overview of project information, including custom attributes
- Purchase Orders (open and closed) related to a project
- Invoices related to a project
- Activities and tasks related to a project
- Real-time profitability and budget comparisons
Technical Process Overview
In this first post, we will cover how:
1. Create the main project report, pmsummary.rpx
- a. Add the report to the site map
- b. Add attributes to the project and put them on the report
- c. Include a picture on the report
2. Create a sub-report showing all invoices related to a specific project, pminvoices.rpx
- a. Add the report as a sub-report to the main report
The final report will be similar to the one pictured below. As needed, additional sub-reports and features can be added, and we’ll address some of the most commonly-needed in later posts in the series.
Acumatica Implementation – Building the Report
Create the main project report
Open the Report Designer and select the option to build a schema. The Acumatica Report Designer is a desktop utility you can install in a Windows environment. The Report Designer is available to our partners in the Downloads section of the Acumatica Partner Portal.
The main table used for this report is the PMProject table. Here is a list of other tables:
- PMProject: the data access class that provides top level project level information. This includes all the system projects and summary data about each.
- BAccount: the main data access class including information about the customer that the project is related to.
- Address: a data access class including address information that we will display on the report
- UploadFile: a data access class containing files that have been uploaded to various Acumatica objects. We will be utilizing files linked to a specific project.
- NoteDoc: a data access class containing notes that relate to different Acumatica objects. We will be utilizing notes linked to a specific project.
To relate the tables, enter the information below into the Relationships tab of the Schema Builder.
The table below provides joint information for all the tables:
|Tables to Join||Join Conditions||Comments|
|PMProject Left BAccount||CustomerID = BAccountID||Projects only include customers, the BAccount table includes vendors, employees, as well|
|BAccount Left Address||BAccountID = BAccountID AndDefAddressID = AddressID||The address table includes multiple addresses for each account. This selects the default address to display on the report.|
|PMProject Left NoteDoc||NoteID = NoteID||Selects the project level notes for display on the report.|
|NoteDoc Left UploadFile||FileID = FileID||Stores pictures attached to various business objects. In our case we select pictures that are related to a project|
After setting up the report relationships, go to the Parameters tab.
- Define a parameter called Project as illustrated below. We will use this to select a specific project that will drive results for our main report as well as our sub-reports. Set the input mask and view name as illustrated below.
- Set a default project value. This will save some time when we are testing our report.
Next, go to the Filters tab and select records from a single selected project as illustrated below.
Now we have selected all data necessary to create the header for the report.
Create the Header Layout
In the Details section of the report layout body, add the fields shown below. Key information about each is included in the table below:
|1||=||StyleName* = Heading 1|
|4||=||Add attribute to project – see Sidebar 2|
|6||=||Add a link to the customer record**|
|8||=||Source = Database, Sizing = Scale|
* The styles appear in a dropdown box after you set the report Style Template = TemplateForm.rpx
** Add a link to the customer record by setting the NavigateUrl field to =’Main.aspx?ScreenId=CR303000&AcctCD=’+ and set the Target to _blank so the link opens in a new tab
Sidebar 1: Create a project attribute.
- Navigate to Configuration -> Common Settings > Common Settings => Attributes
- Add the PMDIRECTOR attribute as shown below.
- Navigate to Organization -> Projects -> Configuration -> Setup -> Attributes
- Add the new attribute as a project level attribute as illustrated below.
- Verify and add values to your new attribute by going to the project screen, selecting a project and navigating to the attributes field.
The example above shows that we have successfully added an attribute.
- The new attribute is automatically available to the Acumatica Report Designer. If the field does not show up in the expression editor (illustrated below), then you need to refresh the schema. To do this, follow the steps below.
- Click File -> Build Schema …
- In Table tab, highlight the table you added the attribute to and click the Refresh button (see below) – or click Refresh All.
3. Click “save to server” to save the refreshed schema and make it available to the expression editor.
4. Within the expression editor, you can add the new attribute to the Value expression of the TextBox.
Sidebar 2: Adding the Report to Site Map
Saving the report to the site map will allow you to apply security permissions and test the report. Follow the steps below to add the new report to the site map.
- Select File -> Save to Server
- Enter the URL of the Acumatica site you will save the report to
- Enter the Login/Password combination. In a multi-company deployment you need to specify the company after the username in the format admin@
- Enter the report name (pmsummary.rpx in our example)
- Optional: you can create a separate version of the report if you may need to roll back your changes
- Add the report to the site map
- After completing step 1, the report definition is stored in the Acumatica database in XML format. In order to run the report we must add it to the sitemap and open it with ReportLauncher.aspx
- Navigate to System -> Customization -> Manage -> Site Map
- In the site map, navigate to the directory where you want the report to appear (I added it to Organization -> Projects -> Reports -> Audit)
- Click the ‘+’ sign to add a new line to the site map
- Specify a unique screen ID (I used PM for the first 2 characters to represent the project module)
- Specify a title for the report that will appear in the site map
- Specify an icon from the drop down list if you want an icon to precede the report name
- Specify the URL that is invoked when the menu item is clicked.
- ~/Frames/ReportLauncher.aspx?ID=.rpx is the format
- The code used to generate the report from the XML file that is published by the Report Designer is referenced by ReportLauncher.aspx that is located in the Frames directory.
- Replace the report name with the name you used to save your report (pmsummary.rpx in my case). The .rpx suffix specifies an Acumatica report designer file.
- Run and test the report
- Navigate to the location where you placed the report and click the link to run the report
- The header information of the report that you created should be visible:
Note: the Director, Notes, and picture are visible only if you have added them to your project.
Sidebar 3: Including a picture on the report
When a picture is saved to the files area of the project (see screen shot below), the file is stored in the database in the UploadFile table. You can use the External File Storage settings to change the physical location of the files, but they are still in the ‘database’ as far as the Report Designer is concerned.
To add the image to a report:
- Drag a PictureBox into the report where you want the picture to appear
- In the Source field select “Database” since our picture will come from the database. Files attached to Acumatica objects are added to the database by default. If another storage mechanism is selected, the same mechanism is used to access the files.
- In the Value field enter to reference the file.
- Based on our schema, all images attached to the project record are returned
- If there are multiple images the construct above does not specify which one gets printed, so the first file encountered will appear. You could further qualify the attachment to make sure that it is a jpg image by changing to “=IIf( Right(,4) =’.jpg’,”,)”.
- In the Sizing field, select scale or fit. Scale maintains proportional dimensions for your image, while fit will stretch the image to completely fill the box.
Create a Sub-Report showing all project invoices
A sub-report is a stand-alone report that is referenced by another report. There is no specific designation required for a sub-report … any report can be a sub-report.
Start by opening the report designer and selecting the option to build a schema.
Invoice Report Schema
For the invoice report, our schema is simple, we include one table called ARInvoice. If line item detail is required, we would include the ARtran table as described below.
- ARInvoice: this data access class includes all the customer invoices in the system.
- ARtran: this data access class includes line item details such as inventory IDs, line quantities, unit prices, and other line level detail, then you need to build a report using the ARtran table.
With only one table you do not need to specify relationships or join conditions.
Next we specify the parameters in the Parameters tab.
This step is critical for a sub-reports because the parameters will be passed from the main report.
In this example, we only want to select invoices related to a specific Project ID so we need to define this as a parameter.
Below, in Sidebar 4, we will show how to pass the Project ID from the main report to the sub-report.
Complete the schema by defining the filtering conditions in the Filters tab as shown in the screen below.
Invoice Report Grouping
There are two reasons for creating a grouping condition in our invoices sub-report.
- The pageHeader section of the sub-report is not visible in the main report. We want to include headers in our sub-report so we do not have to line up columns across reports – so we need a group heading area.
- Grouping gives us the option to include totals on the sub-report
To create the group, go to the main report menu and click the icon in the Groups area. Then click the icon in the Grouping area … this opens the windows below.
Steps to follow:
- On GroupExp Collection Editor: group by Project ID by changing the value in field DataField to .
- On Group Collection Editor: change the Description field to a meaningful label such as ‘project ID’.
When complete, the Report Designer adds two new sections to your report: (1) groupHeaderSection1 (Header of projectID) and (2) groupFooterSection1 (Footer of projectID).
Invoice Report Fields
Now we can add the fields to the report. In this case there are 4 labels in the header section and 4 field references in the details section. In this report, I did not add variables to track totals and print a total line.
The field references in the details section are as follows:
Adding a Link to the AR Document
Use the NavigateUrl filed to specify a hyperlink on the reference number field so a person viewing the report can open the AR Invoice document with one click. Acumatica gives you the ability to form an expression so you can navigate to a specific document depending on the field value.
- Change NavigateUrl to =’Main.aspx?ScreenId=AR301000&DocType=INV&RefNbr=’+
- Change the Target field to ‘_blank’ so the screen would open in a new tab or window.
The properties view of this field is illustrated in the screen shot below.
Invoice Report – Save to Server
Save the report to a server in order to (1) test the report, (2) reference this sub-report from the main report.
The report name is important – we will use this name to add the report to the site map and to create a reference from our main report.
Add the new report to the site map as described in the main report and as illustrated below.
After adding the report to the site map, run the report for Project TMR02 – this should produce the result below. The report is very simple because it is designed to be included within a larger project report.
Sidebar 4: Add the invoices sub-report to the main report
To add the invoices report to the main report, perform the following actions:
- Open the PMsummary.rpx report in the Report Designer
- Drag a sub-report object to the location in the details section of the PMsummary report where you want the sub-report to appear.
- Set the properties as shown in the screen shot below
- ReportName: make sure that you match the name that you gave to the sub-report when you saved it to the server
- Size: the size of the report is important if you want all the columns to show
- Open the ExternalParameter Collection Editor by clicking the Parameters icon
- Add the projectID as a parameter, set the ValueExpr as =. This will pass the current project to the sub-report so the proper data is gathered
The next Technical Tuesday post will focus on creating a sub-report with project purchase orders. Got questions or report-building tips on Acumatica? Ask in the comments below, and be sure to subscribe to get these posts in your inbox!