Reports and Generic Inquires

Hi,I've created a generic inquiry, which pulls record from project transaction record table and based on that GI, I've created a pivot table, which will display month wise project balance for each account group and project. now i want to add another column to display total of current month + last month. how can i achieve this ? So if you look at screenshot, i want another column in pivot table as running total (which will display current month + previous month total). eg 16591.71 + 4190.54=20782.25 in 03-2022 period.

Encountered this issue while working on the generic inquiry. Anyone can help advise how to resolve this? Thanks!!

Hi Everyone,Is there a limit as to the number of pages of a report that can be exported to excel or pdf?It seems like that if the report has 5000 pages, the export to excel or pdf is not working.Any ideas or suggestions on what to do?Thank you.

I need to multiply the value of a variable by 4%.  I am unable to get the calculation to work.  Nothing I try results in multiply the value by 4%.  Any ideas? ThanksSharon

I’m trying to recreate a label design we have been using previously with another package.  The barcode is rotated 90 degrees at the side of the item code and name.  Is there any way to do this in Acumatica Report Designer? Thanks, Phil

Hello,I created a UDF on the shipment screen.  Not an attribute, a user-defined field.I want to print this on SO644000.rpx.While this report does have the SOShipment dac on it, the attribute is not available within the report builder for me to select.I created a generic inquiry just to make sure the field exists, and it does.  I can select it for the GI.I just can’t select it in Report Builder.  I even tried manually using it as a value by typing the name as it should be in the “Value” of the field on the report.  It will not validate.If I examine the DAC SOShipment it is there.  Any thoughts why this would be available for user everywhere except in the report builder?Thanks!

I am looking at creating GI for the above report but the tables used in the report don’t seem to be available in GI. Can anyone advise please? Subreport uses the below which isn’t in GI either. 

Hello,I am currently trying to create GL transactions via the Journal Transactions form (GL301000), specifically, lesson 5 on the p100 project implementation course. When I click “release”, it says “The conversion rate from the transaction currency USD to the project currency cannot be found for the rate type and 1/9/2022 date”. What could be causing this and how do I fix it?Thanks!

Hi, I need help with  a report. I have two different tables, both are grids. When I want print the info in report, the information is repetead twice.This is how I have the relationship between the tables, I use orderNbr as a parameter to connect them I tried with Left relationship but, is not worked.

Hello, Has anyone seen an issue with the Approvals page under the Time and Expense Module regarding “Total Amount” being incorrect? It looks like it is pulling the latest expense receipt amount on the claim, not the total amount for the Claim. This looks to be a customization project, has anyone fixed this issue in the past?Thank you,KT

Hi Everyone, I’d like to use the Date-Relative Filter Clause: “@WeekStart-1” and “WeekEnd-1” so that I can have this report distributed via email weekly with the previous week’s shipments. The additional filtering condition does not let me enter anything but a hard-coded date. I’ve looked in report designer “build schema” but couldn’t find anything obvious that is casting this field as requiring a hard-coded date value.The documentation only references this pane in context of the “Behavior > ViewerFieldsMode” property. I have it set to MergeWithUsedPerhaps Advanced Filters can be enabled in the Report designer and I missed it? Thank you, 

I have an automation that is kicking off daily, but the automation that defined it has been deleted. How do I diagnose and find the cause of the daily email I am still getting. A few weeks ago, I was testing automated sending of reports via email. I created a template of a canned report: Shipments by Customer. I added a schedule to our Send Reports screen. I set the report to email daily at 12:54 pm. Then I added this template to the schedule from the report screen. After some more testing, I dialed in my settings and created a new template and a new automated schedule. The new schedule sends the report every Sunday and is working fine. I deleted the original template and schedule… but I’m still receiving the daily email.In the history, the only thing I can find at 12:54 is a generic “process all pending emails” schedule, but when I look into that history, there’s no evidence of my report in the pending/completed/failed list.

Hello,We recently had the unpleasant surprise of discovering that the default behavior of the Sales Prices Inquiry screen and web service has changed between 2020 R1 and 2021 R1.We used to query the API for all sales prices with no EffectiveAsOf date specified, and would get back all prices for all Effective Dates, including expired prices.Now, all API requests default to returning only prices that are EffectiveAsOf the current date.We whipped up an emergency GI to serve this purpose, but I would prefer to use the built in Sales Prices Inquiry, if possible.Is there a way we can get the old behavior back? I have tried API requests with no EffectiveAsOf date, a null EffectiveAsOf date, and an empty string, but it always comes back with today’s date in the response, and prices missing.Thanks in advance for any insight. 

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.

Hi There, Just checking if there is better answer for this.I know that when using “is between” in SQL Server, or GI,  start date is inclusive and end date is exclusive since time default to 12:00AM. But on Case screen, if I do a filter using “is between” on date column,  the end data record is included unless I manually change the time to 12:01AM.Why? Thanks,Woody 

Hi Everyone, I’m trying to understand formulas in GIs. The documentation does not provide guidance on how to use formulas. There is a Report Designer guide that lists each formula and how it’s used (Isnull() on p. 63) but it lacks detail about how data types affect expressions within the formula. If you know of another resource, please share :-) On to Isnull(). Here’s the documentation.I’ve written a simple formula with unexpected results:=Isnull( CStr([ARInvoice.CustomerID]), 'NULL')in row 1, ‘9698’ does not match either of the referenced columns. Why?My ultimate goal is to have this algorithm in a way that Acumatica understands:If CustomerID is null, return ReferenceID, else return CustomerID 

The report exists on my end.  I can see it under Data views. See screenshot.  However, Emily and other users can’t view it anymore.  She used it this morning.  I looked at the security.  I review the GI but nothing pops out on what the issue could be.  I tried moving it to a different module and the moved it back.  Any ideas? 

The MICR font on my check form is displaying with all red zeros when I print the check.  Any ideas why this is happening?

I have created a GI to use as as an overview of all approval maps.  For some reason I can’t select “Use in Quick Search” for the AssignmentMapID_Description.  Is that because it has a navigation target selected?  Also I noticed that for some of these fields, it takes really long to search, is that because of the complexity of the query?  

I need to create a report based on the Current Sales Price and compare it to Last Cost and Avg Cost, so we can see what the Profit Margin/Profit % is.I need to include the following fields in the report:Customer        Inventory ID     Current Sale Price       Last Cost         Avg. Cost         Last Transaction Qty     Last Transaction Date    Profit Margin    Profit%I would like to be able to filter this report based on Customer Sales Price Code/Customer and Start Date (this would be the Start Date for the Customer’s Price Code)I’m not sure how to bring all this data together, so if someone out there can help, I would greatly appreciate it!Sarah

Hi All,I am working on a report using Detail Sales Profitability as the template to include Freight Cost. Everything works well until I am trying to add up the total for Freight Cost. The issue I am having is that the system somehow keep adding with the same freight cost base on the amount of lines it has within the shipment. Please see below for screen shots:From the report:Example 1Example 2 From Report Designer:  How would I be able to fix it so it don’t repeat adding itself within the same transaction and actually add up all the invoices within the customer?Kind regards,Calvin

Customer would like to print the Shipment Confirmation Report with one click, compare to standard 2 click, one to render report to preview and second click to print.Any thoughts?

Hello,I am trying to include a quick filter on the PMTran Date for a GI. I would like to have the filter setup up to show everything “Is less than or equal to”, I would like it to also show all blank dates as well. Does anyone know how to do this?I attached two screenshots1). GI Screen results (with quick filter)2). Results grids of that fieldAny insights would be helpful! Thank you,KT

Each of our projects can have different insurance requirements/thresholds and/or warranty duration that need to be included/communicated to our subcontractors/suppliers on purchase orders. I have created the necessary fields using “Attributes” and then I added these attributes to the Projects (PM301000) screen as user-defined fields through the Customization>“Manage User-Defined Fields” prompt.Now, I can also add these attributes as user-defined fields on the Purchase Order screen, but they just appear again as blank on each PO form and it doesn’t copy over from the project.In Report Designer, I can edit the Purchase Order report to print the user-defined fields that I added to the Purchase Order screen, but is there a way to link/relate the Purchase Order back to the Project such that I can print on the the user-defined fields from the Projects screen instead of from the Purchase Order (so the attributes only need to be filled in once and are then copied over to each PO if the PO h

I’ve been playing with OData Version 4 with Excel.  My questions is regarding performance.  Is it faster to link individual tables for example InventoryItem and INSiteStatus or just link the InventoryItem table and expand the INSiteStatusCollection?