Acumatica: Generic Inquiries (GI)

Summary

    •  

      Generic Inquiries in Acumatica offer a way to extract the specific data you need and present it to reporting functions for analysis. But they are not just for reports – they can be used in dashboards, made available to BI tools, and offer drill down capabilities for even more detailed analysis. The Generic Inquiry writer is a reporting tool that can access any data in Acumatica—including data stored in customized fields—and publish it, export to Excel, or format it for OData. The OData formatting option exposes the data in the Open Data Format supported by business intelligence tools like Microsoft Power BI.

    • Generic Inquiries require no prior coding knowledge, just select the data you need using wizards that walk you through the process. Generic Inquiries can be reused and modified for a variety of purposes.

    • Generic Inquiries (GI) result basically in a simple list of eg. sales revenue by month by customer. WIth the GI tool you can grab any fields of the database eg. accounts receivables, customers, time overdue, and project. You can specify conditions such as time overdue more than 60 days, and amount larger than $20K.  Running this GI will display all your customers whose accounts receivables are more than 60 days overdue, and larger than $20K. This will be shown by customer by project. Yes, you will be able to click on an AR amount and drill down to the corresponding Sales Orders (SO). You can use that GI as a widget in your dashboard.

    • You can use a lot of the already existing generic inquiries, and simply modify them to suit your needs. You will see this in the video below for invoices. 

Video on building a simple generic inquiry  ( 26 mins, 2019). Please expand to full screen size by clicking on right bottom icon.

Video on building a pivot table  ( 4 mins, 2019). Please expand to full screen size by clicking on right bottom icon.

Highlighting Generic Inquiry Data based on certain met conditions

When a certain field value in a Generic Inquiry exceeds a certain value ( eg. item price exceeds $100), that field value gets highlighted. 

The video ( 3 mins, 2019) below will illustrate this. This requires Acumatica ERP version 2019R1. 

Mass update of an entity field (eg item prices, item status to inactive, item class/category, ) 

You want to eg. update the status of the items that are more than 5 years old, to eg. inactive. 

A lot of ERP products would require you to do a lot of manual steps such as:

  1. Create a Generic Inquiry that lists all items that are more than 5 years old

  2. Export those items to an Excel sheet

  3. Import that Excel sheet to a mass update tool

  4. Run the mass update tool. 

WIth Acumatica Cloud ERP version 2019R1, those steps have been reduced to two simple steps.

  1. Create a Generic Inquiry that lists all items that are more than 5 years old

  2. Import the GI results into an Acumatica import data provider to update the status of all the relevant items

This feature is very, very useful when you have to do mass updates for certain entity fields ( item price, item class, item price discount, ..). 

See the video (3mins, 2019) below.

Acumatica Cloud ERP version 2020R1
offers more advanced GI features such as:
  • Mass Actions
    • EG. approve a set of bills​
    • Eg. Change credit term for a set of invoices
  • Create a new GI based on an existing one but with modifications
  • Choice of navigation
    • ​Add hyperlink to a master record​​
    • Add a sidepanel for a master record
  • Selection of master records​
Leam more.

Exporting Acumatica Generic Inquiry Data to Excel or PowerBi

Exporting Acumatica Generic Inquiry data to outside applications such as Excel or Microsoft Power BI.

  • Sometimes you want to add data of applications external to Acumatica ERP, to the Acumatica GI data. Once Acumatica GI data is exported to Excel or PowerBI, you can add that external data, and also add new calculations (Excel formulas).

  • You can export General Inquiry data in the following ways:

    • To Excel using Excell connection services in Excel​

    • To Excel via O'Data

    • To PowerBI via O'Data

      • PowerBI can present data in geographic maps. PowerBI is a cloud tool that lets you share PowerBI charts and maps with other people. It is part of the power applications of Microsoft. This means that is also accessible via Microsoft Sharepoint, and that it can be shared via MS Sharepoint portals and MS Teams!!

Each way will be illustrated next.

  • Exporting data to Excell using Excel connection services

    • Video (4 mins)​

  • Exporting data to Excell using O'Data​

Video on exporting GI data via O'Data to Excell  ( 6 mins). Please expand to full screen size by clicking on right bottom icon.

Video on exporting GI data via O'Data to Excel but in some more detail  ( 6 mins). Please expand to full screen size by clicking on right bottom icon.

Video on adding data and formulas to Excel data that came from a Acumatica Generic Inquiry  via O'Data (6 mins). Please expand to full screen size by clicking on right bottom icon.

  • Exporting data to PowerBi using O'Data​

Video on exporting GI data via O'Data to PowerBI (18 mins, 2017). Please expand to full screen size by clicking on right bottom icon.

Independent Software Vendor (ISV) application for two-way update between Acumatica and Excel (Extra fee)

An ISV called Velixo Reports has an application that enables you to export Acumatica data to Excel, and to update Acumatica data when you change data in Excel. 

Videos ( 4mins, 2019, 30 mins, 2018) below illustrate this. Click here to learn more. 

Internet Customer Solutions, Inc.

153 Autumn Ridge Rd, Suite 10

Morris Plains, NJ 07950

(o): 973 244 1470

(c): 973 727 9665

info@intercs.com

MS Teams: ToonSix@internetcs.onmicrosoft.com

Contact Us

  • LinkedIn Social Icon
  • Facebook - Black Circle
  • Twitter - Black Circle
  • Google+ - Black Circle

© 2018 by Internet Customer Solutions