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
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.
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.
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
