Free Product Tour

Technical Tuesday: Building a Project Overview Report in Acumatica Cloud ERP Software, Part 2

Doug Johnson | September 30, 2015

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

Acumatica's Technical Tuesday

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:

  • 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 Part 1 of this post series, we covered the first steps in building the report:

1. Create the main project report, pmsummary.rpx

  • Add the report to the site map
  • Add attributes to the project and put them on the report
  • Include a picture on the report

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:

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

    1. Add the report as a sub-report to the main report
  2. Create a report showing open and closed purchase orders related to a project (pmpos.rpx)

    1. Add the report as a sub-report to the main report
  3. Create a report showing activities related to a project (pmactivities.rpx)

    1. 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.

Building a Project Overview Report in Acumatica Cloud ERP Software

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 Relationships tab by linking the OrderNbr fields as shown below.

Building a Project Overview Report in Acumatica Cloud ERP Software

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.

Building a Project Overview Report in Acumatica Cloud ERP Software

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.

Building a Project Overview Report in Acumatica Cloud ERP Software

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)

Building a Project Overview Report in Acumatica Cloud ERP Software

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

Building a Project Overview Report in Acumatica Cloud ERP Software

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.

Building a Project Overview Report in Acumatica Cloud ERP Software

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.

Building a Project Overview Report in Acumatica Cloud ERP Software

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

Building a Project Overview Report in Acumatica Cloud ERP Software

Activities Report Grouping

Create a group based on project ID for two reasons:

  1. 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.
  2. 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.

Building a Project Overview Report in Acumatica Cloud ERP Software

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.

Building a Project Overview Report in Acumatica Cloud ERP Software

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.

Building a Project Overview Report in Acumatica Cloud ERP Software

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!

Doug Johnson

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.

Subscribe to our bi-weekly newsletter

Gartner Magic Quadrant: Cloud Core Financial Management Suites

Find out why Gartner included Acumatica in its first ever Magic Quadrant for Cloud Core Financial Management Suites.