Reports and Generic Inquires

I am trying to build a GI that has an actual percentage margin (Sales-Costs/Sales), instead of an average. This report needs to be aggregated by day (to show the actual margin on a dashboard that is updated every time the page is refreshed)What the query does is if the AR transaction is not released, it will pull in the costs from the SO table to work out the margin.The GI currently looks at the ARTran and SOLine tables I can easily get the net sales and the costs , and can work out a margin based on actual costs from the ARtran table, however I cannot seem to get this to work with unreleased AR transactions that need to pull in costs from the SO table. Any ideas?I have tried this formula =(IIF([ARTran.TranAmt]  >  abs([ARTran.Cost]), ([ARTran.TranAmt]) * IIf( [ARTran.DrCr]='D', -1, 1 ) - (IIf( [ARInvoice.Released]=True, [ARTran.Cost], IIf( [ARTran.DrCr]='D', -1, 1 )*[SOLine.ExtCost])),(([ARTran.TranAmt]) * IIf( [ARTran.DrCr]='D', -1, 1 ) - (IIf( [ARInvoice.Released]=True, [ARTran.C

I’m trying to create a report designer that contains data for specific measurements from multiple different periods, (such as monthly, quarterly and annually.) Is there a way to filter for these time periods within a equation since the overall worksheet seems to be going off of one time period?

Hi, We created an attribute as a DateTime control type. This is attached as a user defined field to the PO Receipt. I am trying to print a report that is sorted by Item & then by the PO Receipt attribute. This is not working. If I sort by the PO Receipt Date, the sort does work. I saw an AUG forum post about using the CSAnswers table.but the data is not in that table. Is there a different table we should be linking to? Can a DateTime attribute be used to do the sorting?  

We would like to be able to have two additional columns in a Daily Sales GI. This GI will eventually become a pivot table. One column will have the sales in the US column if the ARInvoice RefNbr starts with an INV, the other will be an INT if the RefNbr starts with INT. My initial thinking for the formula is:=IIF(LEFT([ARInvoice.RefNbr],3) = 'INV', ([ARInvoice.OrigDocAmt] - [ARInvoice.FreightTot] - [ARInvoice.TaxTotal])) for the U.S. and =IIF(LEFT([ARInvoice.RefNbr],3) = 'INT', ([ARInvoice.OrigDocAmt] - [ARInvoice.FreightTot] - [ARInvoice.TaxTotal])). Both of these formulas would be in their own columns which I have as U.S. in one caption, and INT in the other caption.Date          U.S.                 INT             Total09/01/24    $12,345.00   $460.00      $12,805.00etc.The pivot portion shouldn’t be that difficult, just the part separating the U.S. from the INT.

Hello, can we integrate the PageCount and PageIndex/PageOf variables into a Subreport? We aim to develop a Footer-Subreport for inclusion in all our reports. However, we encounter a challenge where the Page-Count consistently shows as 1 of 1, regardless of the actual number of pages in the report.So Main Report => Subreport  Inside of the Subreport: = Switch( [@Locale] = 'de-de', 'Seite'+' '+[@PageIndex]+' '+'von'+' '+[@PageCount], TRUE, 'Page'+' '+[@PageIndex]+' '+'of'+' '+[@PageCount] )  

Hey all,I’m trying to work on the Lot/Serial Numbers report but its broken. As soon as I click on it, it gives an error message. I can’t even download a version. Any ideas?

Hello.We are having issues with barcodes generated from Report Designer.While we are able to scan these barcodes locally, the client cannot consistently scan them.For instance, on the production order form, they can scan some, but not others.And in General, the barcodes being printed are not crisp and clean.The client does not like the aesthetics of these slightly blurry barcodes.We have played with all of the barcode settings.  The image below is not the current settings, I am just including it here to show where we have been working. Are there any other settings/properties in Report Designer that could allow us to improve the quality of the barcodes being printed?

Is there a Monthly Sales Report by Ship to State? 

Hello, I recently made a copy of a report and uploaded it to the server and it broke both the original and new report. All I did in the new report was hide fields. The steps I took are listed below. Am I missing something critical that would make both reports break? I would appreciate any advice you can give me.  -Download original report (PM651500)-Rename it and save it to another file location (new name PM651550)-Hide fields-Upload to the server with the new report name -Create the site map location copying the original except for changing the screen name to PM.65.15.50 and the URL to match the screen name -Grant access to the new screen  Thanks!

See the picture below. I am trying to add these values to a Dashboard, and I’m not sure where to pull this data from. 

I cannot figure out an elegant way to create the equivalent of “does not start with” in a Generic Inquiry. I’ve tried these things which I would have thought might work but couldn’t for one reason or another:Create a Condition to pull the Substring of the field I was looking for and say it does not equal the value I’m looking to exclude, however I cannot use a function in the ‘Data Field’ of a GI Condition. Still use “starts with” but find a way to create the negative of that, but nowhere in Acumatica do I see that as an option. Use wildcards to compare with the % character, however it seems like it will never satisfy the start of the string and only compare to the entire string (for example, when I tested making a condition where the value does not contain T% it works as if I entered %T% and will also filter out something that has text in front of the T like “ABCT1234” which is not what I want). Add it to the relation instead of a condition and compare a substring of the parent field

Obviously there is the choice to refresh every hour, day or week. But when does that happen? Is the start of the hour/day/week, or when the refresh rate was set?

Hi ALL,Is there any function to add or subtract Period in Report Designer without using text string concatenation?+ Period is in the format 'mm-yyyy'+ Example: ARTran.FinPeriodID = 05-2023=> ARTran.FinPeriodID - 12 Periods = 06-2022=> ARTran.FinPeriodID + 3 Periods = 08-2023 Best Regards,NNT

Good day,I am looking an efficient way to create a tile that would show the LY Sales MTD.  I would assume I need a formula but nothing seems to work using the current, eg. between MonthStart-12 and Monthend-12.  Or something similar using the @Year or @today.  Nothing works correctly.Basically, it would report on the sales for the last year current month by order date.Any assistance would be greatly appreciated.Thank you@Evan G 

Hi all,My use cases: I have to grant access to a person in charge to create a generic inquiry, and this user doesn't have permission to see other generic inquiries from other people.  I tried the following step, but it did not work. This user can still see this GI.Step 1: Revoked this Generic Inquiry from this roleStep 2: Grant access to insert in generic inquiry screen Is there anything I dont know about this?Pls advice me if you have any idea.Thanks in advanced.

Hi there, I’ve modified the original AR Aging report to AR Aging by Employee report in 2022 R1. The report will generate the AR Aging based on the customers assigned to the logged in user (salesperson) only. The report works fine until the version got upgraded to 2023 R2 where it failed to show any records. The tables that I’ve added:CustSalesPeople (linked to CustomerMaster on BAccountID). SalesPerson (linked to CustSalesPeople on SalesPersonID). EPEmployee (linked to SalesPerson on SalesPersonID) I’ve added a line on Parameters called UserID to retrieve the user info by inserting the default value:=Report.GetDefUI('AccessInfo.UserID') I’ve added a line on Filters to link the parameter UserID to EPEmployee table: Despite all this, all it takes is just an upgrade to break this report. I’ve attached the report as well and hopefully someone can help me understand on why the report is not working anymore.

I want to have a generic inquiry form with 3 columns : Inventory ID, receipt number, quantity on hand.Does anyone knows how can I get these data from database?I can see this form on Adjustments screen. but can not see the details from here. 

Unlike most of the other tables, Account Details doesn’t have the tab to say what the Generic Inquiry Table for the report is. It doesn’t seem to be API-AccountDetails, as that Inquiry is missing both the beginning and ending balance. What would be the source for the Account Details data?

I am trying to create a GI that pulls the sales orders as well as the PO orders tied to them to the same GI but I am having trouble figuring out how to properly link these two tables in relations to get the data to pull properly. 

I’m creating doing label print for a shipments and I have an issue with margins. Please refer the design below:Report Page Settings:Label design:What you see when we print:Do you guys have any ideas to get ride of these margins?Thanks in advance.

I have a user who needs to log in and view only two specific dashboards, both of which are powered by a single generic inquiry (GI). While everything is functioning correctly in terms of dashboard access, the user is also able to access the GI directly, which I would like to prevent. Is there a way to allow the user access to the data from the GI without displaying the GI itself on their screen or allowing them to click into it? I tried unpublishing the GI from the UI, but this removed my ability to manage access permissions.

Hi Everyone, I am trying to create a GI for YTD Sales vs PY YTD Sales and need the tax value in another column. I found out that ARTaxTran would be the right table to use but I am not able to join it with the ARInvoice. Any help on how to add a relation between these two would be appreciated. Thank you!

Hi,We are starting to use Landed costs. I would like to make a GI that will show all inventory availability and the total cost (purchase receipt and landed cost (if there is a landed cost for the purchase receipt).Similar to how the inventory lot/serial history works. it will show receipt cost, then if you check the box it will so the cost with landed cost included. I would like that cost with landed cost included.Does anyone know the best way to get that total cost?   

We just upgraded to 2024r1 and now that I can use GIs as tables to look up against within GIs, I was trying to build some more complex reports set up that my sales team requested.They want to view, by customer, the Count of Invoices, Sum of Units Sold, and Sum of Dollar Sales for Last Year’s Month to Date, side by side with This Year’s Month to Date.I can get this information for one time period in a GI pretty easily.  So I set up two GIs for the two different time periods, and then built a 3rd GI to combine the data from both GIs by linking them to the customer table.  Everything seems to work correctly except that the Count of Invoices is coming up blank on the combined GI.  They show up on the “child” GIs properly.  Anyone have a solution to getting the count data to show up?Image of Child GI Results, Count circled:Image of the combined GI, Count Fields coming up blank: Image of the combined GI.  Note: I’ve tried Aggregating by Count, Sum, Max, <Blank>, and tried different Sch