Community
Discussion Forums
Reports and Generic Inquires

Reports and Generic Inquires

Reports, GIs, Dashboards, Pivots

2,047 Topics
8,046 Replies

When you subscribe we will email you when there is a new topic in this category

2047 Topics

Recently active

Most replies Most views Newest first

Jov29Freshman II

asked in Reports and Generic Inquires

Running Balance Quota vs Sales

Hi All, I am trying to create a report that can show what is our running balances on sales to meet our quota, i use invoices and memos as my references for the report, and group it by doc type, and on the formula for the running balances is =IIf([ARInvoice.DocType]='CRM', 10000000+SUM([ARInvoice.CuryLineTotal]), IIf([ARInvoice.DocType]='INV', ((10000000-SUM([ARInvoice.CuryLineTotal]))*(-1)), IIf([ARInvoice.DocType]='DRM', ((10000000-SUM([ARInvoice.CuryLineTotal]))*(-1)), 0)))how can i end up on the correct report for this

2 years ago

edgarrodriguez23Freshman II

asked in Reports and Generic Inquires

Report Designer- Page Count on Header.

I want to show the page count in the header, is that possible?

2 years ago

nsmith51Jr Varsity III

asked in Reports and Generic Inquires

Issue - Sales Order Default Acumatica report printing in 2 pages

Hello Everyone,When I’m printing the Sales Order default Acumatica report with Portrait mode and it is printing in 2 pages.Can you please help me to print in single page?

2 years ago

krausef77Semi-Pro I

asked in Reports and Generic Inquires

Report Designer issue with $TotalAmount not reflecting negative values

Hi All, I ran into an issue with the Order Register report specifically (tested in other reports and they act accordingly).The $TotalAmount in the Footer does not pick up Return orders as negative values.Even after modifying the Line level (Header of Group 1) Section, the values were represented as negative on the line level but are not reflected in the $TotalAmount column. (I created an If statement and multiplied these values by -1:=IIf([SOOrder.OrderType]='RC',[SOOrder.CuryOrderTotal]*-1, [SOOrder.CuryOrderTotal])(just an example of the logic behind it.)The same goes for $TotalLinesAmount and $TotalQTY.Messing around with the footer level and trying to apply the logic from above just led to the same result or completely overstated amounts.The logic works in Generic Inquiries and their Totals Aggregate functions.Am I missing something here?Did somebody encounter a similar issue before?

2 years ago

AJohnsonJr Varsity III

asked in Reports and Generic Inquires

Report Designer - Report Footer Variable Totals Issue

Hello, I’ve ran into an issue with a custom report built through the report designer where my totals in the report footer have not been totaling correctly or at all. All of my totals are in variables currently and I’ve got numerous totals to show. I noticed though that if I take the formula from the variable and throw it in just a normal text box, it sums up correctly. This is happening for all variables in my report footer for some reason.For instance, my RptTotEstFee variable here (all created in report footer):With the full value expression:=iif([@InclDeduction]=1, sum([OpenFileReport.OrigEstimate])-sum([OpenFileReport.OrigDiscAmt]),sum([OpenFileReport.OrigEstimate]))I took that same formula and threw it in a TextBox instead of using a variable (tough to show it, but the entire formula is in that text box), also took the CashRcvd variable and did the same thing:Then when running the report, we are shown the first record of the data’s value on the variable and the actual total for th

2 years ago

adalviJr Varsity II

asked in Reports and Generic Inquires

Unable to use Date Diff on SOOrder.RequestDate and AMProdItem.EndDate

I am trying to find date variance between SO Request Date and AM prod Item End date. This is the formula I have coded: =DateDiff( d,[SOOrder.RequestDate] , [AMProdItem.EndDate] )I get the following error : Any help is appreciated.

2 years ago

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.

2 years ago

adalviJr Varsity II

asked in Reports and Generic Inquires

GI: How can I get the Website that belongs to a contact?

I have built a query in which I have multiple associations with the contact tables to get required information. Contact Table is used to find details for a UDF contact field. Owner Contact table is used to get opportunity owner information.Builder contact table is joined with SO Order to get SO contact information.For Builder contact (actual contact on SO), I am able to get Email, Full Name, ContactID_Description but website is blank. When I check the contact, it has website but not coming up on GI. Another thing I noticed was the Phone 1 is also not coming for most of them, only 2-3 records show up whereas it is present. I am trying to use Contact.Website field. Would really appreciate if some one has an answer to this. -Ameya

2 years ago

paulsweeney34Freshman II

asked in Reports and Generic Inquires

Customizing FA643000 Report

I am looking to modify the FA643000 report.In the stock report I see a table called FAProjectedGLTran.What is the source of this data?Maybe I am missing something but I can find no reference to this table other than in the report builder.

2 years ago

woodyg84Jr Varsity III

asked in Reports and Generic Inquires

Report Designer, CRLead, ownerID is Null

Version 22.109.0023, was trying to print Lead from report designer by using CRLead, but seems that OwnerID, OwnerID_Descirption are not loading owner data properly, so where I should read Owner field then?

2 years ago

ray20Captain II

asked in Reports and Generic Inquires

Conditionally Style in report designer

Hello, As we know, we are having conditionally style in Genric inquiry, like hightlight color. Is there a way to use this similiar function in report designer. Like, can I hightlight or show different background color for the below wgt=0 lines?

2 years ago

krausef77Semi-Pro I

asked in Reports and Generic Inquires

Pivot Table does not recognize Negative Values as being negative

Hi All,We recently fixed a data issue for one of our clients and we need to adjust some reporting on the Dashboards.Basically, every other widget on a Dashboard recognizes negative values that are calculated as negative based on below Formula: Yet when using this Data column from the Inquiry in the Pivot Table, it treats it as positive and adds to the total rather to substract from it.What do I need to do that these values are recognized as negative by the Pivot Table?It’s a very basic Table: Thank you!

2 years ago

jjflores5

asked in Reports and Generic Inquires

Blank check remittance PDF

Everything was working fine this morning but we installed additional modules on Acumatica and now when we try to print out a check we just get a blank check remittance document. Do setting change with the installation of other modules?

2 years ago

brucewarrell96Jr Varsity III

asked in Reports and Generic Inquires

Cannot get INLocation.LocationCD to be a lookup parameter on a report

The report only has one table, INLocation. I want a parameter linked to LocationCD to be a lookup. I cannot get that to work. Here is my setup: What are the rules for adding a lookup parameter to a report?

2 years ago

dgrossJr Varsity II

asked in Reports and Generic Inquires

How do create a relation that will join only one of several rows?

I want to join ARInvoice to FSBillHistory. Sometimes I get two returns for one ARInvoice RefNbr, depending on if there are two appointments billed to the one invoice.There must be a way to use relations to grab only one of the two (or possibly more than two) returns.I have tried using First([X]) = [X] , Max([X]) = ([X]) (can’t because of aggregate). There must be a way to relate the join to provide only one return per match. Can anyone help?? -David

2 years ago

suemackeown13Jr Varsity III

asked in Reports and Generic Inquires

The @me condition to restrict user visibility is no longer working correctly on PO generic inquiry

Prior to their recent upgrade to 2022R1, our client was able to restict the visibilty of records by department and by user using the @me condition in the PO genreic inquiry. By doing this, only those purchase orders created by the user became visible to the user. Now after their recent upgrade the user is able to see all records for the department.The linking is as follows:And the condition is set as:The @me condition is now not working.

2 years ago

TimRodmanSemi-Pro III

asked in Reports and Generic Inquires

Why is OData $select parameter so slow?

I’m wondering if anyone at Acumatica ( especially @vpanchenko ) has some insight on this.I’ve noticed this behavior before, but this is the first time that I got out my stopwatch to time it and verify.I’m on an Acumatica site, pulling data from a Generic Inquiry over Acumatica OData, using Power BI Desktop. The Generic Inquiry is a Generic Inquiry of AR Invoice Lines, pulling from PX.Objects.AR.ARTran and PX.Objects.AR.ARInvoice. The build is 21.209.0034. I tried two different methods of accessing the data: First MethodConnect to the Acumatica OData URL, then browse to the Generic Inquiry within the Power BI user interface, then remove all columns except DocType, RefNbr, and LineNbr using the Power BI user interface. The end result is three columns and this is the Power BI code that gets generated.let Source = OData.Feed("https://mycompany.acumatica.com/odata/production", null, [Implementation="2.0"]), #"PBI-ARInvoiceLines_table" = Source{[Name="PBI-ARInvoiceLines",Signature="tab

2 years ago

jwarrenVarsity II

asked in Reports and Generic Inquires

Joining Employee table with Opportunity Table?

My team is trying to create a report for budget to actuals by salesperson. We have created an attribute associated with the employee class called Sales Budget and entered in the amounts for each of our salespeople. Each of our salespeople are Owners of their respective Opportunities. We are attempting to join the EPEmployee/CREmployee table with the CROpportunity table in order to create a GI that shows Salesperson, Budget and then sums their actual sales for the period. Has anyone been able to join the employee object with the opportunity object through the Owner field (or otherwise)? Any ideas would be appreciated.

2 years ago

mikehoSemi-Pro II

asked in Reports and Generic Inquires

Inventory Transaction History screen does not show drop ship history

Am I correct in understanding that the Inventory Transaction History screen (IN405000) does not include any history related to drop shipments? From what I can tell Invoices (from sales orders) and Receipts from drop ship POs are not showing up on this screen. Aren’t those part of the transaction history of those inventory items?

2 years ago

hayleehicksVarsity I

asked in Reports and Generic Inquires

Is there a way to show line detail data (no grouping) yet pull the min (first) shipment on an orders data only?

I’ve tinkered with the groupings and aggregates for a bit and haven’t come up with a way to do this and wanted to check here first before going the SQL view route. I have an inquiry that is showing shipment line detail per order. I only want to see the data for the sales order’s FIRST shipment though. How can I accomplish this without grouping by order nbr and doing a min on the shipment nbr? I still want to see the line detail and the grouping then takes this option away.Thanks!

2 years ago

jwestermann17Jr Varsity III

asked in Reports and Generic Inquires

Send E-Mails if customer did not order within a certain time

Hello.We have a use case where we would like to send an automated email for example when a customer has not placed a new order for 90 days. We couldn't find a field in Acumatica with the customer's last order date, so a GI doesn't seem to work. Is there a solution to this without programming it?

2 years ago

peterpino91Freshman I

posted in Reports and Generic Inquires

Dashboards 2022R1

If you want to get an updated dashboard, go to the following link: https://portal.acumatica.com/(W(3))/Main?ScreenId=SV000000&PageID=4047ebbb-3f9b-489d-b898-fc3fe9954d6a

2 years ago

mwong11Jr Varsity II

asked in Reports and Generic Inquires

Unable to save native report - Encounter error "Updating the 'UserReport' record failed because data in some field is too long

Hi, I modified a report but unable to save it to server. I can preview it but when I try to save to server, I get the following not helpful error. It does not tell me which field is too long. The report is AR641000. I was able to save the report but after some changes, it started showing the error. The error doesn’t tell me which field is causing the error.Anyone know how to narrow down the issue and resolve it?Report designer version is 2021.115Mark

2 years ago

Neil CantralJr Varsity I

posted in Reports and Generic Inquires

Query Audit History for Specific Modified Field

I’m investigating the impact of a customization unintentionally modifying fields that it wasn’t supposed to in a previous version, 2018 R1, and if it is still occurring in our current version, 2019 R2. Luckily I have quite a bit of audit history to refer to. I was able to to use this query from the Acumatica Developers blog to pull data and then parse it out into something more readable, but I’m unsure of how to validate my results to ensure that I pulled every occurrence of the modified field.I’m specifically looking for every instance where the SOLine unit price was modified by a particular screenID. Would the following query be sufficient in returning all changes to the unit price? Is there a better way to find what I’m looking for?SELECT CompanyID, BatchID, ChangeID, ScreenID, ( SELECT TOP 1 username FROM Users u WHERE PKID = UserID) as Username, ChangeDate, Operation, REPLACE(CAST(CAST(CombinedKey AS VARCHAR(MAX)) AS VARBINARY(MA

2 years ago

CalvinJr Varsity III

asked in Reports and Generic Inquires

How to Get a Value to Show Only Once In Report Designer

Hi everyone,I would like to know what would be the best way to isolate a value that appear in all the lines. I am working on a freight cost report to have SOPackageDetail in its relation. When I add the table, my DocAmt and FreightCost repeat itself based on the number of line in SOPackage,LineNbr. I try to use below formula to filter out but not successful:=IIF([SOPackageDetail.LineNbr]<>first(SOPackageDetail.LineNbr),0,[ARInvoice.OrigDocAmt])=IIF([SOPackageDetail.LineNbr]<>Last(SOPackageDetail.LineNbr),0,[ARInvoice.OrigDocAmt])=IIF([SOPackageDetail.LineNbr]<>MAX(SOPackageDetail.LineNbr),0,[ARInvoice.OrigDocAmt])These formula work with most cases but there are shipments that does not start with line 1 and value could not be shown. See below screen shot for reference: I have attached the file for reference. Hopefully someone would be provide insights or directions on how this can be done. Kind regards,Calvin

2 years ago

Page 66 / 82

Badge winners

pankaj65has earned the badge Easy talker
PatrickBaconhas earned the badge Easy talker
Dipak Nilkanthhas earned the badge Financials expert
kkraushas earned the badge Easy talker
ranjithduraisamy72has earned the badge Easy talker

Show all badges

Terms & Conditions Cookie settings

Sign up

Already have an account? Login

Social Login

Login to the community

Social Login

Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.

Enter your e-mail address

Back to overview

Scanning file for viruses.

Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.

This file cannot be downloaded

Sorry, our virus scanner detected that this file isn't safe to download.