Reports and Generic Inquires
Reports, GIs, Dashboards, Pivots
- 1,282 Topics
- 4,958 Replies
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
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!
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:
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
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
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.
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
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.
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?
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:
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.
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!
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.
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!
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,
Login to the community
Social LoginLogin with your Acumatica account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.