Reports and Generic Inquires

 Hi everyone, I am wondering what is the right way to sort Kit in Report Designer. I have build a report based on below: I have add below sort filter:When I run the report, the KitHeaderID is not sort numerical or Alphabetically and is all over the place:I went and look at Kit Specification and it would show the Kit in numerical order. Does anyone have an idea what I need to do in order to have it sort numerically?Kind regards,Calvin

Hello,     The requirement is as below.      Supposing I am designing a report with report designer, and this report would show all the items ordered in today, and group by itemID.  I would like to show the lines sorting by orderQty of each line item descending. Here, the order qty of each line is a calculated sum value, I did not find a way to sort by the sum value.     Can someone give me a hand on this requirement?     I know there is a workround, if I export this report into excel, and it is very easy to sort by the order qty field. However, I still would like to know is it possible to just display the lines in the sequence of sum value directly.

Hi everybody,I am currently working on a Generic Inquiry that needs to give out the latest location after a transfer.I read something about Self-Joins which doesn’t seem to be working for the INTran object.Basically, I need to evaluate if the ToLocationID is the one from the latest transaction and display this location. Does anybody know a feasible solution?The client is currently operating in 2022 R1.

I’m trying to understand the difference between the ARTran table and the INTran table and when to use either. They seem to have a lot of similar data when it comes to the TranAmt, TranCost, etc., but I do notice some discrepancies between the two.Any help or insight is appreciated.

I am in the process of trying to add the expense subaccount from the AP bill - using the APTran table - onto the AP Aging Period Sensitive report. Has anyone been successful in doing this?I have added the tables APTran  and POReceiptLine - which hold all the information I need. The APTran has the AP bill line detail information and the POReceiptLine has the account number and subaccount if the bill is linked to a PO.I have most of the information pulling on BUT it is pulling each line on the AP bill twice. I realize that something is wrong with my relationships and/or my grouping since the RefNbr is a link between multiple tables (APRegister, APInvoice, APTran,..). I am at a loss on how to correct this since it doesn’t seem like I can remove any of these tables or modify the relationships.Any help would be appreciated! Thank you.

We use prefixes on our Stock Items to indicate the condition of products. I’m trying to use the LEFT function on the ARTran.InventoryID field in a GI to return the first 3 characters of the InventoryID but it is instead returning 3 numbers.I’ve tried using all the conversion functions and they all return a string of numbers from the ARTran.InventoryID field.Any suggestions?

I export the report designate to excel, but i find the columns and rows in unordered file so that are merging.I need a solution, thanks in advance 

Hello,I see a few tables in the DB for GI's.  None of them seem to tell me when the GI was last run and by Whom.   I only see created by and modified by. Does anyone know of how to get this info?We have some legacy GI's that need to get cleaned up and I was hoping to use this as a starting point. Cheers,k2

Hi:Our client is used to filter his legacy Crystal Reports using the condition [CustID] Not Equal 9*.Crystal generates the correct SQL clause as Where [CustID] Not Like ‘9%’.What would be the equivalent syntax in Report Designer’s Additional Filtering Conditions?We don’t have an option like “Does Not Start With” I could generate the correct filter using Parameters, but the client may want to use multiple ‘Not equal’, so it would be ideal to use the Additional Filtering Conditions. Thanks

I am attempting to create our own subcontract form in report designer. I am using the ABC Contractor -Subcontractor Agreement (CSA). Can anyone help me line up the columns so I can have each paragraph have a title and then the next paragraphs title will only display when the next paragraph appears. Title column is on left & Paragraphs are on right (there are blank text boxes between each row)This appears not as expected and instead repeats the Left (title) column in the first paragraph. Instead of 1 title / per paragraph.See left column, repeats 4 title rows per 1 paragraph.Any ideas how to align the title with its corresponding paragraph?? 

Hello,        I have a requirement to send a report to vendor in excel format using Email.I know this can be done by report designer and “report template”. But, right now, this report is simply wrote in GI. So I am asking is there a way to directly prepare this excel report and send by email.      I also know a way to prepare the excel report in automation by using export scenario, however, in this way, It seems I can just upload this prepared file using SFTP or FTP, while our vendor insisted receiving the report by email.      So can anybody help me?

Is this possible? I know there’s an option to attach a report, but we would like the ability to send an email on a schedule with an excel version of a generic inquiry. The inquiry has a fair amount of columns that wouldn’t fit on the report format, so it’s not an option to create a report version of the inquiry. Thanks!

Hi All,We are currently creating a report in Acumatica’s Report designer that is referring to a User-defined Field.The data in this User-defined field is not updating into the report when it is run and printed. Does anybody if there is a certain setting that might be restricting this or any other issue I don’t know of? Thank you!

Is there a way in which one can round the billable time from the CRPMTimeActivity table in a generic inquiry? In the Case Class the billable time is rounded by 0:05. So if the billable time is 83 min, I would need the GI to show 85 min or where the billable time is 57min, it should show 60min.

Hello,for one of our customers we created a very customized docket that can be generated using a run report action in a sales order and gets used to find parts etc. in the warehouse.We now would like to attach this report to the so line files.Here:I know i can add a parameter to the Report to filter for a individual line nbr, but we can’t figure how you would go on and generate all pdfs for all line nbrs where the article has a price > 0 and attach these to the individual corresponding so line.The Report that would be generated would only contain information from line 2 for example and should be attached to line 2, proceed to line 3 where it only contains information from line 3 etc.Is there a method we can use?greetings

I am trying to exclude cancelled items.  Report works well but I do not seem to be able to exclude cancelled records.  I believe I have tried everything I could think of.  I tried using single quotes and double quotes around the word “canceled” and I cannot get this to work.  All I can think of is that Acumatica actually stores cancelled as something other than the actual word?Anyone have any thoughts on this?Thanks! 

Hi All, I am modifying the GL Register Detailed Report (GL621000.rpx). I am trying to add 2 optional parameters (Module and Batch Number in Journal Transactions form) but the batch number selector parameter does not get populated. Hope you can help.Module ParameterBatch Number ParameterFilter ConditionsErrorThank you.

I am looking for a report that would show ALL ITEMS issued to a POP. Does one exist, or will it need to be created?

Hello!I’d like to know how i can make a specific Action mass ready to use/select in Generic Inquiries. I tried creating a action with the Customization Editor and using the following code to make it avaiable for mass processing.  namespace PX.Objects.PO{ public class POOrderEntry_Extension : PXGraphExtension<POOrderEntry> { #region Event Handlers public override void Initialize() { processPurchaseOrderJW.IsMass = true; } #endregion }}This doesn’t work and fails on validation with “The name ‘processPurchaseOrderJW’ does not exist in the current context.The Action is a Workflow Action and is suppossed to set two user defined checkboxes to true.Would be glad to receive some ideas!Thank you​​​​​​​

Dear ALL,Please help me to know which DAC takes the data of the accounts for all periods of the year (Period 1 to Period 12).This DAC will have Fin.BeginingBalance, Fin.PTD Debit, , Fin.PTD Credit, Fin.YtdBalance,.. almost like DAC GLHistory. But the DAC GLHistory is missing periods where there are no transactions.Many thanks !NNT  

Hello folks. I’m trying to open a report template to make a couple of minor cosmetic changes. I’ve done it several times before without any problems.But when I tried to do it today, the “Open report from server” dialog box wouldn’t accept my login credentials.I have two accounts in the system, both have Administrator and Report Designer permissions. One is an AD-connected account (the one I use all the time for everything) and the other is a native account (that I rarely ever use).Although Acumatica itself doesn’t prompt me for credentials when I use the Active Directory log-in option, the Report Designer app does. As expected, the Report Designer’s “Open report from server” dialog box pre-populated the Login field with my user name and left the password field empty, expecting me to enter the password. But when I enter my password (the one associated with my AD account), the dialog box returns the error: “Error during login : Invalid credentials”.Any idea why the “Open report from serv

I would like to add a dashboard widget that aggregates data from a multiselect attribute. When I configure, the widget is displaying separate data sets for each instance of the varying multiselect values.Scenario: business account 1 has an attribute that currently has value 1 and value 2 selected. Business account 2 has an attribute that has value 2 and value 3 selected.I would like the widget to show 1 for value 1, 2 for value 2 and 1 for value 3. It currently is displaying 1 for ‘value 1 and value 2’ and 1 for ‘value 2 and value 3.’ Is there a certain way the widget needs to be configured to get this to display correctly or is this kind of data crunching not possible for multi select attributes? Thanks!

We need to use the report writer to create a report that needs to include prior financial period information. We are looking to include a trailing 3 months in the report.  We have too many lookups and subtotals to use ARM or GIs.

I have been attempting for months to begin the Report Deigner training (S150, 2022R1).  When I finally downloaded the correct version of Acumatica to a stand-alone PC, I went to the S150 Report Designer 2022 R1 page and downloaded the S150 Files for Training.  The Guide provides the Company Story of the Sweetlife Fruits & Jams company.  The downloaded data popped up in SQL Server with a database name of PhoneRepairShop and the data is for a software company, “Software Inc.” with various branches and stores.I’ve gone through the downloads a few times including deleting / uninstalling just to ensure I am addressing the correct version.Anyone know where I can find the Sweetlife data?Likely addressed before, but the Search function, above, doesn’t work for me regardless of browser.

We have a shared filter (which has been present for 6-8 months+) all of a sudden have a default quick filter added to it?  I’m guessing an employee was on the shared filter and some how saved the quick filter.  Any ideas on how to get rid of it without removing and re-adding the shared filter?  I would prefer not to re-add the shared filter because it will go to the end of all of the shared filters and we want this one to be first on the list.  The quick filter is showing up for anyone that clicks into the GI.We are currently running 2021 R2 Build 15.