Reports and Generic Inquires

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?

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? 

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.   

 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

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. 

How does one create a fully new report? I’m making a tweaked version of an existing report, but need to create a new report on the site, and I’m not sure how.Thanks for any help.

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?

Hi all,Has anyone seen this error? Thank you,KT  

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. 

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

We have a custom GI which is showing this message in the status bar:Same query exists in two tenants but only one of them (the one with more data) shows the message.

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 All,We recently fixed a data issue for one of our clients and we need to adjust some reporting on the Dashboards.Basically, every other widget on a Dashboard recognizes negative values that are calculated as negative based on below Formula: Yet when using this Data column from the Inquiry in the Pivot Table, it treats it as positive and adds to the total rather to substract from it.What do I need to do that these values are recognized as negative by the Pivot Table?It’s a very basic Table: ​​​​​​​Thank you!

Is there a way to create a filter on GI to show the last seven days, last month, and to forecast next month? I know there is the @month and the @today parameter but couldn’t find a way to add days to it. I tried @today-7 and it does not filter the last seven days. 

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

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

Hi Everyone, I’m trying to understand formulas in GIs. The documentation does not provide guidance on how to use formulas. There is a Report Designer guide that lists each formula and how it’s used (Isnull() on p. 63) but it lacks detail about how data types affect expressions within the formula. If you know of another resource, please share :-) On to Isnull(). Here’s the documentation.I’ve written a simple formula with unexpected results:=Isnull( CStr([ARInvoice.CustomerID]), 'NULL')in row 1, ‘9698’ does not match either of the referenced columns. Why?My ultimate goal is to have this algorithm in a way that Acumatica understands:If CustomerID is null, return ReferenceID, else return CustomerID 

      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?

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

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.

I am creating a GI and need to pull for a Date, however the field i need to use is a DATETIME field.  I feel like it should be easy to convert it to a DATE field only but it doesn’t seem to be.  Any hints?  I’ve tried every scenario that i can think of and google...I’ve added things to the Data Field, Schema Field, Style etc.  i can do a LEFT(Start Date,11) but then when i try to add a condition that it pulls @Today, nothing will show.  I am assuming it’s b/c of the timestamp attached to the Date.  

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: 

Is there a way to have the SOOrders.CreateDateTime show both the Date and the Time in a Generic Inquiry.

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. 

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.