Community
Discussion Forums
Reports and Generic Inquires

Reports and Generic Inquires

Reports, GIs, Dashboards, Pivots

2,010 Topics
7,879 Replies

When you subscribe we will email you when there is a new topic in this category

2010 Topics

Most views

Recently active Most replies Newest first

ray20Captain II

asked in Reports and Generic Inquires

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

3 years ago

cdiazJr Varsity I

asked in Reports and Generic Inquires

Understanding GI Formulas. Isnull().

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

2 years ago

TharidhiPSemi-Pro I

asked in Reports and Generic Inquires

Display Attribute Values from Stock Items to Report

I need to get some attribute values from the stock items form to a report (Modifying Sales Order Report SO641010.rpx). What are the tables to be joined in order to show the necessary value from the Attributes table to the report?

2 years ago

hayleehicksVarsity I

asked in Reports and Generic Inquires

Created Time in Generic Inquiry

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 .

6 months ago

ray20Captain II

asked in Reports and Generic Inquires

How can I put "Month" as a single parameter in Generic inquiry or Report Designer

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?

3 years ago

vpanchenkoTeam Lead, Reporting&Core

posted in Reports and Generic Inquires

OData v3/v4 plans and migration — need your feedback

Dear partners and customers,Currently, we have two OData endpoints:~/odata, which is GI-based, uses the OData v3 protocol, and has been with us since the 5.1 release; it is widely adopted in various ISV solutions ~/odatav4, which is DAC-based, uses the OData v4 protocol, and was released in 2020R2.The OData v3 protocol slowly becomes obsolete in favor of OData v4. For example, some .NET client libraries stop supporting OData v3 at all, and .NET Core/.NET 5.0+ supports OData v3 only in the compatibility mode with some restrictions.Because of that, we’d like to collect feedback on the OData v3 endpoint usage (GI-based, located at ~/odata route) and ask some question on what protocol-related features you actually use. As you may know, the default payload format for OData v3 is Atom XML (Accept: application/atom+xml or $format=atom). OData v3 also supports JSON in two forms: a regular one (Accept: application/json or $format=json) and a verbose one (Accept: application/json;odata=verbose o

1 year ago

ray20Captain II

asked in Reports and Generic Inquires

Share a formula to get the current FinPeriod to be used in Generic inquiry

Hello, Irina previously shared a trick to get the current finperiod as default in GI. This worked fine in 2020R2, but it does not work in 2021R2. Normally, we just only want the current period data.The below trick is working. If someone has better solution, please also share. Thanks. Although it shows 06-2022 in screen, it actually stores as 202206 in DB, so I am using the below formula to calculate current fin period.=IIf( Month( Today())>9, Concat( CStr( Year( Today())),CStr( Month( Today()))), Concat( CStr( Year( Today())),'0',CStr( Month( Today()))))And then put it in GI conditions.

2 years ago

Ana

asked in Reports and Generic Inquires

Available Quantity vs On Hand Quantity

We are trying to download a report of all Available Qty but can only seem to find a report that shows On Hand Quantity. Any suggestions? I want the available qty as that shows everything that is available to sell where as the on hand quantity will show all inventory (even inventory allocated to a sales order).

2 years ago

jzhuFreshman I

asked in Reports and Generic Inquires

Date Filter in a Generic Inquiry

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.

9 months ago

CalvinJr Varsity III

asked in Reports and Generic Inquires

Count +1 Formula In Report Designer

Hi All,I am looking to count the total line in Sales Order Summary. I do not know what would be the formula to have the text box to count 1 to 10 or more. I used Count but it just don’t add and give me random number. Ultimately I need a formula that simply count number of lines in the report and is not affected but the order itself.Kind regards,Calvin Law

2 years ago

TimRodmanSemi-Pro III

asked in Reports and Generic Inquires

Why is OData $select parameter so slow?

I’m wondering if anyone at Acumatica ( especially @vpanchenko ) has some insight on this.I’ve noticed this behavior before, but this is the first time that I got out my stopwatch to time it and verify.I’m on an Acumatica site, pulling data from a Generic Inquiry over Acumatica OData, using Power BI Desktop. The Generic Inquiry is a Generic Inquiry of AR Invoice Lines, pulling from PX.Objects.AR.ARTran and PX.Objects.AR.ARInvoice. The build is 21.209.0034. I tried two different methods of accessing the data: First MethodConnect to the Acumatica OData URL, then browse to the Generic Inquiry within the Power BI user interface, then remove all columns except DocType, RefNbr, and LineNbr using the Power BI user interface. The end result is three columns and this is the Power BI code that gets generated.let Source = OData.Feed("https://mycompany.acumatica.com/odata/production", null, [Implementation="2.0"]), #"PBI-ARInvoiceLines_table" = Source{[Name="PBI-ARInvoiceLines",Signature="tab

1 year ago

DelFreshman I

asked in Reports and Generic Inquires

Display Date and Time

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

1 year ago

Jennifer DouglasVarsity I

posted in Reports and Generic Inquires

Acumatica Reports - Consistent Formatting for Beginners

Things I learned as an Acumatica noob.As a brand new to Acumatica developer, the thought of creating and modifying reports was terrifying to me. But with a deep breath and fingers crossed I made the leap and have learned so much, including to not only have more faith in myself, but also in the world that is Acumatica. Report creation and modification doesn’t have to be scary. Acumatica Report Designer provides everything you need to modify or create a new report. In this post, I’ll be sharing a few tips that I learned while modifying my first report. To begin, I installed a local instance of Acumatica, along with report designer. This allows me to work with my reports locally before publishing into either my test or production environments. Additionally, having a local install gave me access to one very important piece used for report aesthetics and consistency, namely the TemplateReport.rpx file. Often, I will be tasked with modifying an existing Acumatica report to meet a cer

1 year ago

jeremyg47Freshman I

asked in Reports and Generic Inquires

Simple Month End Inventory Valuation

I have currently been using the historical inventory valuation for reporting month inventory values. This report needs to tie to the the balance in my inventory account. To accomplish this, I am having to the use a start period that is the beginning of time to the current period. The report size gets larger and larger every month due to an additional months data being added. Are there any reports that can be run that do not include each months transaction details? Ideally, I would only need 4 columns:Inventory ID, Description, Quantity on Hand & Value for any given historical period. Any input would be appreciated!

2 years ago

Michael NdungiVarsity I

asked in Reports and Generic Inquires

Retrieve deleted files

I created a generic inquiry to show all credit notes as per serialized sequence.On viewing the Inquiry I noticed 9 of them are missing, maybe they were deleted, this may cause problems in auditing.Kindly assist on how I can retrieve them. Urgently need you help.All responses will be appreciated.

1 year ago

ASamson21Freshman I

asked in Reports and Generic Inquires

How to configure Page Footer only appear on last page

Does anyone have any ideas on how to configure Page Footer only appear on last page? I have set the behaviour to Print on Last Page as “TRUE”. However, when the report are more than 3 pages long, the footer shows up on the succeeding pages except for the first page. Below is the screenshot of my report designer configuration for the Page Footer section. Thank you in advance for any help. Anne Samson

1 year ago

bob39Freshman II

asked in Reports and Generic Inquires

use of IIF statement in Generic Inquiry with Today()

I have an “On Hold” AP Bills Generic inquiry to present same in a dashboard. I tried to use the two example expressions in the Style column of a Generic Inquiry for APInvoice.Due.Date to shade invoice due dates pink (bad) when an AP Bill is past due. However, what I’m getting is pink (bad) only when the DocDate and DueDate are equal dates or when the DueDate is blank (example: for a Debit Adj). Here are the two Style IIf statements that I tried. =IIf([APInvoice.DueDate]>Today(),'good','bad')=IIf({APInvoice.DueDate]>@Today,’good’,’bad’) Looking for the correct Style expression to shade the APInvoice.DueDate pink (bad) when an AP Bill is past its due date. Thank you.

2 years ago

iancook36Freshman I

asked in Reports and Generic Inquires

Rename data field in a Generic Inquiry

What do I need to do in a GI to replicate the functionality I would use in SQL for example to haveSELECT Contact.DisplayName [Contact Name] from Contact where ContactType = ‘LD’I want my GI column name to be Contact Name and not DisplayName.Thanks

3 years ago

ray20Captain II

asked in Reports and Generic Inquires

Is it possible to sort by calculated value/field in report designer

Hello, The requirement is as below. Supposing I am designing a report with report designer, and this report would show all the items ordered in today, and group by itemID. I would like to show the lines sorting by orderQty of each line item descending. Here, the order qty of each line is a calculated sum value, I did not find a way to sort by the sum value. Can someone give me a hand on this requirement? I know there is a workround, if I export this report into excel, and it is very easy to sort by the order qty field. However, I still would like to know is it possible to just display the lines in the sequence of sum value directly.

2 years ago

alicia23Jr Varsity III

asked in Reports and Generic Inquires

Passing subreport variables

Does anyone know if passing variables from subreport to subreport is a possibility or passing a variable back to a subreport from a main report? Thanks

3 years ago

Ellie

posted in Reports and Generic Inquires

Ability to add sort to filters

A sort function should be added to predefined filters. When defining filters for any grid or generic inquiry, a multi-level sort order can be predefined, thus allowing user to essentially create multiple “views” from a single grid/inquiry. This will also solve an issue when create complex aggregate formulas which are not supported in the GI sort tab, and hence presorting the GI is not possible in those cases. A more minimal implementation of that would be to allow the regular sorting to be saved and published to all users, just like the sizing and order of the column configuration currently is.

11 months ago

ray20Captain II

asked in Reports and Generic Inquires

Why sorting in report designer does not work? Is it only effect for detail sections?

Hello, I made a simple rpx like below.I would like to sort the lines by inventoryCD.I don’t know why. is it caused by the relationships below?Or is it that the sorting did not work for group sections? The result is not as expected. It is not sorted by inventoryCD.

7 months ago

TimRodmanSemi-Pro III

asked in Reports and Generic Inquires

Assign Function in Report Designer

I have some questions about the Assign function in Report Designer. You can see the documentation here, also pictured below:https://help-2021r2.acumatica.com/Help?ScreenId=ShowWiki&pageid=83fa0867-0eaa-4d93-9f9a-92c0f1b384d3 My questions:1. Is there a way to use Assign in a TextBox and return a result at the same time? I tried using a semicolon like this, but it didn’t work:=Assign('$MyVariable','some value');'Here is My Variable: '+$MyVariable2. In the Help pictured above, what is <nowiki> doing?

2 years ago

TharidhiPSemi-Pro I

asked in Reports and Generic Inquires

Line Spacing Issue for Acumatica Report Designer Element

Hi, I have a requirement where one field must be printed with some amount of line spacing between them, if the field length is long and uses many lines. Is there an option in Acumatica Report Designer to increase line spacing or any other workaround to achieve space between according to the layout would be appreciated. Thank you.Line Spacing Must Increase for the Field Value Printed

2 years ago

TharidhiPSemi-Pro I

asked in Reports and Generic Inquires

Calculating Total of Column in Header Section

I am fairly new to Report Designer and I have a requirement where they want the total of a column in the header section, this should print only on the first page and values consideredshould be where page index = page count, how can I fulfill this?

2 years ago

Page 2 / 81

Badge winners

Yulia Steinberghas earned the badge Reports and Generic Inquires expert
CorbinHursthas earned the badge Easy talker
Laura02has earned the badge Innovator
APistoriushas earned the badge Easy talker
tarashas earned the badge Customizations expert

Show all badges

Terms & Conditions Cookie settings

Sign up

Already have an account? Login

Social Login

Login to the community

Social Login

Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.

Enter your e-mail address

Back to overview

Scanning file for viruses.

Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.

This file cannot be downloaded

Sorry, our virus scanner detected that this file isn't safe to download.