Reports and Generic Inquires

Hi All,I am creating a report that will help me identify amount of defect / return over a period of time. I used SO.61.10.00 Sales Order Detail By Customer as base report. The issue I am having is that the SOLine.ClosedQty is not able to sum based on the different order types. I have multiple order types that I need to subtract each other in order to produce the result I am looking for. Below are what I am trying to do:Sum of Sales Order - Sum of Correction (Custom Order Type)Sum of Credit (Custom Order Type) + Sum of Replacement (Custom Order Type)I used IIF function in on the detail and it work fine, only when it comes to SUM where the number is off. ‘=IIF([SOOrder.OrderType]='SO',[SOLine.ClosedQty],IIF([SOOrder.OrderType]='CO',sum([SOLine.ClosedQty]),'0')‘Are there any suggestion on what I can do in order to achieve what I want?Kind regards,Calvin Law

Hi, We have created a Inventory Transfer with added from Location and To Location. There are Two Items. We have created a report. We changed the Existing inventory transaction report.   Mainly we are using INtran table and if we have two different location system create two lines in same table.  we tried removing the location columns. but we could not find any distinct column option. We need see only two lines in above example. Expected result as follows.   RegardsNethupul    

I'm putting together a GI around Tasks to use for Dashboards and I need to use the Start Date and End Time fields to show the Start Date and Due Date of tasks. However, I've discovered as I was trying to set up filters for the data that the Start Date and End Time fields are displaying as 1 day earlier in the GI results than they actually are when you go inside the Task.For example, in the GI results this Task is showing a Start Date of 6/15/22 and End Date of 6/16/22: However if you go to the actual Task itself you'll see that the Start Date is really 6/16/22 and End Date is really 6/17/22: Anyone know what's going on here and how to fix it so the GI results show the correct dates? The filters appear to be working off of the true dates in the tasks which makes the GI results seem off when using the filters. The only thing I can think of is perhaps there’s a time zone issue between the fields?Thanks!

I’m trying to sort out default locations for vendors using the vendor location generic inquiry. I’m able to add the “IsDefault” from the Location Table to the results grid in the generic inquiry but when I run the query no locations are marked as default, even though obviously one for each vendor should be the default. Am I missing a step or choosing an incorrect field?

We are working on upgrading two clients to 22.106 and this version seems to have removed the ability to use the keyboard copy (Ctrl-C) to copy the value of a cell into the windows clipboard.  This issue does not exist in 22.105.  Can someone confirm if this is a bug because will will have clients that will complain about this loss of functionality?Thanks,Kurt

We are not using the Acumatica branch logo functionality, because we have a header logo that needs to span across a page.We have added this as a picture on our reports, and it renders clearly in PDF as long as the PDF is viewed in an internet browser.  However, as soon as the rendered PDF is downloaded and viewed in Adobe, the logo becomes grainy.  Here is a small portion of the text in the header logo on Adobe vs Chrome (both viewed at 100%):You can’t really see the problem clearly here, but it creates a significant difference in quality when you look at the whole logo.  I’ve tried different sizing options (normal, scale, fit) and none of them make a difference.Does anyone have an idea on how to fix this?

The report is only pulling data utilizing the main report parameters.  As long as there is data within the parameters of the main report it will print the main report and will then print the data from the subreport that is within the same parameters. If nothing prints in the main document due to the parameters nothing prints from the subaccount even though there is data that should print. What am I missing?

I need the date output in a GI to be yyyy/mm/dd for a third party integration but we use mm/dd/yyyy as a default. How can I flip this around?

Dear ALL,I created Generic Inquiry using “InventoryTranHistEnqFilter” table. But when I pressed “VIEW INQUIRY” to see the result it gave error “Invalid object name 'InventoryTranHistEnqFilter'”.Please explain the cause of the error and help how to fix it !Many thanks !NNT 

Is there a way to create a generic inquiry for Dashboard widgets and the linked inquiry screen into the widget. I want to find out and get a summary what generic inquiry we are not using, not linked to a dashboard

Hi all, Do you know how do I add the Description Tab from stock items screen to a generic inquiry? If I add the body field that is how it looks.  Thanks, Patricia 

Hi All,This is a 2 part questions. I used Daily Sales Profitability AR.67.60.00 as a base report for my Freight Rate report. 1 - How to have the report to show SOShipment.CuryFreightCost to show correctly when I use SUM function? The base report used AR Tran for Net Sales and Cost and it works fine as it sum up each line to create a total. But because Freight Cost is in each transaction line, it would multiply itself with the same number of times as the total lines in the order. I somehow manage to have it correct on the individual order, but when I sum them up, they are wrong. See below: 2 - In the same report, I have orders that are being shipped to US. I am trying to show CurrencyRate.CuryRate for all the US orders. When I link Currency and CurrencyRate table, it doesn’t show the exchange rate at all. Hoping the community will give me some ideas to resolve my issues. Kind regards,Calvin Law

Does anyone have a good explanation of the difference between PX.Objects.AP.Vendor and PX.Objects.AP.VendorR?I haven’t encountered a difference between them (yet), but I’ve noticed that the standard Vendors (AP3030PL) Generic Inquiry uses PX.Objects.AP.VendorR and I wonder why.

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 while the default is the logged in user they could delete the user a

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. 230GARDN  100GDNSPLY   (note the first 3 digits are 230, so this would come after  215LVSTOCK) The thing that

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.