Pivot tables are one of Excel's most powerful tools. A pivot table allows you to extract significance from a large and detailed set of data by providing a summary that lets you explore trends based on that data.
Combining pivot tables with Velixo's ability to access Acumatica Generic Inquiries provides you with a powerful, yet easy-to-use technique for analyzing your data.
Here we have set up a Velixo GIFILTER() function based on some filters we have defined for the Generic Inquiry we will be accessing:
We can then use Velixo GI() function to retrieve the data based on our defined filters:
Before we add our pivot table, we're going to give our GI function a name by clicking on the function and entering a name in Excel's Name Box:
IMPORTANT: Naming the results is REQUIRED if you want your pivot table to be able to respond to your dataset changing in size
Next, we can select our GI() function and then click on PivotTable on Excel's Insert ribbon
For the Table/Range, we enter the name we gave our data - and affix a # sign after it.
(this will allow the area to expand with more data comes from the database, when we refresh our report).
We also specify the location of the data we want to include.
Next, we define the data from the GI that we want to include in our pivot table:
Once we have modified our filters to present a new sub-set of data using our GI function, we can right-click in the pivot table and select Refresh...
To display pivot information based on the values now shown in by our GI function:
❗ When a Distribution List is used with a report containing pivot tables, pivot tables are not automatically refreshed as part of the Distribution process; you need to explicitly enable this on the distribution template worksheet. For more information, see the Distribution List article.