Reports and Generic Inquires

I feel like I know nothing about SQL even after spending 10 plus years using it on other systems when it comes to trying to use Generic Inquiries to create reports. Which I have to do because Acumatica does not have the ability to write SQL directly against the database and produce results (This is a massive feature that I can’t believe is missing and hope 2025R2 addresses it). I need to create a Generic Inquiry that looks at Customer records takes a parameter for CreatedDateTime FROM and CreatedDateTime TO. All I need to do is look at Customers whose CreatedDateTime field falls between those two parameters and run a simply COUNT function to see how many customers were created between those two dates. This seems literally impossible to do via Generic Inquiry. In SQL all I would write is SELECT COUNT(*) FROM CustomersWHERE CreatedDateTime>=1/1/2014 AND CreatedDateTime<=1/31/2014Though if you look at the database you would actually need to query BAccount table to get this CreatedDa

So i created this report. I'm grouping the customers first and ARtran.RefNbr. But it is not working, how can i fix it.This is the relationIts not working. 

First, I tried to eliminate duplication of lines on multi-package shipments by moving the ShipLine fields to a new Group Section as suggested. When that didn’t work, I undid the changes and saved. But now I have all of this space although the height is still 0.16667in.Even publishing the previous version .rpx file to the server doesn’t eliminate the space.Any help out there?

I need to customize the Report definition of Balance Sheeti.e. Adding custom texts , Changing to custom header Is this possible? and How ?

Hello all, I have a generic inquiry created that is supposed to calculate the quote to order conversion rate. The formula we are using is (Number of SO orders with QT type as an original order type) / (Number of QT order type in the system). This is the result: All three fields are calculated fields, using Sum() directly in the formula. Since I am using aggregate function, the GI requires a group by on Order Type. Else it would throw an error. The calculations are all correct by order type, but I don’t want it to be by order type. Using the screenshot above, I want the GI to only have one line, where 2 / 1123 = 0.001809Any idea on how this can be accomplished? Thanks all! 

I am creating a GI and need to pull for a Date, however the field i need to use is a DATETIME field.  I feel like it should be easy to convert it to a DATE field only but it doesn’t seem to be.  Any hints?  I’ve tried every scenario that i can think of and google...I’ve added things to the Data Field, Schema Field, Style etc.  i can do a LEFT(Start Date,11) but then when i try to add a condition that it pulls @Today, nothing will show.  I am assuming it’s b/c of the timestamp attached to the Date.  

We’re experiencing a consistent issue where printing documents screens that typically shows the actual document (invoice, statement, remittance, payment register) are sometimes routing the user to a “Process Report” screen and requires manually re-entering parameters (document type, reference number) and clicking Run Report.Same user sometimes gets it right, sometimes gets this screen. 

Building a GI where I need to get the most recent “Effective Date” for all inventory items that are a “STAFF” price code. The ways I’ve seen to get the most recent record in GI’s won’t work here because the effective date only lives on the ARPriceWorksheet table and not on the ARPriceWorksheetDetails table where the inventory item, price code, and pending price are. Thanks for the help!

I’ve imported HTML descriptions, but they are not displaying correctly in a report. They are displaying correctly on the stock items screen - just not the report where it is needed.If I go to the stock items screen and make one small change, Acumatica will rewrite the HTML and it will then display correctly in a report. Anybody have any ideas for updating these descriptions so that Acumatica will rewrite the description HTML. Should I put a ticket in for a SQL script? Will that resolve the issue? Imported HTML Imported Display Report DisplayDisplay After adding a minor change to the description - Acumatica rebuilt the HTML 

Trying to get a business event to trigger once a week, however, the process doesn’t seem to be working. The Automation Schedule is running, but the business event itself is not. Any troubleshooting ideas?The Business EventThe Report Hasn’t RunThe Automation ScheduleThe Schedule Has Run 

Hello!I have a Parameter set up as @SiteID. I am attempting to make a cell on my report designer only visible if @SiteID parameter is null. Can this be done?    Maybe to explain a bit further. At the top; I’d like this to show up only when @SiteID is usedI’d then like the “warehouse” and “Waukegan” below it on the right column to only show when @SiteID IsNull I’ve looked online and done a bunch of AI chats and i only seem to error out every iteration i use. 

I am trying to create a GI that links the project quote to the project (to compare values).  I’m getting this error when I run it.  I have tried two different ways of linking the two tables: I’ve attached my GI.  Any ideas what is the best way to link these two tables?

exception has been thrown by the target of an invocationunable to cast object of type 'System.Byte[][]' to type 'PX.Reports.Web.WebReport'

Hey! We’re seeing a weird issue with one of our invoice email templates.If we open the PDF preview and click Send, everything works and the email draft opens as expected. But if we’re on the Invoices screen (AR.30.10.00) and use the Email action, it fails every time with this error (Trace-Log):at PX.Data.PXViewCollection.get_Item(String key) in C:\build\code_repo\NetTools\PX.Data\Graph\Collection.cs:line 1489 at PX.Data.Wiki.Parser.ParseContext.SetForeachIterator(String viewName, IDictionary2 parameters, String sortby) in C:\build\code_repo\NetTools\PX.Data\Wiki\Parser\Output\ParseContext.cs:line 123 at PX.Data.Wiki.Parser.ForeachTagProcessor.PX.Data.Wiki.Parser.PXHtmlParser.TagProcessor.Process(String tagName, String content, List1 attributes, WikiArticle result, PXWikiParserContext settings) in C:\build\code_repo\NetTools\PX.Data\Wiki\Parser\BlockParsers\XmlTags\ForeachTagProcessor.cs:line 115 at PX.Data.Wiki.Parser.PXHtmlParser.DoParse(ParseContext context, WikiArticle result) in C:

I’m trying to track who is set to get emails from our templates on various reports.Use-Case: “User A” exits the company, remove them from all outbound emails from all templates.I’d love to make a Generic Inquiry that shows me every report with every template saved for that report and then the TO and CC fields for that template. Which table has this data? Thanks!

Hi.         I would like to include additional levels to the original Dunning letter but I’m unsure of the syntax in report designer. Initially, I would like four levels. Also, include a column for the late fees and interest for each document and a total for the late fees and interest.

Hello,I am trying to create a report in Acumatica Report Designer where I need to first group records from one table, and only after the grouping, use the result to join with another table.Is it possible to implement this in Report Designer? Does Report Designer support virtual tables or nested tables that would allow such a workflow?Any guidance or examples would be greatly appreciated.Thank you!

Hi guys, I created a report showing the invoices and credit memos. There is a total. I need the invoices to add and the credit memos to be subtracted.How do I show the values as negative in a report designer?

I create a new report. And i need set visible group header by some condition When i set =$LaborReport_RowsCount>0  in visibleExpr this headers was not visible. But i have rows in Labor Report.

When add table in schema builder have this error. I reopen report designer and I have this problem again.  

I have field Description or other and i need wrap text with label for this field. How I can do this? For example i have this structure And in report my field description stuck other field, but label description not. 

I set vendor access by user. I want to filter the data by the vendor or employee when running the report in Report Designer. I remember that in customer access, I only needed to link another table to the Customer table, and the data was filtered automatically. But for vendor access, I don’t know how to make it work. Please guide me

I was having an issue with getting my new saved report getting into the site map.  At the time of writing this report I was able to get it to work by “Open report from server - load report lists” and saving it as a new report.I was not able to get it to show up from editing the saved report and saving it to the server.Problem solved, has anyone else had this issue?

I created GI that pulls all appointments that are past today’s date but have the status of not started. Now I am trying to set the appointments that are over 60 days to one color and the ones over 30 days to another color. I am having issues with the date in the formula to set the row colors.  In the conditions it lets me use the @today-30 or -60 function but it doesn’t like the @ today in the results grid to setup the color.  I tried the today() function but then it gives me a syntax error.   =IIf([FSAppointment.ScheduledDateTimeBegin]<((today())-60),'red', (IIf([FSAppointment.ScheduledDateTimeBegin]<((today())-30),'orange', 'default'))) Any ideas on how I can accomplish this? I know the issue is integer versus date but I am not sure how to fix this.

How to filter records by current user branch GI