Skip to main content

Receipt Summary Browser Using Pivot and Generic Inquiry Feature In Acumatica 6.0/6.1

In the latest release ver 6.0/6.1 there are quite lot of new features that we are excited about, today we will discuss on Pivot and how you can extract data from Acumatica and be able to slice your data till it makes sense.

Pivot tables is as extension to Generic Inquiries, in version 5.0 we had capability to extract data via GI but didn't quite had the capabilities to sum or group the data easily or get a count across data elements, pivot makes that happen.

Here we are demonstrating capability of being able to view summary of all receipt received through various payment methods cash, check, credit card or On account terms.

Step 1: Generic Inquiry(click image to view large size)

We created a new generic inquiry with relevant tables to extract data of payment information from Acumatica using tables
PX.Objects.AR.ARPayment 
and PX.Objects.CA.CATran

Get all fields that are required for viewing with your conditions and sort order, view the inquiry results from the result window click Customization menu and Pivot tables to create a new pivot.

Step 2: Pivot Screen (click image to view large size)

In Pivot screen we will define the rows, columns, values and filters field, for this example we will use "Payment amount" as values and "Payment Method" as Columns and "Payment date" as rows.

Make sure the payment amount field properties is set to calculate Sum instead of Count, save your pivot and click to view.

Step 3: Pivot View (click image to view large size)

You can now view the data which automatically sums up based on payment date and spreads it across columns based on payment methods.

It gives you clear picture of payment amount received daily/weekly/monthly basis, sum of total amount per payment method and grand total.

You can also apply other filters to narrow down your results, if you click on the amount you can drill down to transactions too.

With this new feature we were able to satisfy our customer needs to view receipt summary and it took us an hr to put together GI and pivot table to demonstrate true value of Acumatica Cloud ERP and it's powerful features.

Note: Pivot screens can't be added to favorites however it can be shared and added as navigation menu and control the security of the screen.

If you need any other assistance with Generic Inquiries or Pivot features let us know, you can reach us out for Acumatica Customizations by visiting our website InfoSourcing Inc

Comments

Popular posts from this blog

Acumatica JewelShop - JCK Las Vegas Convention for Jewelry Business

Acumatica JewelShop , new plugin for Jewelry wholesale business and custom jewelry manufacturers. Visit us at JCK LAS VEGAS - Booth B50042 | May 31 to Jun 2nd. SANDS EXPO & THE VENETIAN LAS VEGAS, NV https://lasvegas.jckonline.com/ Visit our website for product information  Visit our website for more information about Acumatica JewelShop

Acumatica Snapshots - Uploading and restoring large files

In Acumatica ERP, you can create company snapshots containing your company’s full or partial data. Snapshots that can be saved locally in the same database, or at a specified location. You can import and restore snapshot to another company, in this case test company, it's pretty handy tool very useful during implementations, customization and support/troubleshooting while you work with data. You will occasionally run into few technical issues with snapshots, the newer version now needs you to keep all users logged out and in maintenance mode to take snapshot, link to blog article here. Today we are going to discuss how to export and import snapshots which are larger in file size of above 1GB, this is not a limitation from Acumatica and more to do with constraint and default value set in IIS (Internet Information Server). IIS by default allows uploading of a file size of 1GB and it limits the upload process and it can be tweaked to upload upto 4 GB file size and that's

Acumatica User Defined Fields/Values - UDF using attributes in 2018 R2

In 2018 R2 version Acumatica introduced new feature called "User Defined fields" in short UDF's. These fields allow users to configure existing document screens to capture custom data per document basis. New features like this really helps partner/customer to configure business workflow process, this capability built on a framework can now enhance existing documents like Sales order, purchase order or other document to collect custom data at transaction level using attributes. For more information on which documents/screens you can collect UDF's refer to Acumatica help  In this example I have created few custom data that we would like to accept in our Print industry, this works pretty well with our Acumatica plugin PrintShop for Screen printing and embroidery companies, as business do accept different type of data and allows us to do minimal customizations. If your accepting custom data related to art work, then you can create those attributes ahead of