Community
Discussion Forums
Reports and Generic Inquires

Reports and Generic Inquires

Reports, GIs, Dashboards, Pivots

2,049 Topics
8,051 Replies

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

2049 Topics

Recently active

Most replies Most views Newest first

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

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.

2 years ago

rajanip50Freshman I

asked in Reports and Generic Inquires

Get all Fields from current row of Join Table

I Have joined 2 tables TSNewQCOrder and TSNewQCOrderSpecializedTest.I write the view.public PXSelectJoin<TSNewQCOrder, InnerJoin<TSNewQCOrderSpecializedTest, On<TSNewQCOrderSpecializedTest.qCOrderType, Equal<TSNewQCOrder.qCOrderType>, And<TSNewQCOrder.qCOrderNbr, Equal<TSNewQCOrderSpecializedTest.qCOrderNbr>>>>, Where<TSNewQCOrder.qCOrderNbr, IsNotNull>, OrderBy<Desc<TSNewQCOrder.createdDateTime>>> TSSOShipmentQualityOrderView; protected virtual IEnumerable tSSOShipmentQualityOrderView() { SOShipment rec = Base.Document.Current; PXSelectBase<TSNewQCOrder> cmd = new PXSelectJoin<TSNewQCOrder, InnerJoin<TSNewQCOrderSpecializedTest, On<TSNewQCOrderSpecializedTest.qCOrderType, Equal<TSNewQCOrder.qCOrderType>, And<TSNewQCOrder.qCOrderNbr, Equal<TSNewQCOrderSpecializedTest.qCOrderNbr>>>>,

2 years ago

jetzerb08Freshman I

asked in Reports and Generic Inquires

OData and Deleted Records

I've defined a GI, checked the "Show Deleted Records" box and included the "DeletedDatabaseRecord" field in the grid. Within Acumatica, the GI shows me all records, and I can see that 11 of 23 records have been deleted. But when I query the OData feed with no filter, using the same credentials as within the Acumatica UI, it returns only the 12 “live” records (those that have not been deleted). My goal is to keep my EDW in sync with Acumatica, which means I need to capture inserts, updates, and deletes.Is there some configuration I missed? How can I view deleted records via OData?

2 years ago

apretorius62Freshman I

asked in Reports and Generic Inquires

Business event not working when creating SO programmatically

HiI have the BigCommerce (BC) connector.I import Sales Orders from BC using an Order Type called EO.I have EO type setup to go ONHOLD when createdI created a Business Event of type Record Inserted on a GI to remove the hold using an Import Scenario.When I create EO on SO301000 screen by hand, the BE triggers fine. Within 3 seconds.When I create EO using BC, nothing happens. The new EO appears on the GI, meaning it pics it up, but the BE never triggers.I have seen this behavior before on other functions where a transaction that’s created programmatically DOES NOT trigger a BE.Why is that?

2 years ago

jeans16Freshman II

asked in Reports and Generic Inquires

ARM report pulling in previous years

Client has a valuation financial report where they list out in Rows the (row 1) CY Net Income (ie 2022), (row 2) last year net income (ie 2021), (row 3) year before that (ie 2020) and (row 4) 3rd prior year (ie 2019) in column1, then they have another column, column 2, which starts with the previous year (ie 2021) in row 1, then 2020 in row 2, 2019 in row 3 and 2018 in row 4. They have a row 5 that then takes the average of those 4 years to show the change from year to year.In the row1 I have defined no offset, In row2 I have defined -1 for year, in row 3 I have defined -2 for the year and in row 4 I have defined -3 for the year offset. For the current year in column 1 the numbers show correctly for the 4 years. For column 2 which would start with 2021 the column is defined with -1 in the column set and because the row for the current year has no offset it looks to the column set, so it works for row 1 but for rows 3, 4 and 5 because the offset comes from the row it is showing 2021

2 years ago

mmyersFreshman I

asked in Reports and Generic Inquires

How do I update my odata query after resetting my password?

My query was working properly until I changed my Acumatica password. Now I am receiving an error that the credentials provided do not authenticate. How do I update the credentials within the odata feature?

2 years ago

rhooper91Varsity III

asked in Reports and Generic Inquires

Ability to Default Report Version by Customer

Hello,Is there functionality to default a sales order (or any other report for that matter) version based on the customer? The base requirement is to be able to dynamically change the format of the report and versioning makes the most amount of sense without creating multiple overlays within the same report.The idea would be that “Print Sales Order” would dynamically spit out the correct version without the user having to go in the report itself and select the version and then run it. Even if this needs to be a customization I would be curious if it’s possible.Thanks in advance,

2 years ago

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.

2 years ago

TharidhiPSemi-Pro I

asked in Reports and Generic Inquires

Line Spacing Issue for Acumatica Report Designer Element

Hi, I have a requirement where one field must be printed with some amount of line spacing between them, if the field length is long and uses many lines. Is there an option in Acumatica Report Designer to increase line spacing or any other workaround to achieve space between according to the layout would be appreciated. Thank you.Line Spacing Must Increase for the Field Value Printed

2 years ago

olexandrProkopovych94Jr Varsity II

asked in Reports and Generic Inquires

Wrong time in GI because of Daylight saving time

I need to display on a custom GI the start and end log TIME(from the Appointment screen FS300200).As date-time fields are stored in UTC format.Therefore I use the next well known approach to calculate hour’s part into local time zone:Hour ( DateAdd ( [FSLog.DateTimeBegin], 'h', DateDiff ( 'h', NowUTC(), Now() ) )) But, the problem.How to manage winter/summer saving time periods?The customer is from Singapore, and Singapore doesn't have saving time period.With this Now/NowUTC approach for records from winter period a time is provided with one extra hour.

2 years ago

bhylandJr Varsity II

asked in Reports and Generic Inquires

Systems Administrator

Hi everyone! New to Acumatica here, learning GIs, copying, processing, etc. I’m on my 2nd cycle through the courses, and I need to know what the following warning means:“The correlation name 'InnerQuery_BAccountR_Customer' is specified multiple times in a FROM clause.”I have removed, to the best of my knowledge, all traces of Relations & Conditions that would trigger this.Any help would be appreciated.Thanks, everyone.

2 years ago

ray20Captain II

asked in Reports and Generic Inquires

Find out the top 10 monthly sales qty increasing/decreasing items in just 1 report.

Hello, I have a need to find out the top 10 items with the most sales qty drops comparing to last month.It could be done by sum up the figures group by item this month---------Result 1And sum up the figures group by item last month-------Result 2Then, I can do caculate in excel by comparing result 1 and result 2. I am just wondering is there any way that I can get the final result directly in Acumatica with GI or Report? Thank you in advance.

2 years ago

swhitleyFreshman I

asked in Reports and Generic Inquires

Salvage value in depreciation calculation

New to Acumatica. My question is why is my Depreciation calculation not reducing the asset cost for salvage value before calculating depreciation. The report reads asset cost = depreciable value on an asset with a declared salvage value.

2 years ago

arlineweltyFreshman I

asked in Reports and Generic Inquires

Display Customer Balance on A/R Invoice Form

Howdy all,I’m looking for support on showing some additional customer aging data on the Invoice Form (AR641000).Ideally I’d show these fields:Customer Current Balance Invoice Amount Customer Current Balance - Invoice Amount as “Revised Balance” I *think* what I want to do is add a subreport to the invoice form (AR641000) that references the Customer Documents Total filed from the AR Balance by Customer Report (ar632500). But I’m not clear how to access that exact field value:But I’m not sure what the best way to proceed is in order to recreate the field “$CustomerBal” and add it to the Invoice as a subreport.Or, should I try and do the linking within the current report so I can have “$CustomerBal” interact with the invoice total field? Would be curious to hear what others would do.Thanks for the ideas!

2 years ago

taranjitgillFreshman II

asked in Reports and Generic Inquires

How to change format of date displayed on report header

I am very new to Acumatica, I have inherited a lot of reports in which I want to update the formatting. One thing I’m stuck on is how the coding works on formatting the date. I changed the formula to this (see below). It currently reads as: I want to change the format to include a “/” or a “-” inbetween the month and Year. Another thing I want to do is to just have the last 2 digits of the year if possible so it can look like this “08-22” or at least be able to read as “08-2022”. Any help is greatly appreciated.

2 years ago

DanielStarkeVarsity I

asked in Reports and Generic Inquires

How do I make a GI open in full screen and hide the top menu?

I want to be able to put a GI up on a big screen in the office and not have the menu and headers showing. for example, if i wanted to hide all that i have scribbled on red below, and just keep the main table, is there a way?

2 years ago

podonnell11Semi-Pro II

asked in Reports and Generic Inquires

Qty on Hand as of a certain date

Hello everyone, I am in the process of creating a generic inquiry that shows an inventory valuation by date. The reports that are pre-built with Acumatica only show by financial period. The most important column is the quantity on hand (which is used to calculate the value of the inventory).Here are my joins: Most of my joins are on inventoryID or siteID.For quantity on hand I am using this:[INLocationStatus.QtyOnHand]The problem is that the quantity on hand that I am picking up is simply the latest quantity on hand, rather than the quantity on hand as of the date chosen in the parameter: Does this seem to be a conditions/parameter issue? I am in the process of testing with trial and error on the conditions and parameters. Maybe there is another table I should use for qty on hand? Thank you all for any input.

2 years ago

AlfieFreshman II

asked in Reports and Generic Inquires

Duplicate Order Types but not seen in Order Types Maintenance Screen

Hi Everyone,Good afternoon,I am having an issue regarding Sales Order Forms in Acumatica. It duplicates all the entries upon running the Sales Order Forms.Please see attached photo for your reference: Sales Order Screen :Upon Clicking of Print Sales Order : It duplicates the line item of the Sales Order Forms; Upon checking of Order Types Maintenance :Here’s the Result :But while searching on the screen of order types it gives me duplicate (Same Order Type) Maybe, this is the cause of the issue in sales order forms.May we ask on how to eliminate the duplicate order types even if it not available in Order Types Maintenance ? or Is there’s a way to correct the Sales Order Form in Report Designer? Thank you !!

2 years ago

ltussing03Captain II

asked in Reports and Generic Inquires

Report Selection criteria based on running total

Hello,I need to write a report (or GI) that allows me to report if there are more than 1 sales order in Acumatica with the same “Customer Order Nbr.”I have done similar things a million times in other report writers, but I do not know of a method to accomplish this with Acumatica’s report writer.So I need to run through the SO’s and report Acumatica sales orders and their associated “Customer Order Nbr”, but only if multiple sales orders have the same “Customer Order Nbr.”. I’ve used functions like running totals that reset on the change of a field value etc… using other reporting tools, but I don’t know how to accomplish this here.Any great ideas on Friday afternoon in AcumaticaLand!!!???Thanks!!!!

2 years ago

ely36Freshman II

asked in Reports and Generic Inquires

Restriction Coloumns in Report by specific user

Hi,We are going to have clients log into to Acumatica. In Report Inventory Valuation - IN615500 for sales team just wanna see their own stock without any information Total Cost of the items.Fyi, Report Inventory Valuation with total cost field will use to others user/user roles such as admin, purchase teams.Is it possible setting restriction total cost on these report by specific user/roles?Note : Client perhaps not any customization report for this implementation phase 1.

2 years ago

dougmcbride79Semi-Pro I

asked in Reports and Generic Inquires

Twilio: Replies to messages

We just set up the Twilio SMS Integration. We are on Acumatica 2021 R2. If Acumatica sends a SMS via Twilio, and the receipient replies, how / where do I see the reply?

2 years ago

aborisova37Semi-Pro I

asked in Reports and Generic Inquires

Restriction Groups by Project Generic Inquiry

We want to have a GI that will trigger notification via a Business event if a project is assigned to more than one active Restriction Group.Here are the table Relations:However, when the project is in two or more groups, it’s shown in the GI results with no groups.Please help to get it to work so that if there is more than one group that a project is assigned to, the GI shows all of them in the results. Thank you!

2 years ago

vivekmVarsity I

asked in Reports and Generic Inquires

How to show PDF document content on Reports

Hi Team,Is there any possibility to show PDF document content on a report.If it is possible can you suggest the best possible way to achieve it please. We are working with 21R2 - Build 21.208.0032. Thank you in advance !

2 years ago

Page 67 / 82

Badge winners

wanizaidi27has earned the badge Easy talker
stephenbologna39has earned the badge Integrations and Web Services | APIs expert
pankaj65has earned the badge Easy talker
PatrickBaconhas earned the badge Easy talker
Dipak Nilkanthhas earned the badge Financials expert