Reports and Generic Inquires

Hello all,I’m working on a generic inquiry. I need a total inventory amount for each item. The result as below. The item existed in different warehouse.  When I add a grouping for InventoryID. The result become to :  My question is how do I ignore grouping on MSRP column? My GI setting is below.  

Using the APARord view as previously documented works well.  I have a client that needs 3 copies of a form, how do I set this up?  I am trying to print 3 copies of the Sales Order, each copy has a slight variation to what prints.  I used 2 sub-reports to achieve this, but the issue is when it tries to print a multipage sales order, the sub-report prints page 1 correctly, but then continues on with the line details onto page 2, with no logo, heading information which the client does not like.  I can create 3 separate forms, place them in the reports tab of Sales Order screen.  So the client wouldn’t have to click on each 3 separately to print, can there be some type of automation that when form A prints, automatically print form B then form C?

Hi,I have created an User Defined Field in Sales Order form. I want to add the same in Sales Order printed form (SO641010). I am aware the value for User Defined Fields are stored in SOOrderKvExt but I do not see in Report Designer. I have even tried Build Schema > Load schema and Refresh/Refresh All, but still I am not able to get it.Any suggestions?I am using Acumatica version 20.208.0031.

Hello,   I’ve wrote a tabular report to show customer(as row) sales in 4 warehouses (as column).The values are the total sales qty.     The column right now is dynamic, that if 1 warehouse has no sales at all, this column stands for this warehouse would not show on the report.       Is it possible to always show 4 columns, even if that warehouse has no sales.  

Hello,      I’ve wrote a report and I’d like it to run on schedule to show the last week’s data.I was using “[WeekStart]-1” as the datefrom and “[WeekEnd]-1” as the dateto.       And I save a template to schedule run on every Sunday.       However, if the business date changes, When using the template the DateFrom would change accordingly, while the dateto would remain still.       I temporarily work around this issue by assigningDateTo  =DateAdd( ([@WeekStart]-1), 'd', 6)         Then, the Dateto would change according to the business date.        So, I am wondering did someone else meet the same issue and is there anything wrong with the [WeekEnd] parameters.

I need a report or generic inquiry to show customer lead time.Our users need to be able to enter a Starting Date and Ending Date based on Invoice Date.I would like to be able to run this report by selecting a single customer or all customers.The lead time would be calculated by subtracting the Invoice Date - Sales Order Date.I would prefer a report but I’m not familiar enough with the tables/relations yet to make this myself.I see the GI DB Ship On Time, so maybe we could modify this GI…I look forward to seeing what you guys and gals can show me.

Is there a way to add a time stamp to when EDI salesorders enter Acumatica?

How to adding a clickable link for excel export using generic inquiry. It should be same report export. 

Is anyone aware of support for the Maxi / Aztec code barcode standard within Acumatica Report Designer? [ reference : https://www.barcodefaq.com/2d/maxicode/ ]We’re currently working through a phase of our implementation that dives deep into the Shipment Label needs of our larger customers. Some needs seem straight forward but end up at a dead end while others have slightly convoluted solutions. Oh, please note that we’re integrating with Easypost which will likely render the majority of our more typical Shipment Labels but these are not the labels I'm concerned about at this moment.Imho these larger customers' needs could be accommodated easily if we were to take ownership of the design of the Shipment label. I know there’s some decent work involved, which I've done before, but it would be a clean repeatable process.The main road block I now see is that Acumatica Report Designer doesn’t seem to support the Maxi / Aztec code barcode standard.Has anyone had the need to print this barcod

Help me please, how to include image to imagebox for external link, example from google photo

Hello,          In Generic inquiry, we have something fantastic like @weekstart and @weekendBut these does not work for report designer.        Is there a way to set the report designer’s default data range as “Last week”?

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

HI, I am trying to do 2 things in a Generic Inquiry for sold Kitted items - getting the quantity of the components.I am trying to have a calculation of sold components be exportable to excel as a numberic field from a GI. The formula is: =([ARTran.Qty] * [INKitSpecStkDet.DfltCompQty])It comes through as a text field, left justified. We would like the result to be a numberic field, Right justified.We would also like to have this display with only 2 decimals 

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!