Acumatica Generic Inquiries and pivot tables make it easy to extract the data you need for use with reporting and analytics applications. With Generic Inquiries, you define the specific data you need and use that data as the source for reporting functions and deeper analysis. Watch the video or continue reading below to see how you can use GIs to organize and view your data in a detailed way with this firsthand demo:
Acumatica Generic Inquires & Pivot Tables Video Transcript
Hi, my name is Tom Costa with Acumatica and today we’re going to take a look at the new pivot table feature available in Acumatica 6.0.
At the start, when you start looking at pivot tables you really have to start as, well what data do you want to use the pivot table to analyze? So, within Acumatica 6.0 we’re going to be able to use our Generic Inquiries as really being the starting point for our pivot table function.
So, let’s go ahead and go into the Generic Inquiries and we’ll pull up one of our standard ones that we have, invoiced items, and invoiced items is really a collection of sales data – customer, items, quantities, sales numbers, sales rep, things like that. So it’s really a great starting point for one of our pivot tables.
So, once we call it up, we’ll click over to view Inquiry. So now we’ll be able to take a look at all of the actual data that we’ll have available to our pivot table and then under Customization we’re going to say, “well we’re going to make this into a pivot table.”
So, very easy to be able to determine what information we want to analyze and then get it basically into a pivot table configuration screen, which is where we’re at right now.
So, when we’re here, you know really the first thing we’re going to do is we could hit Add New, and so what data do we want to be able to analyze and you know, how do we want to analyze it?
Well, the first thing is since it has customer sales let’s go over to our account name field and drag it over to our rows because that’s going to be the really the basis for our analysis.
But, account name, maybe we don’t necessarily want it to say “account name” in the pivot table, we want to make it a little bit easier for our users to be able to really at a glance recognize what they’re looking at So under “properties,” under “caption,” let’s go ahead and just change that to “customer.” So now it’s going to show “customer” on the row.
So, let’s also take a look at, “well what did this customer buy?”
Well, let’s go ahead and take a look at our inventory ID and for that really, I want to make that a little wider just so that you know the entire ID is in the field. So, we’ll make that 150 characters wide and under width.
So next and the last piece onto the rows would be, “well, how many of these did they buy” So, we’ll move quantity over so now that we’ve got the rows defined. The next thing we want to do is, “well, what do we actually want to analyze,” actually the values or the numbers.
So, we’ll use our external amount and for that again we’re just going to change the caption under properties to “sales” and then we also want to change the format to currency. So, we’ll just type in a “c” under format for currency and we’ll do the same for external profit and we’ll change it to “gross profit,” so again people will easily be able to see what they’re looking at and we’ll make that a currency field as well.
So now we have what we want to analyze in there, but we also may want to include some other filters in the pivot table to be able to use while we’re looking at our data.
So, we’ll take a look at customer class and we’ll just move that over to the filters quadrant, and we’ll do the same for item class and then also financial period, and then name, which is our sales rep.
So, once we have that done, what do we want to call it now? Well let’s call it our “sales analysis pivot table,” there we go and we’re going to share this.
So we could always find our pivot tables just by a quick look up and actually be able to view it, but I actually want to put this on our site map so that it’s you know it’s going to be available to the users just through the normal navigation of the system. So let’s go ahead and share this now so that we can, you know, view it from inside the normal navigation of Acumatica. So what we’ll click on is Share and then we’ll say, well where do we want to share it. So let’s go ahead and put it in accounts receivable in the Explore section and we’ll go ahead and hit save.
So now once we go over to the Finance module, or suite, and accounts receivable under Explore, and now with the table, there’s a couple things that we can do with it but probably the most powerful feature we have is, as you can see these are all hyperlinked, so now we can start taking a look at this raw data of what items each customer has bought how, many they’ve bought and we can actually start drilling down on this data now to the source document if we wanted to.
So here we’ve drilled down to the line, and we can drill down even further to the actual source document of the of the query.
So again, access to that information is going to be very easy, even though it’s in a pivot table we still have all of our normal drill down capabilities. So right now, we were able to drill back down to that original invoice from that line we looked at and here’s that $3408 that we were just looking at.
So again, that full drill down capability is inherent within the system, but now we can also do some other things with this data as well.
One of the things that we might want to do is maybe we might want to see our sales by our sales rep, so what items this sales rep have been selling.
So we can actually move that down to the column for instance and now start taking a look at sales by the individual reps that we’ve had in there. so, we can see Steve is really leading the pack with his sales, and again, we could drill down on just Steve sales if we wanted to as well.
So sales reps are going to be available to us and then we can also do some things with let’s say the financial period as well, but this goes back to 2013 so let’s go ahead and just do a quick filter there and just say, well we really just want to take a look at 2016 sales now.
So let’s take a look at that, so now we’re looking at 2016 sales for Steve Church for which items that he’s actually been selling. So again, how many has he sold, what items, which customers of his have been buying, so again, a very easy way to start taking a look at anything that can be analyzed in terms of sales figures or where of our leads been coming in, from our CRM system. Any of these can be sources for a pivot table to be able to view the data exactly the way you want but not in a rigid format.
This gives us a lot of flexibility in terms of what items, what customers, what sales reps. We can, again, filter on just a particular sales rep if we wanted to just to see his sales or maybe just a particular item class sales and see what’s been moving lately in this class of products. So again a lot of flexibility and I’m giving you really the power to be able to look at your data the way you want to through this easy-to-use pivot table function.
Discover More Tips, Tricks and Other Features for Acumatica
SWK Technologies will help you get the most out of your ERP investment, leveraging our deep technological and industry knowledge as a top Acumatica partner and Community resource. Check out some of our Acumatica videos and other educational resources, and reach out to us when you’re ready to learn more tips and tricks to maximize your value return on your software.
Contact SWK here to gain access to more Acumatica tips, tricks, updates and tailored demos.
Learn More About Acumatica Pivot Tables