Reports and Generic Inquires

Reports, GIs, Dashboards, Pivots

  • 1,282 Topics
  • 4,958 Replies

1282 Topics

Jennifer Douglas
Jr Varsity II
Jennifer DouglasJr Varsity II
posted in Reports and Generic Inquires

Acumatica Reports - Consistent Formatting for Beginners

Things I learned as an Acumatica noob.As a brand new to Acumatica developer, the thought of creating and modifying reports was terrifying to me.  But with a deep breath and fingers crossed I made the leap and have learned so much, including to not only have more faith in myself, but also in the world that is Acumatica.  Report creation and modification doesn’t have to be scary.  Acumatica Report Designer provides everything you need to modify or create a new report.  In this post, I’ll be sharing a few tips that I learned while modifying my first report. To begin, I installed a local instance of Acumatica, along with report designer.  This allows me to work with my reports locally before publishing into either my test or production environments.  Additionally, having a local install gave me access to one very important piece used for report aesthetics and consistency, namely the TemplateReport.rpx file.   Often, I will be tasked with modifying an existing Acumatica report to meet a cer

2
kandybeatty49
Captain II
2 months ago
K
Jr Varsity II
kanupindiJr Varsity II
asked in Reports and Generic Inquires

How to display Child PO Qty for items tied to a Blanket Purchase Order

We have a generic inquiry that lists blanket purchase orders information. Unlike Blanket SOs, Blanket POs do not have a Child Orders tab- making it tricky to track child POs tied to them.  SOOrder table has QtyOnOrders field, however POOrder table does not. How can I calculate Qty on Child POs and have it display on the GI?The GI is not line specific it is only header specific. When I added POLine table, it replicates all the information which is not required.The below snapshot is one from Blanket SO Gi, we would like to display something similar for the POs. How can we achieve this?@lauraj46 - any ideas from you would be helpful as well! Thank you in advance! 

4
K
Jr Varsity II
58 minutes ago
A
Freshman I
alistairFreshman I
asked in Reports and Generic Inquires

Printing Labels to Device Hub

I have a report I created that prints barcodes.  I need to print it to the Device Hub I have running, setup for RAW MODE.  How do I print this custom report to that device hub printer?

3
darylbowman
Captain II
3 hours ago
K2a
Jr Varsity II
K2aJr Varsity II
asked in Reports and Generic Inquires

Report Designer Totals

Ok, I'm sure I'm missing something super simple. i have a report that I want to summarize open/unpaid balances for a customer where certain Sales Orders are Flagged (with a manual) Checkbox. I have the schema and all data flowing properly in the report EXCEPT when it comes to totaling the report.   I assumed that I could accomplish this with grouping, but that doesn't seem to work.In the report you can see i have 2 Sales Orders.  The report should calculate the total unpaid, but it is circling through and adding up the unpaid balance from ALL the lines on the SO.  I thought if i put the 'totals' in a group and grouped by Customer AcctCD that it would sum at that level but I"m obviously missing something:  

0
K2a
Jr Varsity II
19 hours ago
A
Freshman I
AmandabFreshman I
asked in Reports and Generic Inquires

Inventory Allocation Report

I am trying to design a GI that will show me allocations for stock items that we have on Open PO (open qty). I want to know for all open PO quantities, how much is designated/linked to a SO and how much is for “stock”. The report is functioning properly and is showing me what I want, however, I would like to have separate columns for each PO Line to show if it is meant for Stock of SO (if Line type is Goods for IN, I want the open qty to show in my column Stock Inventory...and if Line Type is Goods for SO, then I want the open qty to show in my Inventory for SO column). The logic I am using on my GI is =iif([POLine.LineType]='Goods for SO', [POLine.OpenQty],0)  and  =iif([POLine.LineType]='Goods for IN', [POLine.OpenQty],0). However, all data being returned is 0.In the example below the first line of my report should show 7 in the “Stock Inventory” column and for the 2nd line I have highlighted, you should see 5 in the “Inventory for SO” column, but for some reason the formula I am usi

3
A
Freshman I
19 hours ago
Z
Z000075
asked in Reports and Generic Inquires

Error when running Project Billing due to ProjectID Task Type

Error occurred when “RUN PROJECT BILLING” states that “An error occurred during processing of the field ProjectID value 39 Task Type is not valid.” We went into all the parameter's and we do not have a task 39.  Any solution would be appreciated as we cannot run the Project Billing    

1
BenjaminCrisman
Acumatica Employee
23 hours ago
J
Freshman II
jhouserFreshman II
asked in Reports and Generic Inquires

Duplicate lines in GI when joining SOLine and SOLineSplit

Hello, I’ve joined SOLine and SOLineSplit so I can get the PO and AP bill associated with sales orders in a GI. When I activate the join, it shows multiple lines for an invoice / order with only one line as shown below. Been wrestling with it for a while and hoping someone can assist. XML of my GI attached. Thanks in advance.      

2
J
Freshman II
23 hours ago
R
Jr Varsity II
RajginoyaJr Varsity II
asked in Reports and Generic Inquires

Operation ID not populating in the report parameter

  I want to populate  [AMMTran.OperationID] as the report parameter. I have added the parameter in the schema but nothing is showing up on the report screen.This is how I see this empty selector. 

2
R
Jr Varsity II
1 day ago
C
Freshman I
carmelinem89Freshman I
posted in Reports and Generic Inquires

How to display inventory IDs in the sequence, for example

I want the report to be printed in the following manner:Page #1 - Page break after inventory ID: 30Page break after inventory ID: 40Page 1Page  #2 Page 2 

4
BenjaminCrisman
Acumatica Employee
1 day ago
L
Freshman I
liam64Freshman I
asked in Reports and Generic Inquires

TRIMMING A FIELD IN REPORT DESIGNER (FORMULA ?)

Hi there, Is there a “Format” or “Formula” to trim a field in report designer. ie. I want to turn C-16600-O into just 16600. I do not want to physically change the naming convention inside Acumatica, i simply just want to trim the field in the report designer. Your help would be highly appreciated. Thank you

2
L
Freshman I
1 day ago
V
Freshman I
vpoulsonFreshman I
asked in Reports and Generic Inquires

Getting all the totals from a subreport on the main report

I have a subreport that gets the total amount invoiced for a sales order.  In the main report i get sales orders with an invoice in a date range and then pass in the sales order number to the subreport to get the total invoice regardless if the invoice is in the entered date range.  The subreport works correctly but in the main report it is not giving me a correct total.  It will sum all but the last value from the subreport.  I’ve tried every combination of the ProcessOrder I can think of.   Attached are the two reports.

0
V
Freshman I
1 day ago
R
Jr Varsity II
RajginoyaJr Varsity II
asked in Reports and Generic Inquires

Lot / Serial Number not populating in the report parameter?

I want to populate  [AMMTran.LotSerialNbr] as the report parameter. I have added the parameter in the schema but nothing is showing up on the report screen.This is how I see this empty selector. 

1
BenjaminCrisman
Acumatica Employee
1 day ago
S
Jr Varsity I
sspencerJr Varsity I
asked in Reports and Generic Inquires

Adding Summary Amount to widget on the dashboard

We have created the dashboard below.On the dashboard first we have have KPI Meter/Scorecard with the total for the Open Sales Orders,  Then directly below the this we have a Data Table Widget.   What we would like to do is Display the Total Amount on the top of the Data Table and eliminate the need for the KPI Meter/Scorecard.  See the Red Circle and Arrow.   Has anyone done anything like this?If we click on either widget it takes us to the GI with the specific filters and we have figured out how to put the Summary Total at the bottom of the GI.  Is there a way to put it at the top of the GI and then have it also displayed in the widget? 

1
BenjaminCrisman
Acumatica Employee
1 day ago
L
Semi-Pro II
larrylySemi-Pro II
asked in Reports and Generic Inquires

How to create a multi level axis in report designer

Hello everyone, Does any one knows how to create a multi level axis in the report designer? Report designer is adopted from excel. But the charts setting is limited. I want to create a multi level chart that grouped by month and product. Has anyone done this before? Thanks for sharing.  Excel sample: 

1
BenjaminCrisman
Acumatica Employee
1 day ago
param2022
Jr Varsity II
param2022Jr Varsity II
asked in Reports and Generic Inquires

Populate the Material selection in report based on the status of material

I have my custom report. I want to populate the material number selector based on the status and date selected in the above parameters. How can we do that any ideas ??Report screenThanks

1
BenjaminCrisman
Acumatica Employee
2 days ago
E
Freshman I
ellidacornavacaFreshman I
asked in Reports and Generic Inquires

How to Include Inventory Items with 0 Qty Produced in Report

I’ve created a report that groups Inventory Items so that I can sum Qty Produced for each item over a given time frame. The goal is to have a column for Inventory ID/Description, a Qty Produced Goal Column, and an Over and Under Column for the sum/difference where an item was over or underproduced where even if the item has not been produced, it would still show on the report. For instance, no CHOC400 had been produced, it would still appear on the report, where:Goal = 700Qty Produced = 0 Under: -2060Instead the report is only showing if the item has been produced. So, for example, an inventory ID, CHOC400 which has not been produced this month, isn’t showing at all. How can I fix this? I’ve attached a copy of the .rpx version of the report that’s saved as .doc so I could upload it.  

4
E
Freshman I
2 days ago
swittrock
Freshman I
swittrockFreshman I
asked in Reports and Generic Inquires

Formula help in a GI

I am getting a syntax error with this formula and can’t quite figure it out.=IIF ((DateDiff(interval, [ARInvoice.DueDate], Today()) >=30 and (DateDiff(interval, [ARInvoice.DueDate], Today())<60, [ARInvoice.CuryDocBal],0)I am trying to create columns of data depending on the “aging” of the invoice balance in a GI so that we can export to Excel for submission to our Auditors.  The value should either be zero or the invoice balance.I have this similar formula that works fine for current invoices:  =IIF ([ARInvoice.DueDate] >= Today(),[ARInvoice.CuryDocBal],0) Thanks!

11
swittrock
Freshman I
2 days ago
R
Freshman I
rashmikamudalinayake10Freshman I
asked in Reports and Generic Inquires

Pivot Table customization

Is it possible to make a custom pivot table instead of using the default pivot. If so, how?

0
R
Freshman I
2 days ago
J
Freshman II
jaysonFreshman II
asked in Reports and Generic Inquires

Generic Inquiry Concatenate Field Values in Group By

Hi! Is it possible to concatenate these values after group by? I want the output to be “Order Fulfillment, Customer Experience” since they just belong to a single activity.

2
D
Jr Varsity III
2 days ago
C
Varsity III
craig2Varsity III
asked in Reports and Generic Inquires

Create Action URL in Report

Hi all,I need salespeople to Approve their order after viewing the printed report version.  Is it possible to pass an Action via URL so I could create an “Approve” link in the report, much like you can do with a Parameter?Much appreciated!

2
C
Varsity III
2 days ago
B
Jr Varsity II
billmarmcJr Varsity II
asked in Reports and Generic Inquires

Nice Labels

We are in the process of converting from Macola/Synergy to Acumatica. All of our box labels and item UPC code labels have been done with Nice Labels using Zebra printers. Is there a more efficient way of converting the Nice Labels to a format that Acumatica can use or will we have to design them similar to the way Acumatica report is done, RBAR (Row by Agonizing Row). I know that there are companies out there can can convert the Nice Labels to Acumatica format such as ASGARD), but if possible, we would like to do it inhouse.

2
B
Jr Varsity II
2 days ago
S
Freshman II
swartzfegerFreshman II
asked in Reports and Generic Inquires

Older than "@Today" expression for row style (DateDiff?)

Hi all, I just wrote a row style -- =Iif ([SOOrder.CustomerRefNbr] = 'PRO FORMA' and [SOOrder.Status] = 'R','red20', 'default') If it’s a PRO FORMA on CREDIT HOLD, highlight the row red. So far so good. I would like to modify this so it’s only on credit hold pro formas that are greater than a week old. So I did something like this:=Iif ([SOOrder.CustomerRefNbr] = 'PRO FORMA' and [SOOrder.Status] = 'R' and [SOOrder.OrderDate]< @Today -7,'red20', 'default') That unfortunately gives an error. Is there a way to do dates in expressions? I see the DateDiff function but I have no idea how to work the syntax on that. Thanks!

4
S
Freshman II
3 days ago
C
charitha82
asked in Reports and Generic Inquires

How can I filter most recent Sales Order in a Generic Inquiry

I have a generic inquiry to show all sales orders lines, but now I want to modify this to retrieve only most recent sales order’s SO Lines. How can I do that?

9
Naveen Boga
Captain II
3 days ago
E
Freshman I
enriqueyFreshman I
asked in Reports and Generic Inquires

Check Alignment and Margins

Hi Community,The check print is out of alignment with our new printer. How can I adjust the alignment and margins? 

4
Naveen Boga
Captain II
5 days ago
Michael Hansen
Varsity II
Michael HansenVarsity II
asked in Reports and Generic Inquires

Generic Inquiry Unable to Pivot Table

I have a GI that refuses to output a Pivot Table. What types of things mght block pivot tables? I have full admin rights in this system. I can MAKE the pivot, but the display is empty. Here is an example:   Without any parameters etc, I have data and I know that every record has a BRANCH associated with it, so at the very least, I should get a list of branches here. I have the dates rounding to YEAR, so I should see something like 2019 <> 2020 <> 2021 <> 2022 <> 2023 across the columns too. But no matter what fields I use to make the pivot, I get this empty pivot table. Anyone know what types of things inside a GI might create a block on pivots like this? Side Panels? Groupings? Mixed Aggregates? Thanks,

14
C
Varsity II
5 days ago

Badge winners

  • Low Code | No Code Customizations or Integrations
    jininhas earned the badge Low Code | No Code Customizations or Integrations
  • Easy talker
    Kyle Vanderstoephas earned the badge Easy talker
  • Financials expert
    NuwanTharanga62has earned the badge Financials expert
  • Easy talker
    jaysonhas earned the badge Easy talker
  • Easy talker
    palboreshas earned the badge Easy talker
Show all badges
Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2023  Acumatica, Inc. All rights reserved