Pivot Tables Within MYOB Advanced
We usually associate the concept of Pivot Tables with Excel, these nifty reporting tables allow data to be grouped and aggregated easily. How great would it be if you could use the power of pivot tables directly in your ERP system? Well, in MYOB Advanced you can!
Pivot Table Functionality
MYOB Advanced introduced Pivot Table functionality back in version 2017.1 The pivot tables provide users the ability to reorganise and summarise information to provide them different perspectives of the data. The tables are quick to build, have drill down functionality and can be easily exported.
A pivot table is created from a Generic Inquiry, which is another reporting function in MYOB Advanced that offers a way to extract the data you require and present the data in a form that can be analysed easily. Think of a Generic Inquiry as a custom tabular view of data that you are specifically interested in.
The Generic Inquiry data can be used in dashboards, pivot tables and published and extracted into Excel. Data within a Generic Inquiry can also be accessed externally to MYOB Advanced via the ODATA connector.
Below is a screenshot of a customer transaction Generic Inquiry.
Building Your First Pivot Table
Once you have your Generic Inquiry ready to go its time to start with your Pivot Table.
The Pivot Table function allows you to sort, count and total data. As the Pivot Table stores information separate to the Generic Inquiry you are able to change the structure of the Pivot Table without changing the Generic Inquiry data. This allows for fast and flexible report creation.
You can access the Pivot Table functions via Configuration > Customisation > Pivot Tables.
Using the global search bar, searching for ‘Pivot Tables’ will also get you there.
Once you are on the Pivot Table form, use the Screen ID field navigate to your Generic Inquiry or any of the pre-existing Generic Inquiries.
Give your Pivot Table a name and start dragging your fields from the left pane into the various sections. This should feel familiar to you if you have created Pivot Tables within Excel.
Its worth noting that while a field is selected you can modify the Aggregate type on the right hand properties pane. Changing calculations such as Sum, Average or Count. You can also rename the caption on the field here!
Pro Tip – Column names that make sense will help people understand your report and data!
Once you have finished adding your fields you can save the Pivot Table and click View Pivot from the top menu bar. Remembering you can easily come back and edit the Pivot Table once you have reviewed the output.
Your Pivot Table will output in a familiar format and you can export directly to Excel if you wish, but if you stay within this view you have the power to directly drill down into the source records by simply clicking on any of the values in the Pivot Table!
You can even click and drag columns to new positions directly within the view!
Pivot Tables are just one of the many amazing features within MYOB Advanced. Please reach out to speak to one of our consultants to learn more about Pivot Tables or about MYOB Advanced in general.
