Skip to main content

Reports and Generic Inquires

Reports, GIs, Dashboards, Pivots

  • 2,365 Topics
  • 9,462 Replies
2365 Topics
hstone
Freshman II
hstoneFreshman II
asked in Reports and Generic Inquires
Generic Inquiry Separate Lines Items due to Different Operation ID

Hi All, I am trying to get my generic inquiry to do two things that I am not understanding… Grouping the items from the bom into a grouping in some way so that it doesn't look like i need the same weldment 36 times Also, this is what it is generating when the BOM i am working with is It seems to be changing the work center on the second operation and is doubling how many times it is showing it. Any suggestions are helpful at this point or if more screenshots are needed please let me know!

214
hstone
Freshman II
R
Varsity I
rashmikamudalinayake10Varsity I
asked in Reports and Generic Inquires
Is it possible to turn number format to letter format in a report

Is it possible to turn number format to letter format in a report E.G: 100 I need like this → One Hundred

706
A
Jr Varsity III
B
Jr Varsity II
brandontfrankJr Varsity II
asked in Reports and Generic Inquires
Pulling data from PREmployeeAttribute in a GI

Hello. I’m trying to create a Generic Inquiry that pulls information from the PREmployeeAttribute screen which resides under Employee Payroll Settings > Tax Settings. I have the GI created and linked with the PREmployeeAttribute table but I’m not sure how to specify which ROW and COLUMN that GI pulls. For example - when I choose Object: PREmployeeAttribute > Data Field: value it will return the social security number. I’m guessing because that’s the first value on the table. But I want to add another column / row in the GI that pulls the ‘value’ from the Ethnicity row.

2116
Chris Hackett
Community Manager
E
Jr Varsity III
eucciferri35Jr Varsity III
asked in Reports and Generic Inquires
Formula not pulling in the correct values in a generic inquiry

So I was calculating the % completion on a project in a generic inquiry by dividing the actual costs to date by the Revised estimated costs on a generic inquiry. After some help from the community I was finally able to get the formula to not error out. The ending calculation was not correct so I took out the division piece and had it show me just the top number and just the bottom number but those are not pulling correctly. I used the same fields that are existing on other lines the generic inquiry. I’m using PMBudget.Type= ‘E’ and the PMBudget.ActualAmount for the cost to date PMBudget.Type =’E’ and the PMBudget.RevisedAmount for the Revised Estimated costs. If I just pull those lines that already exist Actual Cost to date and Revised Budget amount and setup new lines copying the same formula. I get different amounts. Here is the results the column and the test column next to it have the exact same formula I copied and pasted but they come out with different results? Original Cost to

296
E
Jr Varsity III
K
Freshman I
kkrausFreshman I
asked in Reports and Generic Inquires
How to Parse Out a Email from a Contact Id?

I’m trying to get a contact email from a Sales Order to a email, however, this is bringing in the Contact ID and not the email. I’ve tried to get the email in the box, but only the ID is able to add an attribute. IS there a way to convert the ID into a Email.

426
bwhite49
Pro III
chrislower
Jr Varsity III
chrislowerJr Varsity III
asked in Reports and Generic Inquires
Dashboard: Inquiry "Normal" Levels

Thanks everyone for the help recently on creating some new Dashboards, learning lots! A new issue/opportunity that has popped up: I am trying to create a meter widget that shows our sales reps their Margin dollars compared to their Margin Quota. The Margin Quota is stored in an attribute in the employee table. We have the GI already focused in on the individual employee but not sure how to have it pull in the Employee Quota attribute for that particular employee. Here is the attribute: Thanks

12015
A
Freshman I
J
Semi-Pro II
jzhuSemi-Pro II
asked in Reports and Generic Inquires
Sub Inquiry Error: Object reference not set to an instance of an object

Hi! I am merging two Generic Inquiries together to achieve the following: If the item on shipment is a kit item, display the component items of that kit, and the vendor inventory item of that component item. If the item on shipments is not a kit, then display it as is. Issue: While everything is pulling in and displaying correctly. I am unable to drill into the item if it is a kit, and unable to drill into its related component items. When I click on them, I would expect it to take me to the stock item screen, however, it gives me the error: Object reference not set to an instance of an object. If the item on shipment is not a kit item, I am able to click on the item and it does take me to the stock item screen. Any suggestions on what maybe causing this issue and where I should look at? Attached below Shipment Detail is the main GI.

90
J
Semi-Pro II
E
Jr Varsity III
eucciferri35Jr Varsity III
asked in Reports and Generic Inquires
Generic Inquiry iff statement that is giving me an invalid string constant error

I am trying to enter a formula into a generic inquiry that gives me a % of completion based on: cost to date/estimated budgeted costs * 100 When I started I kept getting a div/0 error so I wanted to write the formula as follows if the cost to date or the estimated budgeted cost was less than or equal to zero then column would return a value of 0 otherwise it would return the value of the calculation. I am just learning the syntax of more complex if then statements. I have tried everything and moved from syntax errors to index out of bounds. Here is my formula that is now generating a invalid string constant error. =iif([PMBudget.Type] = ‘E’,iif(([PMBudget.CuryActualAmount]<=0) or ([PMBudget.Revisedamount]<=0) ,0,([PMBudget.CuryActualAmount]/[PMBudget.Revisedamount]),0) It seems like it should be a fairly simple formula but it just not working. I’m missing something.

406
E
Jr Varsity III
L
Freshman I
LaurenceGiraud36Freshman I
asked in Reports and Generic Inquires
Combine Parameters in GI

we currently work on creating a GI and have the following question. It has the VendorCD as one of the filter parameters. For technical reasons (bug in acumatica when opening the GI via another screen with parameters), we need to define a second hidden filter field that takes the internal integral BACcountID of the vendor. When opening the GI from another screen the BAccountID will be passed (and not the VendorCD) and the results in the GI are all correct. However it looks confusing for the user as the regular VendorCD filter remains empty. Is there is a way to combine two GI parameter fields by some formula? Thanks a lot!

619
K
Freshman I
toonsix
Freshman II
toonsixFreshman II
asked in Reports and Generic Inquires
Having CoPilot in the help section of eg. the Generic Inquiry Editor

Hi: The dashboard Production Manager 2024R2 has a Work Center Queue widget showing the Quantity to Produce. The Quantity to Produce column is the quantity to produce for production orders planned, released, and in process. I expected it to be (quantity to produce – quantity completed – quantity in process = remaining quantity to produce). However there is no such column in the GI. The solution could be to have CoPilot that answers my question “ How can I see the remaining quantity in the GI?” See more details in the attached document. Thanks for your help Toon Six ToonSIx@intercs.com

271
Irina
Community Manager
B
Freshman II
bodiecFreshman II
asked in Reports and Generic Inquires
Display in a GI Transactions Made On Overtime

I would like to be able to keep track of quantities that were produced during Overtime Hours. My initial thought was to have a GI for move transaction history with a checkbox that would check if it was made during these overtime hours. This has proven to be quite the task and I just can’t get the correct transactions checked. I then thought I could create a Work Calendar and pull it in as a Data Source to compare with the times that the transactions occurred at. For this I had to create a GI that pulled the time information from the work calendar which made it display as a formula when it was used as a data source. However, I get an error when viewing the inquiry that these items “can’t be bound” (whatever that means). My new thought would be to create a business event that would trigger when a move transaction happened during Overtime Hours and it would automatically update an Attribute within the produced item displaying OT. This way I could filter by the attribute. However this woul

303
lauraj46
Captain II
N
Freshman I
nikki7Freshman I
asked in Reports and Generic Inquires
Conversion error

I am trying to retrieve the tax ID number from the vendor table. I see the field I need, but it comes up blank. All I need is the vendor and tax ID to show up in this query, and all I can get is the Vendor ID and name to show. I tried linking the location table, but I get the following error: conversion failed when converting the nvarchar value ‘Main” to data type integer. I feel like this should be a simple query, but I am obviously missing something.

92
N
Freshman I
plambert
Jr Varsity II
plambertJr Varsity II
asked in Reports and Generic Inquires
New reports throw 'Root Element is missing' after being published to Acumatica

I am running into an issue when attempting to publish a new report I have created from scratch to my Acumatica instance. This is occurring after our upgrade to 24R2 Unlike the other related article, How to resolve the 'Root element is missing' error on opening a report in Acumatica Report Designer | Community, this error is happening in Acumatica and not the report designer itself. I know my report designer is working correctly because I can edit and save-to-server existing reports without issue.The issue only happens when I try to use a new site mapping. I am able to connect the report designer to Acumatica. I can save it to the server. I have updated the Site Map screen to create a new screen for it - IN615300 since it is an inventory status report. I have set permissions to allow the appropriate people to access the report. Yet, when anyone goes to view the report, we all see: with a trace of: Is there some additional configuration I need for new reports? Are there restrictions to w

202
plambert
Jr Varsity II
S
Freshman I
Sydneysturtevant
asked in Reports and Generic Inquires
New Report not Populating on SIte - how to fix

I recently downloaded the Report Designer. I copied GL.63.30.00 from the server and modified it and then saved to the server as GL.63.30.02 then I went to the site map and added a line for the new report. The problem I get is, the report does not populate on the site. It also doesn't generate when I try typing it into the link (https://globalwide.acumatica.com/(W(29))/Main?ScreenId=GL633002). When I copy the 002 URL into an existing line, it says “You do not have enough rights”. Also, note, when I reopen the report designer and “open from server” and “load reports lists”, my prior saved report generates. So I believe it is in the server OK. I have a feeling I need certain user roles to fix this. Any recommendations?

7213
S
Freshman I
plambert
Jr Varsity II
plambertJr Varsity II
posted in Reports and Generic Inquires
Two column or multi-column layout in the Report Designer, two approaches

I recently had the task of creating a report with a two-column layout and was surprised at the lack of guidance available, so I am posting my results for the next person to find more easily. Hiding alternating rows formula: The key to creating multiple columns pull more than one record of data into a section and then to toggle the visibility of that section by a formula that uses the modulus (% operator) of the row number. First create a variable for the detail section, $RowNbr, with a value of =$RowNbr + 1. If you have groupings for your data, then also set that $RowNbr variable in the parent group to have a value of 0, which will reset the counter in each grouping. Then for the VisibleExpr of that detail section, set the value to =$RowNbr % 2 = 1 to look something like this: Record 1 Record 2 (visible) $Record 2 Record 3 (hidden) Record 3 Record 4 (visible) Record 4 Record 5 (hidden) … Displaying multiple records in one section with NEXT(): One approach to display multiple records in

913
plambert
Jr Varsity II
B
Freshman II
bodiecFreshman II
asked in Reports and Generic Inquires
Using a Calendar as a GI Source

Hi, I recently was working on a way to pull productivity data from Acumatica. Specifically during Overtime shifts. Display Only "Time" in a GI, not Date. | Community However, this became quite the rabbit hole and I tried to find another direction. So I wanted to start a new question and see if anyone has any experience with this. I noticed the Work Calendar screen in Acumatica, and hoped it might help me. So I created a Work Calendar Schedule for Overtime hours like this: I was hoping I could pull this into my Move transaction summary GI so that I could maybe have a checkbox check if the transaction was done during the Overtime Calendar Hours. Or even if I could display the Calendar ID if the transaction happened within that calendar. However, I am struggling to get this information to pull into my GI correctly, I started another GI to just get the times to display, however it gives me this random date and does not help me in this regard. Does anyone have any Ideas on this? Or am I rea

495
B
Freshman II
H
Semi-Pro II
HarrySemi-Pro II
asked in Reports and Generic Inquires
How to print correct page numbers in report?

Hello all, How can I print the page numbers in a correct way? I now there’s an option under the properties section - Print on Every Page but I’m unable to find that option in report designer. FYI, there are no joins/tables being used in here as it is just a simple form.

347
WillH
Varsity II
S
Jr Varsity III
sspencerJr Varsity III
asked in Reports and Generic Inquires
Display Day of Week in GI

I would like to display the Day of the week in a GI, Either display, Sunday, Monday, Tuesday, etc… or display, 1, 2, 3, Where 1 is equal the first day of the week etc… So for Document Date, I would like to see Document Date Document Day 10/6/2023 Monday

1583
B
Freshman II
J
Varsity III
jwarrenVarsity III
asked in Reports and Generic Inquires
Joining tables through text/description field?

I am building a GI that would link service orders with sales order. In this case, the service order is not created from the sales order, so they are not inherently linked. Instead, the sales order number is entered manually in the description of the service order. So I am looking to join the service order and sales order tables through the service order description text field. How would I do this join? The join above is not getting me any results.

161
BenjaminCrisman
Acumatica Employee
H
Semi-Pro II
HarrySemi-Pro II
asked in Reports and Generic Inquires
What expression to insert to display the company logo in report?

Hello all, I am not able to figure out the expression to insert in report desginer to display the company’s logo in report.

523
A
Jr Varsity I
D
dashcraft0329
asked in Reports and Generic Inquires
Purchase Accrual Details - help reconciling

I have been hired as an accounting consultant to clean up several accounts and I am brand new to Acumatica. One mystery is the Purchase Accrual Balance by Periods Report. There are issues that are almost 5 years old. I see purchase orders that say that they aren’t received, but the PO has a receipt. I also see PO’s that show that it hasn’t been billed, but I see a bill with that PO number in the system so I’m at a loss on how to proceed. Does anyone have any ideas on how to proceed?

130
D
J
Semi-Pro II
jzhuSemi-Pro II
asked in Reports and Generic Inquires
Using two GIs as data source

Hello! I have created two generic inquiries to be pulled into a main one. Numbers on the subqueries are correct, however the numbers are being pulled into the Main GI incorrectly. Any ideas are appreciated! I have tried changing the join type for some tables and changing aggregated functions but has had no luck. The goal is for the GI to display amount applied to invoice, payment amount, amount applied to SO, $ value of shippable items, and $ value of non-shippable items, all grouped by SO. This is the main GI - SOByLineItem and PaymentGi are two separate GIs used as sources. These are the fields being pulled from the other GIs. This is the payment GI This is the SOByLineItem Gi

692
lauraj46
Captain II
L
Freshman I
lbrown57Freshman I
asked in Reports and Generic Inquires
Usage Current vs Prior Generic Inquiry/Pivot Table

Hello, I am trying to create a GI that shows inventory usage (sales and issues) for a pivot table that will display the current 12 months vs prior past 12 months. My last activity link does not seem to be displaying data for the last activity period. I am also trying to create a formula to group periods by current and prior which I can use as columns on a Pivot Table for qty totals. Something like switch(month(today()),'1',month(today()-1),'1') then when it gets far enough back switch(month(today()-12,'2',month,today()-13,'2') but I am unsure of how to apply this in the GI. Can anyone provide help on this? GI attached. thank you!

261
lauraj46
Captain II
E
Freshman I
ElvinTFreshman I
asked in Reports and Generic Inquires
Field/Parameter issue

I cannot seem to get this parameter to show up when trying to run this report. The field does have values when running the inquiry through the GI. I have the correct data type selected and the joins are correct. It’s been driving me crazy. Screenshot 162720 - Schema Builder on report designer Screenshot 162748 - The report I’m trying to add the parameter on. Screenshot 162817 - Results of clicking “source data” through DAC schema browser for “FSContractPeriod” Screenshot 163019 - FS-ServiceContractDetails inquiry results

494
BenjaminCrisman
Acumatica Employee
T
Varsity II
tahayabaliVarsity II
asked in Reports and Generic Inquires
Sales and Profitability Report Group by Parameter

Hi All, I have a bit complex task assigned to me. I'm working on customizing a report in Acumatica Report Designer Sales Profitability by Customer Class report. I want to add a GroupBy parameter that allows grouping the data by Weekly, Monthly, Quarterly, Yearly, or 5-Year periods. Like if Select the Quarterly it should the prior 3 months data and it should be visible inside report too. Any Idea on how to this task? Any help would be great. Thanks

383
bwhite49
Pro III

Go to

Discussion Forums
Financials
Distribution
Manufacturing
Construction
CRM
Field Service
Retail
Payroll
Professional Services
Low Code | No Code Customizations or Integrations
Develop Customizations
Develop Integrations with Web Services | APIs
Other Developer Topics
ISV Solutions
Configuration and Installation
Reports and Generic Inquires
Import and Export Scenarios
System Health and Performance
Community site discussions
Community Introductions
Everything Else
Technology Partners and Customers
News & Announcements

Tags

Most helpful members this week

Badge winners

Show all badges
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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 — 2024  Acumatica, Inc. All rights reserved