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

Doug Johnson | October 1, 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 three posts, we've been building 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

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

Yesterday in Part 2, we covered:

  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

And today in Part 3, we'll look at how to supplement the report with Budget and Actual expense reporting:

  1. Create a report showing comparing budget and actual expenses (pmbudgetgraph.rpx)

    1. Add the a graph to the budget report
    2. 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.

projectoverview

Create the budget comparison report and graph

Open the Report Designer and select the option to build a schema.

Schema Details

The main table used for this report is the PMProjectStatus table. The PMProject table is required for capturing the project ID used as a reference to the report:

  • PMProjectStatus: the data access class that contains summary information about all released project transactions as well as project budget. This table makes it easy to compare budgets to actuals.
  • PMProject: the data access class that provides top level project level information. This includes all the system projects and summary data about each.

To relate the tables, enter the information below into the Relationships tab of the Schema Builder.

PR307

After setting up the report relationships, go to the Parameters tab.

  • Define a parameter called Project as illustrated below. Use the ContractID field from the PMProject table.

pr306

Next go to the Filters tab and select records from a single selected project as illustrated below.

pr305

I explicitly list the expense account groups that I want to include in my report. You could also include project revenues, but this would impact the totals we will compute for the report.

Budget Comparison Report Grouping

Creates groups based on project ID and Account Group:

  1. The ProjectID allows us to break out totals by project. Since a project is passed from the main report, this is important for displaying the proper information.
  2. The Account Group allows us to compute totals by Account Group allowing us to provide an additional level of detail to our report.

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

pr304

Box Name GroupExp Collection Editor DataField Other Info
projectID Grouping =
AccountGroup Grouping =

Budget Comparison Variables

After creating the groups, define 4 variables by clicking the Variables Collection in the details Section of the report. Two variables store the amounts for each account group while 2 variables store amounts for the entire project so we can include a total amount. The values for these variable are provided in the table below.

Variable Name Reset Group ValueExpr
BudgetGroup AccountGroup =$BudgetGroup+
ActualGroup AccountGroup =$ActualGroup+
BudgetProject projectID =$BudgetProject+
ActualProject projectID =$ActualProject+

An image that shows adding the first variable is shown below.

pr303

Budget Comparison Report Field Selection

After creating the groups, there are 7 sections available for our report. We will add text fields to these sections as shown below. The table contains details regarding the text boxes.

PR302

Box Name Location Value
Category Field Footer of AccountGroup =
Budget Sub-Total* Footer of AccountGroup =$BudgetGroup
Actual Sub-Total* Footer of AccountGroup =$ActualGroup
Remaining Sub-Total* Footer of AccountGroup =$BudgetGroup-$ActualGroup
Budget Total** Footer of projectID =$BudgetProject
Actual Total** Footer of projectID =$ActualProject
Remaining Total** Footer of projectID =$BudgetProject-$ActualProject

* Set the Format = #,###,###,##0.00 for proper number formatting

** Set the StyleName = Header1 to add emphasis for the total column. This requires setting the StylesTemplate of the main report to TemplateForm.rpx. We also added a dotted line above the Footer of projectID section.

Sidebar 1: Add a graph to the budget comparison report

Adding graphs to your report provides visual appear to the data that you collect. In this section, we add a simple bar graph to compare the total budget to the total expenses for the project.

To do this, follow these steps:

  • Click the Chart icon, then click the location in the Footer of projectID where the graph will be located.
  • Set ChartType = Bar in the Properties area of the Chart
  • Create two data points and set the values for them as shown below

PR301

  1. Click the Series collection in the Chart properties
  2. Click the Points collection in the MSChartSeries Collection Editor
  3. Click Add a member in the MSChartDataPoint Collection Editor
  4. Set the YValueExpr to =$ActualProject and the AxisLabel = Actual in the properties area of the first data member
  5. Set the YValueExpr to =$BudgetProject and the AxisLabel = Budget in the properties area of the first data member
  • Select other field values for proper formatting of your report

    • Delete the legend in the Properties area of the Chart.
    • Set the Axis colors = White to hide them as necessary in the Properties area of the Chart
  • Open the ExternalParameter Collection Editor by clicking the Parameters icon

The nuances of the graph tool are beyond the scope of this article.

Sidebar 2: Add budget comparison 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.

Conclusion

The project report summarizes several elements of a project on a single overview report. Each area provides drilldown to quickly gain insights to individual documents.

Several other enhancements could be added to this report. Some suggestions:

  • Add a sub-report listing bills related to a project (easy)
  • Add a sub-report listing hours an employee worked on the project (easy)
  • Add a sub-report with details of expense reports related to a project (easy)
  • Add additional charts and graphs for rapid visualization (moderate)
  • Add drilldown capabilities to the budget and actuals on the comparison report (advanced)

Acumatica Projects can include data from virtually every other module in Acumatica. This makes the project summary report valuable in providing at a glance data describing the overall project.

Create a presales support request if you would like to receive the report and sub-reports referenced in this document.

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!

asdasd
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

Subscribe