Reports and Generic Inquires

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.

I have created a GI to capture cases closed this week that have an attributes field selected to send a survey. The GI is rendering correctly.I have an email notification with a business event that fires when a record is inserted.  I want to track the email to the case when it is closed, and when I add the ((CRCase_noteID)) to the Link To Entity field, the email activity tracks to the case, but it sends twice. If I remove the ((CRCase_noteID)) Link to Entity, it will send the email once, but doesn’t track it as an activity to the case. 

Hi All,I want to drill down and show like a parent child relationship in the GI. As seen below reference number 00038 has two items on it but they are not visible, instead of clicking and going to the next screen, I want to see the result below.Is it possible?Thank you  Below are the items on reference number 00038: 

Hello,I want to be able to display the POs linked to a production order.Generic Inquiry, reports that type of thing.I don’t seem to be able to inspect any element to find a DAC or anything.  I usually would be asking for the SLQ Table but I know that isn’t relevant for Acumatica hosted.How would I be able to determine this for elements I can’t inspect?Does anyone know what I am looking for?  I need a “table” with production order ID and the linked PO(s).Thanks!

On the default Sales Order print out the Quantity, Price and Discount Percent field prints out to 4 or more decimal places.  Example How do i modify the format in the Report Designer to only Print 2 decimal places. So the qty reads  1.00, price reads as 17,198.00 and disc pct reads 3.43%   I am assuming i change this in the Appearance row for each field.  I can not get the correct Syntax.  Example below is what is in there by default. 

Hi All, How can I restrict the visibility of Purchase orders only to the people of the same work group. meaning if the Approvers of other departments log in they don’t see the full list. and users also see what is relevant to their department. I have setup Work groups ( in Company tree) but I cannot see how I can filter it in GI. Thanks for your help in advance. Ghazal

Hello,I’m writing a very basic report in the report designer to be used as a subreport later.It is one table with 2 values on the report in text boxes.I get the following when I click “preview” 

Hi All - I'm really struggling with a seemingly simple GI. I have the POLine table and I want to get the most recent unit cost for each inventory item. For example, in the filtered result below, I would like only show the circled results.   When I group by InventoryID and apply either a aggregate Max on the order date field or a sort by date, I get the most recent date but I don't get the correct unit cost associated with that PO date. Instead, the cost is a sum of all aggregated records. I can’t use any of the aggregate tools on the unit cost (max, min, avg, etc.) because cost can go up and down - I want the most recent cost. How can I just get the unit cost associated with the most recent record date? Is there any way to do this within a GI? Any help would be greatly appreciate! Best,Amit

How do I make widgets I have in Acumatica show up in the Acumatica Mobile App?  If it matters, most of my widgets are derived from pivot tables. 

Hello,I have a GI where I need to include certain Sales Orders AND exclude orders from that subset where they have 1 particular NONSTOCK inventory item "COLOFEE"Hoping someone can help with the funky GI conditions functions:  I can do this in sql using the Having clause:Select c.OrderNbrfrom soorder C, Soline A, Inventoryitem Bwhere c.OrderNbr=a.OrderNbrand A.inventoryid=B.inventoryidand B.inventorycd = 'colofee'group by C.OrderNbrhaving sum(case when b.inventoryCd = 'colofee' then 1 else 0 end) = 0help is appreciated!k2

Is there a setting or access right that can be set to allow users other than Admin to ignore the Excel export limit of 1000 records?

I am developing what I think should be a pretty straightforward Sales Order form. For the transaction lines, I would like to have a grid format where there is a border around each cell. This works fine if I put a border around each cell in the detailSection1 part of the form (I have adapted this from SO641010.rpx… the out of the box Sales Order printed form for Acumatica). When I preview this in Report Designer, I get my grid with borders and everything is good. However, if I output to PDF, then only some of my vertical borders appear, and no horizontal borders around each cell.Preview in Report Designer (or in Acumatica with PDF Preview disabled)... Output in PDF Format… grid lines are basically gone I have seen some previous hand wringing about this in other posts. I don’t need this to work perfect (it obviously doesn’t), but I just need A way to do this. If it involves a specific configuration for each individual cell, so be it. But can someone demonstrate a working way to create a

I have a requirement for a csv file to be created that will be uploaded to a bank. The data is tied to a check run listing out the check number, vendor, amount, and invoices paid.  The pain is that the format is one row per check, and the invoices paid by the check are listed out recursively across on the same row, be it just 1 invoice being paid or 20 by that check. It repeats the invoice/ref number and amount paid (against the invoice) columns on the row.  Right now, the spreadsheet is maintained manually by the customer so they handle the uncertainty of how many invoices are being paid, column headings, etc..  I know the unknown of how many invoices are being paid is a problem.  I have a GI that lists what I want with each invoice paid being a new row, but no idea if that invoice detail can be placed going across rather than down.  I’m thinking it probably isn’t possible within a GI, but just wondering  if anyone has encountered this type of format/layout requirement and what any op

Hi all! I’ve created a set of labels using Report Designer and added to the site map. I now want to add a Print Label button to a generic inquiry. I setup the report up in the navigation parameters, added a column that reads ‘Print Label’ and pointed the navigation to the report/labels. This works when only needing to print one label. But if I click print label on one row and click print label on another row, for example, the second row displays the data from the first row. It doesnt seem to update based on the row I am clicked on. Is this a bug or is there a setting needed to get the print out to show the correct data per row? Thanks!

Hi, We wanted to create a GI to get the inventory in and out details for a given time period hence we created a GI joining PX.Objects.IN.INTranSplit and PX.Objects.IN.INTran tables. Inquiry title is Inventory Transaction History Inquiry.Then we gave start date and end date parameters using the release date in InTran table.The end date parameter seems not working properly. For example, if we give the end date as 3/31/2022 the data shows only up to 3/30/2022. Is there any reason behind this behavior or have I not set the GI in a correct manner? Please assist me. Best Regards Amanda  

Hi, I have customized the Inventory Register Detailed Report and no additional joins have been added on my end, but there is a duplicate line being printed on the report, this happens in the original report as well. How can I modify it to not print a duplicate line?Transfers Form has 2 recordsOutput has 4 lines  

Hi,I am still green to creating Generic Inquiries. I am trying to create a GI that displays the total inventory Items including qty and amount for each customer invoice. Then from there I am going to create a dashboard that shows the top selling items for each inventory item by qty. Here are screenshots for my total setup. How can I get the Customers Invoices to consolidate/filter correctly in my GI? Let me know or what conditions/settings I am missing? I have also attached an export of the GI if needed. Thank you,Frances Pantelidakis  Screen 1 Tables Setup: Screen 2 Relations Setup: Screen 3 Sort Order: Screen 4 Results Grid: Screen 5 Output of GI:  

Hi, I am doing a GI to show the GL accounts for NonStock Items.The NS Items are tied to a posting class. The posting class can use the default of Inventory Item (I) or Posting Class (P) I am trying to get the GL account to be in one column. My data field looks like this: =iif ( [INPostClass.InvtAcctDefault]='I', [InventoryItem.InvtAcctID], [INPostClass.InvtAcctID])The GI is returning 2037 instead of the GL#, 11302. Is there a function or a way to get the 11302( The GL from the chart of accounts) to display? Thank you, Bill 

HeyI was wondering if anyone was able to create a GI with more than 213 Rows. The max seems to be 213. Also, has anyone added new rows to a GI  from the XML file? My guess is you need to generate a new RowID; that's something I have not been able to figure out. 

Like my last question, I am receiving the following error, but it is not on a field that can have a aggregate function. I have attached the xml file.Sorry for all the questions. I will get this down eventually.