Reports and Generic Inquires

Hi All,I am looking to count the total line in Sales Order Summary. I do not know what would be the formula to have the text box to count 1 to 10 or more. I used Count but it just don’t add and give me random number.   Ultimately I need a formula that simply count number of lines in the report and is not affected but the order itself.Kind regards,Calvin Law

Background: I received a request to adjust the Bills and Adjustment Generic Inquiry Screen to not allow users to view all Bills. However, the users would need to be able to view some bills that are related to a vendor but not others as they are not relate to their projects. Similarly, the users would need to see certain bills that were related to a GL account but not other transactions. For this reason, I determined that row level security was not the best route. I instead adjusted the Bill and Adjustments Generic Inquiry to have an owner parameter with the default value of @me; corresponding generic inquiry conditions were made for this as well. This has assisted with the issue as now, by default, the users can only see bills that they are listed as an owner on.  Problem: I am unable to prevent the users from editing who is listed as the owner through tools-Access Rights by Screen in the Generic Inquiry Parameter. So whi

Hello and thanks for the input to all my reporting & Generic Inquiry people!I'm attempting to create a hierarchical sorting structure for a Report Designer Report. I'm following documentation but can't seem to get the data to sort in the intended order.The goal is to sort the Item list by the first 3 numeric values within a 3-segment Item Class code.I can return the value I want in a separate text field: Left([InventoryItem.ItemClassID],3) But I can’t seem to sort on it. As an example, here are a few item classes in their intended sort order:1. 210FENCE  100BARB   100SHEFF2. 210FENCE  100BARB   200OKSW   (note the first 3 digits of the third segment are 200 (for 200OKSW ) , so this would come after 100SHEFF)3. 210FENCE  100BARB   400BEKHRT 4. 215LVSTOCK100GATES  200METRO  (note the first 3 digits are 215, so this would come after 210FENCE)5. 215LVSTOCK100GATES  200METRO  6

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 (whi

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?

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([ARI

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

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?