Reports and Generic Inquires

Good Afternoon, I am looking for some assistance with creating a GI. I am using the Inventory “Issues” GI as the basis. I am trying to modify it to include information from the INTrans table. I would like to bring in the following Properties:INTrans.SiteIDINTrans.InventoryIDINTrans.QtyINTrans.TranCostThen add an additional condition to only show item that have INTran.ReasonCode of “Damaged” Essentially I am trying to create a GI that shows me the total cost/qty by site and Inventory ID of all “Damages”. Using this to begin tracking manufacturing shrink/waste. Currently I am getting error when trying to view the GI so I am sure I did something wrong with the tables and relations. Attached is the GI as it is now. Thank you for the assistance. 

Howdy,This is my first time attempt in the Community forum  and I am still learning the product. So, correct me if I am wrong please.The report “Sales Order Details by Customer” should not display the total Qty if the UOM (unit of Measure is different). An HOUR is different from EA.The report should only display TOTALS for Line Total and  Open Amount 

Hi, I am trying to do a GI get Month-To-Date, Year-To-Dae & Prior-Year Sales totals for an inventory item. There are multiple warhouses setup. I am using the INTran table, with a condition of the item & the TranType of Invoice. If I sort the results in Site Order (SIteID), the results do show in Site order. However, if I set the grouping to be by SiteID, results are totaled correctly by do not appear in Site Order.Is there a way to sort by the grouping conditions?No grouping:With Grouping:   

Hello,     I need to get the infomation of such items:    We have qty on hand>0  And We didn’t sell for a long time like 90 days.    I can get this done by joining INsitestatus and ARTran table.        The key of my solution is that Acumatica will by default get the maximum of trandate after I group by inventoyID      And I can use a “second filter” on GI results like below to get the information I wanted. It is a solution.However， it is quite time consuming because ARtran table is quite huge.Can I replace ARtran with another table (Where I can still get the last sell date infomation and join much less lines) ? Or is there a better way to fulfill my need. 

The mission with this generic inquiry is to show the payment amount value with the decimal stripped out. I’ve tried a few formulas but Acumatica seems to want to add 2 trailing 0s when I do that. Example:  The goal is, instead of showing $8321.05, to show:  832105In SQL I’d want to return a substring, or trim 2 characters from the right side of the string. Here are a few examples of what I’ve tried: Try to get the field to display as a schema field ‘payment amount’ Try to get the field to display as schema field ‘doc description’ Try to return from the right side of the string, -2 positions: =Right(CStr( Replace(  [APPayment.CuryOrigDocAmt], '.' , '' ) ), -2) Try to cast the field as a decimal =Replace(  CDec([APPayment.CuryOrigDocAmt]), '.' , '' ) Try to cast the field as a string  =Replace(  CStr([APPayment.CuryOrigDocAmt]), '.' , '' ) Any suggestions on this? Acumatica really seems to want to add those zeroes!

Can the report designer run on a Mac book?  If yes, is there any special set up? Thanks,  

What do I need to do in a GI to replicate the functionality I would use in SQL for example to haveSELECT Contact.DisplayName [Contact Name] from Contact where ContactType = ‘LD’I want my GI column name to be Contact Name and not DisplayName.Thanks

Does anyone know a workaround to populate the current or last WeekID (EPTimeCard.WeekID) in a parameter on a GI.  I have a few use cases for this and in order to use the GI’s for Dashboards I need this filter to be applied.   As a workaround I know I can use weekstart as its a field on EPTimeCard but this is less user friendly as users would be able to pick dates other than week start dates from the dropdown. For example you can use @today or  @weekstart-1 for a specific date field but I have been unable to figure out the logic to do this for a WeekID if its even posable. Thanks in advance for any suggestions.   Attached is a GI I’m using to show missing timecards.

Hello,I m a new user of Acumatica,  I would ask the other more experimented users, is there some way to retrieve data from the cloud database down to Excel like I was used to do with my MS SQL Server database, via Power Query fonctions (I had of course the Server address, username and password to be authorized to get in) ? thank you very much. Cai

I’m trying to access attachment contents over OData so I can pull Stock Item images into Excel. I tried PX.SM.UploadFileRevision and PX.SM.UploadFileWithData, but the UploadFileRevision.BlobData column doesn’t appear in either a Generic Inquiry or in the OData feed. Does anyone have any ideas on how I can access the raw file attachment contents? Note in these screenshots that there are 5 columns in the Generic Inquiry, but only 4 columns appear in the results: 

Good day, I modified a check form (AP641000), and everything looks good, when printing more than 1 check, the View PDF or View HTML shows the correct data on the preview of the PX.ReportViewer.axd. But, when you print the checks using the PRINT button, ** I edit this part, as I get the margins to work, but not the first page *** the starting the second page, the margin for the second and third page will move up, but the first page margins (seems to be the top margin) will not match the other pages, is about a row down, not even that much, but is notisable.  Have any had any experience with this? The customer is printing on an HP printer and I have an HP printer and can replicate the issue, but cant seems to find a solution for it.

I have been trying to figure out if this functionality is possible in Report Designer. I have a report with a grouping of items. I am trying to add an image that spans however many rows are in that group, but currently if I add a pictureBox to the section, it will repeat for X number of rows in the group and also add additional whitespace for each row. Is it possible to have an image (pictureBox) span multiple detail rows/groups in report designer? I have seen a previous example using a vertical line that spans down the entire length of the report by using the “bring to top” option, but trying to recreate this using a panel with a picturebox included has been unsuccessful. I’ve also attempted using a panel, line, and/or pictureBox with a background embedded image set, but that did not work either.   

Does anyone know of a way to show the applied payment total (including prepayments) on the SO Invoice printed form report (SO643000.rpx)?  The field appears on the SO303000 screen and the total is correct, however that field (ARInvoice.CuryPaymentTotal) is not available to choose in the Report Designer.  It seems like this would be a common requirement, so hopefully we are just missing something obvious here.  This is for 2020 R2.  Thanks in advance! 

helloi tried to join batch and account which is not possible so i have to pass by gltran to get one field to filter with it branch.status  my problem now is that each line is duplicated many times i know the problem is with the join between LegalGneralBalance and GlTranI tried to join with Account and i tried to group the result with AccountID Or AccountCD but i got the same results 

hello everyone, I was trying to create a report with two different date ranges to compare the values, for example, if I chose a date range from  01-2018 to 03-2018 it would bring me the values from this range and from last year like 01-2017 and 03-2017 and I was wandering if I can add a check box when I check it (true) some filters will appear to choose the second date rangethank youthis is the standard report I want to compare values like the report in RM00CG00  

We are rolling our the contract management module and noticed there really aren’t any reports or inquiries out there. Has anyone developed reports/inquiries for contracts they’d be willing to share?Thanks!

How can pass list of values to a single parameter in the report designer   

Hello, I am trying to add the Customer Order Nbr to my Shipment GI. I have added the table and included the correct item in the Results Grid. However, nothing is populating in the GI. Any idea as  to why?  

I am trying to set Dashboards for various users in our company.  Is there a good video training or KB article that would be helpful? 

Hello,    We previously enabled system locales/multiple languages.  Now, the inventoryitem.descr looks like right and proper in the GI screen like below:   However, when I am trying to use it in the email template, it is displayed with extra information.  [{en:YIHAI\ 15x1000g\ Seasoned Chili Oil}]    You know, we don’t need the {}，[]  and en： kind of things   How can I make it just look the same as in the Screen.

We’d like to use the results form different GIs for one report, is this possible? How?

Hellowhen I try to recover the field [InventoryItem. Body] and change the option convert HTML into text and the option can splits to true, it doesn't move to the second page so it always removes some lines and I want to know if report designer has a limit on how many lines can print from InventoryItem. Body THANK YOU

Looking for help from GI experts! It is very difficult to distinguish between open service orders that have appointments scheduled for the future and open service orders that have appointments that have past or no appointments at all. (In this case, appointment needed = false). I’m creating a custom GI called “Appointments on Open Service Orders” that shows me if there are future appointments for a service order or not. This appears to work--I can identify if there are no future appointments, therefore triggering review.The only issue is it doesn’t show me open service orders that have no appointment at all. Generally, if the service order is open, no appointments are created and appt needed = false, it’s because there is nothing in the Details tab (to trigger appt needed). I can’t figure out how to create a way to catch this (because there are no appointments and no details, so what do you use for GI?).So, I have two questions: Does anybody know how to “catch” open service orders with

Hello，     For the SO643000.rpx, we are using Acumatica original logic to display the Date and Due date as below:         It shows properly when print the invoice on the screen directly.          However, when we are using “Notification template” to attach this report and email it to customers.      It shows 2 and 3 instead of Feb and Mar,      Which we does not understand.    because the rpx is exactly the same one      Can somebody help me?      

Hello everyone, Is it possible to print items with zero quantities on a pick list? I am trying to get some non-stock items that have a distinct item description that act as line comments for the sales order. For example, I will have 10 lines of stock items with quantities and then 2 lines of non-stock items that act as description placeholders for the order itself, however they are at an order quantity of zero.  I have tried to play around with the visibleexpr and simple iif statements to no avail. If anyone can point me in the right direction on this one that would be great.