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.

Business Purpose

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:

2. Create a sub-report showing all invoices related to a specific project, pminvoices.rpx

Add the report as a sub-report to the main report

Today in Part 2, we will cover:

Create a sub-report showing all invoices related to a specific project, pminvoices.rpx

Add the report as a sub-report to the main report

Create a report showing open and closed purchase orders related to a project (pmpos.rpx)

Add the report as a sub-report to the main report

Create a report showing activities related to a project (pmactivities.rpx)

Add the report as a sub-report to the main report

Goal

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 the last post tomorrow.

Create a Sub-Report showing all project purchase orders

To continue building out our comprehensive report, we create a report that shows purchase orders relating to the project. We want to capture all purchase orders, including those which have not yet been received and invoiced.

Purchase Orders Report Schema

This report will be slightly different than our AR invoices report because the project ID for purchase orders is specified at the line level instead of at the document level. The tables of interest are:

POOrder: this data access class includes all the purchase orders in the system.

POLine: this data access class includes line item details. Since the project ID can differ at the line level (a single PO can contain items for multiple projects), we need to use this table

These tables can be joined in the Relationshipstab by linking the OrderNbr fields as shown below.

Go to the Parameters tab to create a parameter for the projectID since this will be passed from the main report. This is shown below.

Go to the Filters tab to create filtering criteria … we only want to select records related to the current project.

Purchase Orders Report Grouping

We need two grouping conditions in this report.

Project ID: this is used to group all the purchase order lines associated with a project ID.

Purchase Order: within a project there could be multiple purchase orders. We want to display a single line for each purchase order, so we need to group all the purchase order lines by purchase order and summarize them.

The Group Collection Editor and the GroupExp Collection Editor are shown for both grouping conditions below.

After adding the two groups, there are 7 sections in our report:

pageHeaderSection1 (not used)

groupHeaderSection 2 (holds the labels for our fields)

groupHeaderSection 1 (not used)

detailSection1 (not visible, used to accumulate values to be summed)

groupFooterSection1 (shows results for each PO)

groupFooterSection2 (not used)

pageFooterSection1 (not used)

In order to sum the amounts for a project, create variables for the amount columns in our details section.

The table below describes the data to enter for these variables.

Variable Name

ValueExpr

POamount

=$POamount+

POopenAmt

=$POopenAmt+

The ResetGroup is set to POgroup in each case … this resets the values each time we get to a new purchase order.

Purchase Orders Field Values

There are eight fields of interest in this report. The values for these fields are listed in the table below.

Box Name

Location

Value

Line Order Number

Detail Section

=

Line Order Date

Detail Section

=

Line Amount

Detail Section

=

Line Open Amount

Detail Section

=

Order Number*

Footer

=$POnumber

Order Date

Footer

=$POdate

Order Amount

Footer

=$POamount

Order Open Amount

Footer

=$POopenAmt

* To link the order number field … to the purchase order, set the NavigateUrl property of this field to =’Main.aspx?ScreenId=PO301000&OrderType=RO&OrderNbr=’+

Sidebar 1: Add purchase order sub-report to the main report

To add the purchase orders 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.

Create a Sub-Report showing project activities

In this section to explain how to create a report that shows activities related to the project. Activities can include things like tasks, timecard entries, work items, emails, and user defined activities.

Activities Report Schema

This report contains only one table, so there are no relationships.

EPActivity: this table contains a list of all employee activities related to different objects.

In the Parameters tab create a parameter for the projectID that will be passed from the main report. This is shown below.

In the Filters tab create a filter so we select only activities related to a specific project.

Activities Report Grouping

Create a group based on project ID for two reasons:

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.

We want to sort activities so the most recent activities are listed first.

The values for the Group Collection Editor and the GroupExp Collection Editor are shown in the screen shot below.

Activities Report Field Selection

After creating the group above, there are 5 sections available for our report. We will add text fields to 2 of these sections as shown below. The table contains more details regarding the text boxes.

Box Name

Location

Value

Other Info

Date Label

Header of projectID*

Date

Person Label

Header of projectID

Who

Activity Label

Header of projectID

Activity

Date Field

Detail Section

=

Format = M/dd/yyyy

Owner Field

Detail Section

=

WrapText = False

Activity Summary

Detail Section

=

WrapText = False

* Set the StyleName = Top Line in the header section to print a header line above this report.

Sidebar 1: Add activities sub-report to the main report

To add the purchase orders 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.

Tomorrow, we’ll focus on adding the subreport comparing budgeted and actual expenses. 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!

Director of Partner Programs and Enablement at Acumatica. Doug is in charge of showing people the specifics about what makes Acumatica’s Cloud ERP software awesome for our customers and partners. For other tips and technical training, stay tuned on Tuesdays.

If you decide that you no longer want to receive a newsletter, you can unsubscribe by clicking the
"Unsubscribe" link, located at the bottom of each newsletter.

About Acumatica ERP system

The Acumatica ERP (Enterprise Resource Planning) system delivers adaptable cloud and mobile technology with a unique, all-inclusive user licensing model. Acumatica provides a complete real-time view of your business anytime, anywhere, on any device. Through our worldwide network of partners, Acumatica ERP provides a full suite of integrated business management applications, including Financials, Distribution, Manufacturing, Project Accounting, eCommerce, Field Service, and CRM. There is only one true Cloud ERP platform designed for mid-sized customers: Acumatica.