Community
Discussion Forums
Reports and Generic Inquires

Reports and Generic Inquires

Reports, GIs, Dashboards, Pivots

1,986 Topics
7,767 Replies

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

1986 Topics

Recently active

Most replies Most views Newest first

uma47Freshman II

asked in Reports and Generic Inquires

Pivot tables - number formatting

Hi, I have created a pivot table for shipment data and it has lots of number fields that are presented as text, when you export to excel. Is there a way to format the number columns so, it will show left aligned in the table and will have the correct format when exported to excel?

3 years ago

EdongFreshman I

asked in Reports and Generic Inquires

How to include the Aggregate Field like Sum in GI Export to Excel?

Hello, How to include the aggregated field or sum fields in the bottom of a generic inquiry when exporting to excel? Thanks, Ed

3 years ago

mwong11Jr Varsity II

asked in Reports and Generic Inquires

Generic Inquiry - How to filter an GI when GI is exposed via Web Service EndPoint?

Hi,Is it possible to pass a Parameter to the GI when the GI is extended via a WebService EndPoint? Setup:I have created a generic inquiry called SubcontractsByProject, it takes in 1 Parameter called ProjectID, I have set the ProjectID with a default value of a project and it filters to that Project, so the GI is working as it should. I have extended a Webservice EndPoint to SubcontractsByProject using instructions from https://www.acumatica.com/blog/contract-based-apis-in-generic-inquiries/ The web service workshttp://localhost/entity/Subcontracts/18.200.001/SubcontractsByProject?$expand=ResultIt is returning data with the default ProjectID in the GI How can I pass a different value of ProjectID to the webservice?I tried adding a parameter in the Body like so in PostMan but it does not work ThanksMark

3 years ago

larrylySemi-Pro II

asked in Reports and Generic Inquires

Generic Inquiry-How to ignore grouping on column

Hello all,I’m working on a generic inquiry. I need a total inventory amount for each item. The result as below. The item existed in different warehouse. When I add a grouping for InventoryID. The result become to : My question is how do I ignore grouping on MSRP column? My GI setting is below.

3 years ago

mdubin66Freshman I

posted in Reports and Generic Inquires

How to print 3 copies of a form

Using the APARord view as previously documented works well. I have a client that needs 3 copies of a form, how do I set this up? I am trying to print 3 copies of the Sales Order, each copy has a slight variation to what prints. I used 2 sub-reports to achieve this, but the issue is when it tries to print a multipage sales order, the sub-report prints page 1 correctly, but then continues on with the line details onto page 2, with no logo, heading information which the client does not like. I can create 3 separate forms, place them in the reports tab of Sales Order screen. So the client wouldn’t have to click on each 3 separately to print, can there be some type of automation that when form A prints, automatically print form B then form C?

3 years ago

KrunalDoshiFreshman II

asked in Reports and Generic Inquires

Add User Defined Fields in report

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.

3 years ago

ray20Captain II

asked in Reports and Generic Inquires

Is there a way to make the columns fixed when using Tabular report with report designer?

Hello, I’ve wrote a tabular report to show customer(as row) sales in 4 warehouses (as column).The values are the total sales qty. The column right now is dynamic, that if 1 warehouse has no sales at all, this column stands for this warehouse would not show on the report. Is it possible to always show 4 columns, even if that warehouse has no sales.

3 years ago

ray20Captain II

asked in Reports and Generic Inquires

Relative datetime parameter issue--[WeekEnd] does change according to the business date in report

Hello, I’ve wrote a report and I’d like it to run on schedule to show the last week’s data.I was using “[WeekStart]-1” as the datefrom and “[WeekEnd]-1” as the dateto. And I save a template to schedule run on every Sunday. However, if the business date changes, When using the template the DateFrom would change accordingly, while the dateto would remain still. I temporarily work around this issue by assigningDateTo =DateAdd( ([@WeekStart]-1), 'd', 6) Then, the Dateto would change according to the business date. So, I am wondering did someone else meet the same issue and is there anything wrong with the [WeekEnd] parameters.

3 years ago

sarahFreshman I

asked in Reports and Generic Inquires

Need a Customer Lead Time Report

I need a report or generic inquiry to show customer lead time.Our users need to be able to enter a Starting Date and Ending Date based on Invoice Date.I would like to be able to run this report by selecting a single customer or all customers.The lead time would be calculated by subtracting the Invoice Date - Sales Order Date.I would prefer a report but I’m not familiar enough with the tables/relations yet to make this myself.I see the GI DB Ship On Time, so maybe we could modify this GI…I look forward to seeing what you guys and gals can show me.

3 years ago

michellec11Freshman II

asked in Reports and Generic Inquires

add time stamp for EDI orders.

Is there a way to add a time stamp to when EDI salesorders enter Acumatica?

3 years ago

divyangFreshman I

asked in Reports and Generic Inquires

How to adding a clickable link for excel export using generic inquiry. It should be same report export.

3 years ago

jmckinnonJr Varsity II

asked in Reports and Generic Inquires

Maxi / Aztec code support in Acumatica Designer

Is anyone aware of support for the Maxi / Aztec code barcode standard within Acumatica Report Designer? [ reference : https://www.barcodefaq.com/2d/maxicode/ ]We’re currently working through a phase of our implementation that dives deep into the Shipment Label needs of our larger customers. Some needs seem straight forward but end up at a dead end while others have slightly convoluted solutions. Oh, please note that we’re integrating with Easypost which will likely render the majority of our more typical Shipment Labels but these are not the labels I'm concerned about at this moment.Imho these larger customers' needs could be accommodated easily if we were to take ownership of the design of the Shipment label. I know there’s some decent work involved, which I've done before, but it would be a clean repeatable process.The main road block I now see is that Acumatica Report Designer doesn’t seem to support the Maxi / Aztec code barcode standard.Has anyone had the need to print this barcod

3 years ago

jagur

asked in Reports and Generic Inquires

How to create image box for external link

Help me please, how to include image to imagebox for external link, example from google photo

3 years ago

ray20Captain II

asked in Reports and Generic Inquires

How to Manage Report designer date parameters default value in week ranges?

Hello, In Generic inquiry, we have something fantastic like @weekstart and @weekendBut these does not work for report designer. Is there a way to set the report designer’s default data range as “Last week”?

3 years ago

byates

asked in Reports and Generic Inquires

Help with GI Creation

Good Afternoon, I am looking for some assistance with creating a GI. I am using the Inventory “Issues” GI as the basis. I am trying to modify it to include information from the INTrans table. I would like to bring in the following Properties:INTrans.SiteIDINTrans.InventoryIDINTrans.QtyINTrans.TranCostThen add an additional condition to only show item that have INTran.ReasonCode of “Damaged” Essentially I am trying to create a GI that shows me the total cost/qty by site and Inventory ID of all “Damages”. Using this to begin tracking manufacturing shrink/waste. Currently I am getting error when trying to view the GI so I am sure I did something wrong with the tables and relations. Attached is the GI as it is now. Thank you for the assistance.

3 years ago

VinodR

posted in Reports and Generic Inquires

Sales Order Details by Customer Report

Howdy,This is my first time attempt in the Community forum and I am still learning the product. So, correct me if I am wrong please.The report “Sales Order Details by Customer” should not display the total Qty if the UOM (unit of Measure is different). An HOUR is different from EA.The report should only display TOTALS for Line Total and Open Amount

3 years ago

wfilipiak67Captain II

asked in Reports and Generic Inquires

Trying to sort by grouping in a GI

Hi, I am trying to do a GI get Month-To-Date, Year-To-Dae & Prior-Year Sales totals for an inventory item. There are multiple warhouses setup. I am using the INTran table, with a condition of the item & the TranType of Invoice. If I sort the results in Site Order (SIteID), the results do show in Site order. However, if I set the grouping to be by SiteID, results are totaled correctly by do not appear in Site Order.Is there a way to sort by the grouping conditions?No grouping:With Grouping:

3 years ago

ray20Captain II

asked in Reports and Generic Inquires

Can I directly get item's last sells date?

Hello, I need to get the infomation of such items: We have qty on hand>0 And We didn’t sell for a long time like 90 days. I can get this done by joining INsitestatus and ARTran table. The key of my solution is that Acumatica will by default get the maximum of trandate after I group by inventoyID And I can use a “second filter” on GI results like below to get the information I wanted. It is a solution.However， it is quite time consuming because ARtran table is quite huge.Can I replace ARtran with another table (Where I can still get the last sell date infomation and join much less lines) ? Or is there a better way to fulfill my need.

3 years ago

arlinewelty93Jr Varsity III

asked in Reports and Generic Inquires

Generic Inquiry: Return substring from a currency value (remove auto-inserted trailing 00s)

The mission with this generic inquiry is to show the payment amount value with the decimal stripped out. I’ve tried a few formulas but Acumatica seems to want to add 2 trailing 0s when I do that. Example: The goal is, instead of showing $8321.05, to show: 832105In SQL I’d want to return a substring, or trim 2 characters from the right side of the string. Here are a few examples of what I’ve tried: Try to get the field to display as a schema field ‘payment amount’ Try to get the field to display as schema field ‘doc description’ Try to return from the right side of the string, -2 positions: =Right(CStr( Replace( [APPayment.CuryOrigDocAmt], '.' , '' ) ), -2) Try to cast the field as a decimal =Replace( CDec([APPayment.CuryOrigDocAmt]), '.' , '' ) Try to cast the field as a string =Replace( CStr([APPayment.CuryOrigDocAmt]), '.' , '' ) Any suggestions on this? Acumatica really seems to want to add those zeroes!

3 years ago

ArleneFreshman II

asked in Reports and Generic Inquires

Report Designer - Macbook

Can the report designer run on a Mac book? If yes, is there any special set up? Thanks,

3 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

wfilipiak67Captain II

asked in Reports and Generic Inquires

Decimal/Number formatting in a GI to excel

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

3 years ago

greglangVarsity I

posted in Reports and Generic Inquires

Populate Current "weekId" in GI Parameters

Does anyone know a workaround to populate the current or last WeekID (EPTimeCard.WeekID) in a parameter on a GI. I have a few use cases for this and in order to use the GI’s for Dashboards I need this filter to be applied. As a workaround I know I can use weekstart as its a field on EPTimeCard but this is less user friendly as users would be able to pick dates other than week start dates from the dropdown. For example you can use @today or @weekstart-1 for a specific date field but I have been unable to figure out the logic to do this for a WeekID if its even posable. Thanks in advance for any suggestions. Attached is a GI I’m using to show missing timecards.

3 years ago

CaiFreshman I

asked in Reports and Generic Inquires

Cai

Hello,I m a new user of Acumatica, I would ask the other more experimented users, is there some way to retrieve data from the cloud database down to Excel like I was used to do with my MS SQL Server database, via Power Query fonctions (I had of course the Server address, username and password to be authorized to get in) ? thank you very much. Cai

3 years ago

TimRodmanSemi-Pro III

asked in Reports and Generic Inquires

Pulling attachment contents via OData

I’m trying to access attachment contents over OData so I can pull Stock Item images into Excel. I tried PX.SM.UploadFileRevision and PX.SM.UploadFileWithData, but the UploadFileRevision.BlobData column doesn’t appear in either a Generic Inquiry or in the OData feed. Does anyone have any ideas on how I can access the raw file attachment contents? Note in these screenshots that there are 5 columns in the Generic Inquiry, but only 4 columns appear in the results:

3 years ago

Page 77 / 80

Badge winners

tarashas earned the badge Easy talker
hkabirihas earned the badge Import and Export Scenario expert
aiwanhas earned the badge Acumatica expert
rfrantzhas earned the badge Easy talker
WillHhas earned the badge Easy talker

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.