Recently, I was working on a report for a client. We had to utilize the sub-report functionality of Acumatica’s report designer. This most often happens because you need different data sources that cannot be aggregated within a single report.
In SQL languages, we utilized subqueries for this. Since this functionality is still in development for the Report Designer and Generic Inquiries, I was leaning toward the Sub-report.
The report's primary objective was to compile all the Service Orders (SVOs) and appointment details for a linked Service Contract. To provide a comprehensive report, Labor, Material, and Travel Expenses should be consolidated. Along with an overview of Contract Revenues and Fixed Asset values.
This meant I would need at least one sub-report to handle the complex calculations happening in this report. As development progressed, I realized that I also needed to pass values summed up in the sub-report into the main report. This proved really simple once I tracked down all the pieces from various sites and blogs.
The documentation on sub-reports is thin, but the basics are very simple. Information can be found in the S130 or S150 training manual, depending on the year and version you reference. At the time of writing S150 for 2022R2 pgs 53-57.
Here is a high-level recap of what I did to get my subreport created.
Navigate to the section of the report you want the subreport to be a part of.
Select the subreport tool to draw a box that will limit the area the subreport can occupy in your main report.
Draw your box.
Now you want to modify the subreport ReportName property and the Name property.
In my case, I give the subreport properties the following values.
My next step is to create a new report that will be the subreport.
I then create my subreport like any other report in Acumatica.
My main report passes a value from the Main Report to the Sub Report via a parameter called Appointment.
My sub-report then filters the records it gets from Acumatica using the parameter on the filters tab of the schema.
My Sub report runs and does some calculations to provide details of who clocked to the appointment and what their total fixed labor costs was.
This total labor cost is then stored in a variable to be passed back to the main report later.
The first challenge is getting your main report to reference the Variable from the sub-report.
In my example, one of the totals I wanted to pass back to the report was the $LaborCost. The following blog posts helped get me 90% of the way there.
To call the variable from the subreport, I needed to reference $subReport1_LaborCost
$ for Variable, subReport1 for the Name property of my subreport, and LaborCost for the variable name on the report.
Similarly, if I had a second report, I could reference the variable $ExpenseCost from it with the following
$subReport2_ExpenseCost
The next challenge was that when I used this and tried to do calculations with it, Acumatica Report Designer would tell me it didn’t exist. This took much longer to locate, but I finally realized why the value was not being interpreted correctly or causing an error.
This post was the key to helping me understand the issue.
When Acumatica passes the value from the Sub Report to the Main report, it does so as a string/text. If you want Acumatica to use it in any other way, for example, in my Sum(). I needed to convert it to that value even if the Subreport variable is already of that type.
In the end, my equation to display the total was this:
=Format('{0:N}',CDec($subReport1_LaborCost))
And to Sum up, the Contract level was:
=Format('{0:N}',Sum(CDec($subReport1_LaborCost)))
Formatting it to a 2 decimal place number, summing up the section from the converted decimal number from the passed string value from the summed report.
