Reports and Generic Inquires

Hi, we scan and attach our proof of delivery to our invoices for easy reference. Our administrators in each branch have been instructed that all invoices need to have the POD attached, but this is not always done. I need a report which will show me which Invoices do not have attachments so I can address this with the branch manager.

I am trying to find a table the will show if an order has been picked. I’ve found a two fields in the SOShipment DAC one called Picked and the other called Picked Qty but neither seem to be used. Are there other tables that have either Picked or Picked Qty.Thanks 

I am able to insert QR code on invoice template(so643000) with any fields i need with no challenge.I have a requirement from customer to use the QR code to check if invoice exist in database for the customer, date and amount. If exist the QRCode should return “Valid Invoice for Customer “ABC”, Date “23/04/2024”, Amount “USD500”. If invoice cannot be found the QRCode should show “Invalid Invoice”Any help appreciated

Customer has 5 branches.  They have a AR Aging Template set for each branch.  Is there a way to run all templates with one click / selection?  They want to do this on demand not on a schedule. (2023R2)  

Hello,VERY new to all of this (our company is going live next week).  I’ve been watching the university videos and looking around here, but I cannot seem to get the answer to my issue.  To keep it as simple as possible, I copied a dashboard for Sales Manager which shows all kinds of information in widgets for every salesperson.  I want to take that copy, and make it so it only shows data in the widgets for the person who is logged in and make it accessible to all of our sales team.  I’ve tried various @me’s  but it just doesn’t work.  Maybe I’m picking the wrong field?  I’m just spinning at this point mentally trying to get what seems like should be a very easy modification to work.  Any help is greatly appreciated.   EDIT:  I have been able to make the widgets work if I manually edit each one to a specific person.  I’m trying to avoid doing that 75 times.  Hoping I can just pass the value of the logged in user down to each of them.

Hi,In report “Account Details”, it only allow to run a single Account each time. I want to run a range of account, just like Period. I try to work on customization but not successful. Could some one help me on that?Thank you.Raymond

We have a Generic Inquiry that has the Customer.ParentAccountID and not all of the parent accounts have that that number all by itself (the account number also, in some cases, has the name of the customer). I was thinking in that case to use a SWITCH similar to the one here:=IIF([Customer.ParentBAccountID] = ' ', [Customer.AcctCD]) but that is not working also. Also I am not sure if the Customer.ParentAccountID is empty or NULL. I know that the Customer.ParentAccountID should be showing up because when I do a CRTL+ALT the Element property shows the field as Customer.AcctCD. I also thought about getting just the number portion of the AcctCD but that seems more work then necessary. I could use any suggestions that the Community may have.There are accounts whereby the Customer.ParentBAccountID is showing up correctly on that column.In this case the Customer.ParentBAccountID = ‘ ‘ or NULL and the customer.AcctCD is 16310 - Grainer. 

Hello, I am new to Acumatica but been working with Power BI for many years. I have been working on an integration to bring in invoice (SO LInes + Header) data. I have been able to connect to a GI and begin to import the data into Power BI. However, the query seems to stall out at 268MB. When I export the GI out of Acumatica to excel the spreadsheet is 27MB. The shape of the excel document is 187k rows and 35 columns. We are just looking at completed shipments/invoices so nothing open.I am able to import a customer master table and an item master table without issue which are both under 4MB. We are using the Power BI incremental refresh as a way of managing the large amount of data Power BI tries to import.Is there a reason Power BI seems to be trying to import more than 10x the size of the excel export?What I am attempting to do is import each year in a separate GI. So 2022, 2023, and then the current year. Here is the current year query to the GI. Very straight forward: let    Source

Can I know if is there a way to set the visibility of the Detail Section’s header to appear on a page without keeping it as ‘Print on every page = True’ if only the Detail Section has been populated in that specific page? Please check the below attachments. It depicts the 2nd page of the report.Repot Preview 

Hi all, I want to create new GI which can show the total cost of purchase receipt and total amount of bill but I dont know how to link because there is no link between 2 screens  Thanks in advanced.

how to solve it ?

Hello all, Quick question. Can Power BI only connect to Generic Inquiries? Or is there a way to connect to the instance directly like you would a SQL Server connection? We’re on a cloud instance. Thanks!

i want a generic enquiry that lists all journals filtering by Company and or branchChallenge is filter only showing branch level and no company level. Any help

Hi Everyone,Good Morning,I need data from FSServiceOrder table based on the CustomerID and LocationID to the report.For example:Based on the SrvOrdType, RefNbr we have CustomerID and LocationID. For this CustomerID and LocationID we have multiple data in the FSServiceOrder. So I need to get that multiple data into a sub report based on the filters CustomerID and LocationID.So please help me out, how to get that multiple records based on the filters in same DAC.FYI...Please see the below attached screenshots: 

Hi. In my report parameters I want to load Sales order number parameter that has open status only. How can i achieve this ? Please help!I tried to achieve this by setting the soorder.status equal to N in the filter section. But it’s not working. Can anyone help?! 

Howdy Howdy,I am experiencing a very perplexing issue and I wanted to throw it to the community to see if there was any insight into the problem.ABS-VENDSUM is a clone of the Bills and Adjustments Generic Inquiry with less results grid lines and a few more conditions. Essentially it pulls all closed bills and adjustments, properly converts debit adj into a negative value, groups them and while grouping it sums the total values. Simple. ABS-CHECKSPAY is a clone of the AP - Checks and Payments Generic Inquiry with less results grid lines and a few more conditions. It was determined that measuring cashflow via checks instead of bills was more accurate which I cannot argue. However when applying the same logic to the APPayments table everything works identical until we attempt GROUPING the output. At this time it selects a single transaction and presents that as the value. You can see that the logic is working because ungrouped it shows all line items it should show and it correctly turns

Ok, this is a stupid question.  We recently implemented a new tenant on our Production system and I have a need to connect to the site using Report Designer.HOWEVER, because now we have multiple tenants the old URL format of “https://acumatica.xyz.com” does not work.  I know with OData you have provide the tenant name at the end of the URL like this, “https://acumatica.xyz.com/odata/tenant%20name”. I thought the format for the Report Designer would be similar such as“https://acumatica.xyz.com/tenant%20name”But that throws an error when I try to load reports. So, what is the correct URL format for Report Designer to connect to each tenant individually?Sorry again for such a stupid question

Is there any way to get the SQL running behind the Pivot table? Also, if I have a SQL Script, how can I get the PIVOT to be created from that SQL?

In order to create balance in PBI, I need beginning and ending balance of each period. However, there’s no generic inquiry that provides such information.  TrialBalance Odata is empty (no data pulled when connected to PBI via odata)GL Tran does not have beginning and ending balance Anyone had any luck? Thanks

I am Trying to  create a Report to Find out the List of invoices Paid in Full /status closed based on Payment Date  with Following Fields SO Order number SO Type , Payment Date Customer , Invoice # , Date etc   Following tables were Used ARTran>SOorder, ARTran> ARInvoice, ARInvoice>ARADjust , I grouped it by Customer and when trying to get Subtotal for Customer , The result is getting Multiplied with the Number of Line Items in the Invoice Document .  How to resolve this ?   

Help with Report Designer

For all report designers out there, just sharing this tidbit in case it is helpful:With the permission changes in 2024 R1, a custom report added to the Site Map directly will be Revoked for all roles, including the report designer.From the 2024 R1 Release Notes:If an administrative user adds a new site map node directly on the Site Map (SM200520) form, the system will automatically set the form’s access rights to Revoked for all user roles. Then the user should grant access to this form to particular user roles on the Access Rights by Screen (SM201020) form.This means that the report publisher will not be able to open the new screen, even to drill into Tools-->Access Rights to assign the rights.  Instead the report publisher should use Access Rights by Screen to assign rights as needed.  If a Workspace has been specified for the report, then it will be found in that workspace.  If no Workspace is specified then it will be found under Hidden.Laura 

Hi All, I have created a tabular report and wanted to have a subtotal at the end of the report columns. I have found these ideas on the community regarding on the subtotal columns of the tabular report. Ability to create subtotal or total columns using a tabular report | Community (acumatica.com)Since that subtotal functionality does not exist in the standard tabular report functionality, do you have any ideas on how I can put a subtotal on a tabular report?I tried to hardcode the column bucket which is the account group on my report. Unfortunately, the figure is not showing on the correct bucket if there are similar cost code that has been used for multiple account group. Below are my groupings and report output for reference. In the below sample report, how can I consolidate the row lines per cost code and show the on the correct account group bucket?REPORT OUTPUT: I have tried to set the grouping for the Division to cost code to consolidate the value of the report per cost code. But

Before the recent update and enhancements for 2023 R1/R2 to Acumatica-LotSerialNbrAttribute-2023R1-ONWARD I was able to use LotSerialNbrAttributes as parameters in a GI and Report. There have been some table updates, we followed the instructions and updated the DAC with an SQL script and published the new customization package. We updated the tables, relationships, and fields in our custom GIs and reports that reference these tables. However, I receive and error when trying to use Attributes from the tables as parameters, with the previous version we did not have this problem. If I try to use one of the lot serial number attributes as a parameter it says it cannot find the field. Yet, if I deactivate the parameter and conditions for the field and only list the attributes in the results, it find the fields no problem. Example, INItemLotSerialInfo.SERIES_Attributeshttps://github.com/Acumatica/Acumatica-LotSerialNbrAttribute-2023R1-ONWARD/issues/4 Has anyone found a solution for this?xml

We just upgraded to 23R2 on Friday and noticed that some of reports are now pulling four decimal places in lieu of two. Under companies our price decimal places are still set to 2. We had this issue on another report previously and I was able to fix it by using #,##0.00 in the format field. However, that format is not working for these reports. Detailed formatting on these wasn’t previously required, so I’m not sure what changed causing the report to now have 4 decimal places or how to fix it. Has anyone else run into this with the upgrade? Or does anyone have other suggestions on how to get our reports to pull just the 2 decimal places? Thank you!