Skip to main content

Reports and Generic Inquires

Reports, GIs, Dashboards, Pivots

  • 2,072 Topics
  • 8,127 Replies
2072 Topics
J
Varsity II
jwarrenVarsity II
asked in Reports and Generic Inquires
Need help with an IF statement in GI

Hi, Trying to build out a GI and needing some help on an IF statement. Here’s the scenario.  If the Lot/Serial class is ‘LOTTED’, then the Lot/Serial Nbr. column should be ‘green’ for ‘LOTTED’ items where it is not empty and ‘red’ for ‘LOTTED’ items that are empty. If it is not LOTTED, then it should be ‘default’. If the Lot/Serial Class is ‘DEFAULT’, the QTY Remaining column should be ‘green’ if QTY Remaining > QTY on Hand and should be ‘red’ if QTY Remaining < QTY on Hand. If it is LOTTED, then it should be ‘default’ in this column. Here are my existing formulas in the respective fields:QTY Remaining:=IIf( [AMProdMatl.QtyReq] > [INSiteStatusSummary.QtyOnHand], 'bad', 'good' ) Lot/Serial Nbr:=IIf([AMProdItem.LotSerialNbr]= Null,'bad', 'good' )   

7875
brendan91
Semi-Pro III
1 year ago
M
Semi-Pro II
mikehoSemi-Pro II
asked in Reports and Generic Inquires
OData issue with Power BI (DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed)

We’ve been using Power BI to connect to Generic Inquiries via OData for the past year. All of the sudden in the past few days we’ve received the following error message in Power BI: Anyone know what might be causing this and how to resolve the issue?I found this article that references a similar issue, but none of our GIs have a period (.) in the name so the solve offered in the article isn’t helpful in this case.

7663
Chris Hackett
Community Manager
1 year ago
S
Jr Varsity III
sspencerJr Varsity III
asked in Reports and Generic Inquires
Report Designer - How to Add a new Grouping to report

i am trying to modify a report I want to add another layer of grouping for subtotals.  I want to add Group3.  I can modify group 1 and group 2 but cant figure out how to add a Group 3.  fairly new to Report designer 

7661
M
Jr Varsity II
1 year ago
E
Freshman II
edgarrodriguez23Freshman II
asked in Reports and Generic Inquires
Duplicate item Acumatic Report Designer

Report DesignerI have this issue with Picking Report, the first two items get duplicated when I'm running the report designer.

76019
BenjaminCrisman
Acumatica Employee
2 years ago
L
Captain II
ltussing03Captain II
asked in Reports and Generic Inquires
Audit Trail Reports and GIs

Hello,I want to write reports and/or GIs to show audit trail history.Specifically, I want to show value changes, the field name, what the value was before and the new value and the user who performed the change.I would also like to be able to report lines that were deleted.Specifically, this is for projects, but the question is applicable in general.What table and fields would I use?I see an AuditHistory DAC, but it does not have USER, BEFORE VALUE, AFTER VALUE.And the ModifiedFields entry seems like an unusable jumble of stuff.

7545
L
Captain II
2 years ago
S
Freshman I
seastman20Freshman I
asked in Reports and Generic Inquires
Audit History - Ability to print out report

The auditors would like a printout of the audit history.  We can see the changes through Audit history, but there is no way to send a report to auditors for their files.

7532
R
Freshman II
2 years ago
hsarjapur
Varsity I
hsarjapurVarsity I
asked in Reports and Generic Inquires
How to show Google maps in a wiki?

How to show Google maps in a wiki and expose that Wiki on dashboard? We can add google maps script to show up in Wiki page but would like to see if we can add that wiki into dashboard Wiki component and show on the dashboard.

7501
Irina
Community Manager
4 years ago
E
Freshman I
EdongFreshman I
asked in Reports and Generic Inquires
How to add an extra aging bucket to AR Aging Report?

Hello, How to add aging bucket to AR Aging Report. I have to add 91-120 days, 121- 150 days and 180 days over. I’m little confused on the functions used inside the logic variables in the report. Thanks and Best Regards

7492
M
Freshman I
1 year ago
Naveen Boga
Captain II
Naveen BogaCaptain II
posted in Reports and Generic Inquires
Performance Issue on the Generic Inquiry

Hello Everyone,We have created a Generic Inquiry screen and it is taking 2 minutes in production (where as I have 1256007 records) and this GI is loading very fast in my local machine as I have less number of records.Attached this GI for your reference, can anyone help me to optimize the performance with this GI, when we have huge data.  

74712
N
Freshman I
5 months ago
Laura02
Captain II
Laura02Captain II
posted in Reports and Generic Inquires
How to Mass Update Records with Generic Inquiry

Do you have a new Cash Account, and need to add the new Cash Account to a large number of Open AP Bills? Are you adding Sales Tax module, and you need to apply a new Tax Zone to Customers based on their Zip Codes? Are you changing Salespeople to different zones or products, and you need to update existing Default Salesperson for your Customers or existing Product Manager on your Inventory Items?Mass Update Records may help you!In this example, a company opened a new bank account and needs to update the Cash Account on 3000 open AP Bills. Mass Update is one way to update open bills. (Import Scenario is another way that is not covered in this post.)Add the Cash Account field that needs to be changed to the Results tab of Generic Inquiry:Add Cash Account to AP Bills InquiryOn Entry Point tab of Generic Inquiry screen, Enable Mass Record Updates.On Mass Update Fields tab of Generic Inquiry, add the field to be updated; in this case, Cash Account.Return to your newly updated Generic Inquiry

7456
Q
Freshman I
7 months ago
E
ebaker
asked in Reports and Generic Inquires
Generic Inquiry date output & formating

I need the date output in a GI to be yyyy/mm/dd for a third party integration but we use mm/dd/yyyy as a default. How can I flip this around?

7452
Naveen Boga
Captain II
2 years ago
M
Freshman I
mrivera67Freshman I
asked in Reports and Generic Inquires
Restrict access to only print approved PO

Hi, is there a way to restrict certain users to only print approved PO only? Thanks in advance. 

7449
N
Freshman II
5 months ago
M
Semi-Pro II
mikehoSemi-Pro II
asked in Reports and Generic Inquires
"Conversion failed when converting the varchar value to data type int." error

I am trying to build a GI using the INItemSite table to display the InventoryID and the MinQty designated for each warehouse. I’m trying to use the following formula to limit to the MinQty from a specific warehouse and will duplicate several times for each warehouse (grouping the results by InventoryID):=IIf([INItemSite.SiteID]='Warehouse-01',[INItemSite.MinQty],0)However, when I try to view the results I receive the following error message:Conversion failed when converting the varchar value 'Warehouse-01' to data type int. Anyone have any suggestions around this? I would imagine this error means the INItemSite.SiteID field does not actually contain the text data but is actually a numeric value assigned to that SiteID, but I can’t figure out how to fix this GI. Seems fairly straightforward.

7375
M
Semi-Pro II
1 year ago
Naveen Boga
Captain II
Naveen BogaCaptain II
asked in Reports and Generic Inquires
Can we get a duplicate records count in Generic Inquiry

Hi Everyone, In Generic Inquiry, is this possible to get the duplicate records count just like below sample SQL query. SELECT username, email, COUNT(*)FROM usersGROUP BY username, emailHAVING COUNT(*) > 1

7272
Naveen Boga
Captain II
4 years ago
U
Freshman II
uma47Freshman II
asked in Reports and Generic Inquires
Pivot tables - number formatting

Hi, I have created a pivot table for shipment data and it has lots of number fields that are presented as text, when you export to excel. Is there a way to format the number columns so, it will show left aligned in the table and will have the correct format when exported to excel?

7246
U
Freshman II
3 years ago
H
Captain II
HarshitaCaptain II
asked in Reports and Generic Inquires
How can we create variables in Report Designer?

Hello Comminuty,I would like to know how can we create variables in Acumatica Report Designer? Specially if a new datetime field is added on the screen and the user wanted to see the full format as 20 October 2023.  

7212
R
Freshman I
2 months ago
S
Freshman II
stephaniet98Freshman II
asked in Reports and Generic Inquires
Grouping Generic Inquiry by Formula

I am trying to create a generic inquiry that groups on a formula.  However I am getting the error in the following screenshot:The help file for the Generic Inquiry screen specifically states that I can add a formula so I am not sure what I am doing wrong.  Any ideas? On another note, if there is a table in Acumatica that breaks the subaccount into their segments I would much prefer to use that but I have been unable to find one.Appreciate any help that you can provide!

7187
Robert Sternberg
Captain II
1 year ago
M
Jr Varsity II
matthewbeebeJr Varsity II
asked in Reports and Generic Inquires
Business Event not firing if Generic Inquiry has a conditional

Strange issue with Business Event not firing if the underlying GI has a conditional on it.  Worked through all the troubleshooting guides I could find, and still stumped. I have a Generic Inquiry which is a simple join of the AMMTran table and the AMProdItem table:The relations are simple, just joining the shared keys of OrderType and prodOrdID:  And my conditions are straightforward, looking for Materials documents that linked to Closed production orders: And my business event is set to “Trigger by Record Change” with Raise Event set to “For Each Record”:This does not fire.  If I change my conditions on the GI to remove the status=Completed, it works fine (though it fires immediately upon issue of the materials, which is NOT what I need, as I need to do something with the Material once the Production order completes). I have tried it with “from schema” for the condition instead of the =’M’, so it’s not anything that simple. I’ve tried changing the Trigger conditions to be Record Inser

7088
BenjaminCrisman
Acumatica Employee
1 year ago
rodman
Freshman I
rodmanFreshman I
asked in Reports and Generic Inquires
Generic Inquiry HAVING SQL Clause

Does anyone have a clever way to use a Parameter in a Generic Inquiry to generate a HAVING clause in SQL right after the GROUP BY clause? It generates a WHERE clause which doesn't allow me to work with the aggregation results.I can do an on-the-fly filter, but I was hoping to use a Parameter.

7087
TimRodman
Semi-Pro III
3 years ago
Jeff96
Captain II
Jeff96Captain II
asked in Reports and Generic Inquires
Shared Filter on Generic Inquiry Permissions

I can’t figure out where the access rights to share a filter on a GI is located in the permission settings. 

70516
K
Freshman I
2 months ago
W
Freshman I
wrajendran23Freshman I
asked in Reports and Generic Inquires
Attaching PDF file into custom report

Hello there,We are developing custom report using 21 R2 release, and the requirement is to print a PDF attachment after printing the custom report.The PDF is attached as a file attachment either under STOCK ITEM or KIT Assembly.Any idea or any one has done this kind.

7003
W
Freshman I
2 years ago
PhilipW
Freshman I
PhilipWFreshman I
asked in Reports and Generic Inquires
Is there a way to correct shipment after invoice?

Is there a way I can correct / update an order after it has been invoiced? specifically for the sps edi tab for shipments. Normally I place a reference number under the SPS EDI tab for and order before it’s invoiced, however I accidentally hit prepare invoice instead of correct shipment. Now when I try to add the reference number to the order that was invoiced the “correct shipment” option is greyed out and unable to update the SPS EDI tab.

6963
PhilipW
Freshman I
1 year ago
pandachan48
Jr Varsity II
pandachan48Jr Varsity II
asked in Reports and Generic Inquires
Inquiry formula issue? - Margin

I have set the formula to count the margin, I’m not sure is the formula correct?=([Products.CuryUnitPrice]-[Products.curyUnitCost])/[Products.CuryUnitPrice]does someone know how to solve this problem?After I set the formula, it come out the error. “Divide by Zero error encountered”.  

6915
TimRodman
Semi-Pro III
3 years ago
R
Captain II
ray20Captain II
asked in Reports and Generic Inquires
How to email a GI result in Excel format like the way rpx report can do?

Hello,        I have a requirement to send a report to vendor in excel format using Email.I know this can be done by report designer and “report template”. But, right now, this report is simply wrote in GI. So I am asking is there a way to directly prepare this excel report and send by email.      I also know a way to prepare the excel report in automation by using export scenario, however, in this way, It seems I can just upload this prepared file using SFTP or FTP, while our vendor insisted receiving the report by email.      So can anybody help me?

6904
R
Captain II
2 years ago
N
Varsity I
npace29Varsity I
asked in Reports and Generic Inquires
Display of new Combo Box value for a status field in Generic Inquiry

Hello, I have added a small customization to the Vendor Status field such that it includes a new available option for selection. This was done through the Customization Project via the ‘Fields’ section of the ‘Vendors’ [AP303000] screen, where a new Combo-box value of ‘Z’ and description of ‘Pending Approval’ (see below screenshot) were added.  The respective value of ‘Pending Approval’ is available on the ‘Vendors’ [AP303000] screen, however when running the Generic Inquiry which shows the list of Vendors, there is a slight issue, whereby the Status is being shown as blank. I have some workarounds to this such as by using a Switch function to manually show the status description, although this is not “clean” as it could break in case of upgrades and it would be considered as sort of “hard-coding”, thus is not good practice.  Does anyone know whether there is perhaps some missing configuration that I need to do to make the value description show for the new Combo-box values? Thanks in

6874
N
Varsity I
2 years ago

Badge winners

Show all badges
Terms & ConditionsCookie settings