Reports and Generic Inquires
Reports, GIs, Dashboards, Pivots
- 1,421 Topics
- 5,560 Replies
Recently we had the requirement to create a Generic Inquiry and Report with parameters to filter on a date range using both date AND time to account for multiple labor shifts. I could not identify a schema field that included a time component, so I created two additional string parameters for the time components. I then parsed the time parameter and added the corresponding number of minutes to adjust the start and end dates in the GI/Report condition. I also had to make an adjustment for UTC, which works differently between the GI and the Report. GI search screen:Condition on the GI:It seems a bit hacky, but it does do the trick. Has anyone else encountered this requirement and possibly found an easier method to provide the functionality?
When accessing a GI through OData in PowerBi or Excel, I get the following error message:DataSource.Error: We couldn't parse OData response result. Error: Unexpected end of file has occurred. The following elements are not closed: feed. Line 6, position 120.Details: DataSourceKind=ODataDoes anyone know what this means, and how this can be solved?
This may be a noob question, but how can I set a condition on the generic inquiry for a data field based on a formula? See below.In the results grid, I have a formula that is calculating GP%. I want to set a condition on the GI to display only projects with a GP% lower than 25. How can this be done on the GI? BONUS: Alternatively, if someone could help me figure out why this business event is not firing, I wouldn’t have to filter the GI in the first place.
Hello,i am trying to figure out if there is an eas way to create table lines. I tried working with panels, but experienced issues with them not always growing with the textbox, which made it problematic. Is there any good way to do it?Greetings
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.
Hello, I am trying to write a GI to get the customer’s sold since information like the below screen. You can find my share in the below post: My solution almost works fine, but I find it difficult to get the “last sale qty” and “last sale price” by item using GI. The last sales date can be got because the default aggregation is MAX, the max date is exactly the last date. However, the max(orderqty) is not the last order qty, and also the max(unit price) is not the last unit price. so currently, I am using the AVG unit price and Sum Order qty as a workaround, and it can be accepted by user. But still, I am eager to learn how can I get the data using a single GI? Thank you.
Per our internal Acumatica help wiki, I should be able to download the Acumatica Report Designer from http://acumatica-builds.s3.amazonaws.com/index.html?prefix=builds/<buildNumber>, but upon searching for my build number, I encounter a directory with no items.Has the Builds URL changed and is there an alternate URL to download this directly from Acumatica?
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.
Generic Inquiry in Acumatica is an easy tool to fetch the data that resides in Acumatica database tables. This topic covers the steps to create a simple Generic Inquiry. This topic will be useful for non-technical Acumatica users to create a Generic Inquiry in Acumatica.Steps to create simple Generic Inquiry:Open the Generic Inquiry Screen Give a name to the Generic Inquiry for example “Sales Order Data”, as shown in the below screen shot.Add an Acumatica Table - for example SOOrder for Sales Order dataAs shown above, search for the table and select the required table for data. SOOrder is selected in this case. Search for the table SOOrder and select: Add fields to the “Results Grid” tab Save and View Generic Inquiry data Optionally add condition(s) to filter the data, if required, under the “Conditions” tabFollow the below steps to add Conditional filters to the data. Click on View Inquiry button to preview the Generic Inquiry data output Please Note:It is advised to have SQL knowl
In Report Designer, we have a text box with several lines of text. The requirement is to adjust the font color or bold property for only a few words in the middle of this text string. Like this.We reviewed the Format function, but didn’t see an option to control these properties.Anyone know a way to do this?Thanks,Andy
Does anyone have any ideas on how to add backordered items to an Invoice? These would be zero dollar lines for visibility purposes only.We have the Add Zero Lines for Items Not in Stock checkbox checked on the Sales Orders Preferences (SO101000) screen so we get the following behavior: 1. Create a Sales Order where the first 2 lines are available, but the 3rd line is not available: 2. Create a Shipment and all three lines are carried onto the Shipment, even though the 3rd line is not shipped: 3. Create an Invoice and only the first two lines are carried over. The 3rd line disappears since it didn’t ship:
I am using a GI as data source, and trigger business event to email sales that some shipline is not shipped in full. I will list all shortage ship lines in the email body. It is working fine with the below method. However, the sales are complaining it is not looked nice, Like below Can I make each field display in fixed length, just like what is showed in Report designer?
I have a Generic Inquiry that sums transactions over a date range specified by Required Parameters StartDate and ThruDate with Default Values of @YearStart and @MonthEnd-1 respectively:Generic Inquiry ParametersAs I needed the ThruDate in Excel exports of this GI, I added it to the Results Grid:Generic Inquiry Results GridThe unexpected result is the Parameter (Thru Date) and the Result column (Statement Date) get different values when the Inquiry is executed:ResultsIf the Parameter is manually changed in the results screen, the dates match.The value in the resultant column is the date being applied (not the defaulted date displaying at top) verified by the calculated values and manually entering the dates.
The "Is Null" in report designer visibility setting does not work, then how can I filter the data I want?
Hello,Thanks to Gabriel’s help. I can get the data I want in GI using a second filter. like below: The key in the join condition is that “INitemsitehistday” might only have qty in record，no sales record So if “left join” used， might get a joined line with INitemsitehistday.Sdate as Null. This filter out is working well in GI.However if I would like to apply the same kind of second filter by using the visibility section setting in report Designer. The “ Or [INItemSiteHistDay.SDate] Is Null” does not work. Then can somebody help me to get the data I want in Report designer?1， Not sold in 90 days2， never sold， which left join get a NULL value
Hi All, Is there a way in Acumatica to bulk update a particular field in all the Business Accounts/Customers/Vendors? If yes, can we perform bulk updates on the records filtered by a certain criteria?For example: Change the Owner field for all Business Accounts in Colorado State with Status “Active”. Any help will be greatly appreciated.Best Regards,Amruta
Hello all,Just curious how we could find the generic inquiry behind any screen. For example, the 'Account Summary' Screen: ScreenId=GL401000.I ask, because the mission is to create a dashboard tile showing the ending balance for a certain GL Account where the record period = the current period.I’d like to understand where I can copy/edit/manipulate the data I see on the Account Summary screen. I tried:Navigating to the screen Customization button (looking for ‘Edit Generic Inquiry’)But ‘Edit Generic Inquiry’ is not an option.What is an efficient way to get the info in the grid below, assuming I can create the parameters for Ledger & Period?Thank you for any input.
Hello everyone, I am in the process of creating a generic inquiry that shows an inventory valuation by date. The reports that are pre-built with Acumatica only show by financial period. The most important column is the quantity on hand (which is used to calculate the value of the inventory).Here are my joins: Most of my joins are on inventoryID or siteID.For quantity on hand I am using this:[INLocationStatus.QtyOnHand]The problem is that the quantity on hand that I am picking up is simply the latest quantity on hand, rather than the quantity on hand as of the date chosen in the parameter: Does this seem to be a conditions/parameter issue? I am in the process of testing with trial and error on the conditions and parameters. Maybe there is another table I should use for qty on hand? Thank you all for any input.
Has anyone been able to take the shipment (or another similar transaction) created date/time field and only display the time? I was able to use the CRCase table trick to get the date column to display the date and time, but the client would like the time in a separate column. When I try to take the right most characters from the field, it displays the time in UTC time instead of our current time zone like the date/time field displays .
Hello, I know “Date to” and “Date From” are more flexible. But sometimes, the data are just required in terms of “Month” range. So, to make the user just select 1 time, I am looking for a way to set a single parameter, like Month with data source “Jan, Feb, Mar,,,,” and so on. I’ve tried the Fin Period, which, of course, currently set exactly as the Month range. However, Fin period of My designed GIs can not be selected by user, it is a text input format, which is not convenient for user to use. While the some of original Screens can be selected. So, can anyone guide me how to do it like the above?
Hi All,I am looking to find a way to use the total sales amount at the bottom of Daily Sales Profitability report within the line to calculate what percent is the customer contributing to the total sales for a period of time.Ultimately I am looking to achieve the below: Customer Net Sales % to TotalABC Company 100,000 10% Total Sales 1,000,000How would this be done? Or is there a report that already does comparison of customer to the total sales?Kind regards,Calvin
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
PowerBI: credentials failing on OData connection despite successful XML view of OData Generic Inquiries
Hello all,Looking for some OData insight here. The goal: pull a Generic Inquiry through to Power BI.I have the XML page working great with the "Odata" test URL following the naming convention: http://<application site domain>/odata/<company name>I assumed that was the “test” that would ensure that I’d be able to connect to an OData endpoint within an Acumatica instance.However, when I attempt to "Get Data" through Power BI and load in the URL with basic authentication, I have tantalizing visibility to the Inquiries, including the one I want to use, but I'm unable to get past the authentication step in order to finish loading data from the data source.Perplexingly I can't seem to use my regular credentials using Basic authentication, at any level. Here’s the XML. This loads after prompting for Acumatica credentials. However, when I use the same URL and enter the same credentials into “Basic” authentication I’m getting the credentials error: From looking online, I’m curious
Hi, We are trying to print a PDF document, a 2 page Terms & Conditions, after the Order form prints. I have been able to convert the PDF to jpg & embed the jpg in the form. But the T&C prints condensed & does not fil out the page. I am wondering if there is a way to designate a PDF to print or if there is a better way of accomplishing this. Should we store the Doc in the Database rather than the form? Any other ideas? This is a cloud environment (AWS) .Thank you, Bill
Login to the community
Social LoginLogin with your Acumatica account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.