- Do you use one of the more recent Acumatica 6.x versions?
- Do you export your Acumatica data into Excel so that you can use pivot tables?
- Have you found you can’t drill into the underlying transactions?
If so, you need to try out pivot tables inside Acumatica! They’re great, easy to use, and the best part, you can drill-down into the information.
Here’s how:
First, go to: System/Customization menu.
Now, you will see a field called Screen ID. If you have built an Acumatica Generic Inquiry (GI), the Screen ID represents that.
You can now turn that inquiry into a multi-dimensional analysis tool. Choose the Sales Profitability Analysis GI. The fields requested in the GI are shown in the left column. Drag and drop and fields into the appropriate four areas to the right depending how you want to render your report. (Just like Excel!)
Note that when you select a field in an area, like Margin, you can indicate how you would like to format the field, aggregate it, etc. in the properties pane to the right.
Click on the View Pivot from the toolbar and you get the following results. Let’s look closer at our customer, Agrilink Food (underlined below), by clicking on the + sign next to their name.
Now we see that Agrilink bought six different products from us and we can see the total sales, margin amount and average margin % by product of all the underlying orders they placed.
Say we want to look at the orders containing Hot Dog Buns (underlined below). Just click on the blue hyperlinks in the columns to the right.
Here, we see quite a few orders were placed containing Hot Dog Buns for the period of time (upper left corner). I can now click on a specific Sales Order.
This is great! You can drill-down into specific transactions right from you pivot tables. If you still want to use Excel with this data and create graphs etc., just click on the Excel icon at the top of the table (see below). This will download the data into Excel.
I hope you try this and save a few hours in your day. The best part, all of this is done right from your favorite web browser with Acumatica!
If you have questions or need some assistance, visit our support page for more help.
