Creating a Pivot Report

In the last video we created the saved search we will use. In this video we will turn it into a pivot report. The process is pretty simple. Let’s bring it back up by going to Reports, then Saved Searches, and clicking All Saved Searches. The way we create, and view, the pivot report is by clicking edit over here. This means, you must have the ability to edit the search you want to use as the base. We can preview this, and it’s what we had in the last video, so we are all set. Let’s return to the criteria, and click here to get started creating the Pivot Report. This interface works through drag and drop. Initially you take fields from the Search Fields list, and drop them to the right, on the layout fields section. The fields here on the left, are the same ones we created, when creating the saved search. You might also notice they are in the same order we placed them on the results subtab. Any custom labels we created, like changing Amount to Total, are preserved as well. Since we are looking to see the quantity of items sold, by sales rep, let’s start by dragging the Sales Rep to the Row Dimension. I’ll also make this layout window a bit bigger, so it is easier to see. Let’s put Quantity under data. We see this immediately refresh, however this isn’t that useful. We could collapse all of our sales reps, and just get an aggregate total of the number of items each rep has sold. But usually this wouldn’t be all that helpful. Let’s add item to the Column Dimensions. Now this looks a lot closer to what we had in Excel earlier. This bottom pane is just a preview pane, so it won’t show us all the data, we would have to run the report to see it all. But we immediately see a problem here, and it is the same one we had in Excel. There are a lot more items, than there are sales reps. This means our view will be quite a bit wider, than it is long, and that will make it difficult to read. In Excel we could just move the Item to Row Dimensions, and the Sales Rep to Column Dimensions. We can try that here. I’ll go ahead and move Item. This is now nested under Sales Rep. Now we can’t move the Sales Rep, and that’s not what we want. This actually might not be a bad report, but it’s not what we were going for either. So let’s delete the Item by right clicking it and selecting delete, which is our only option. We could have also deleted this, while it was under Column Dimensions, and it would have had the same results. We can move Sales Rep to Column Dimensions. We can also drag Item back to Rows. Now we have something similar to what we had in Excel earlier. One of the important things to note though is. As we were doing all of this dragging and dropping, and moving things around, the preview pane kept updating. While it only provides a small sample of the results, this lets you know if what you are building, resembles what you think you are building. Put another way, it allows you to build interactively. Let’s go ahead and Run the full report by clicking, you guessed it, Run Report. Here we see the results. To make this a little easier to read, and get a similar look as what we had in Excel, we can click this Collapse button at the bottom. We can scroll through the results if we want, but these are the same as what we achieved with Excel earlier. We also have options to Print this, or to save it as an Excel, PDF, CSV or Word Document. You might notice that there are some options missing here, that are included in regular reports and saved searches. We can’t email this for example. Let’s come back to our layout, so we can see some more functionality. I’ll make the layout window larger again as well. One of the things we might want to do is, see not only how many of an item was sold, but what the value of those items were. This is helpful because, certain items may matter more, or less, to a business. For example, a car sales person who sells two cars, is probably better than one who sells 200 air fresheners. We’ll add total here under Data, and now we see some dollar amounts start to show up. For this first HP computer we see two were sold by Mary Redding. We might also want to know when these were sold. For that we can add Period, and this is the accounting period, under Columns. Now we can see that one of these was sold in December 2012, and one in August 2014. The difference between Columns, and Column Dimensions, is that Columns just adds extra columns here on the left hand side, adjacent to the Row Dimensions. Column Dimensions are used to break data out, and expand the columns based on each unique instance of the data. If you take a look at what is highlighted, these are the Row Dimensions. These are the Column Dimensions. These are the Columns. And this is the data. We might also want to see the order number nested under the item. We can do that by dragging and dropping it under Row Dimensions. Now we can see, for each item, not only when it was ordered, but also on what order it was placed. I suspect after seeing these examples, you probably can think of at least a few places where this would be immediately useful in your business. I do want to cover a few last things though. First is that you can collapse the search fields, which might be useful if you have a narrow screen. Or if you have all the fields you need in the layout already. You can also collapse the preview pane, however that is usually not as helpful. If you are wondering how you save this, you don’t need to worry, it kind of just gets saved in the background automatically. If we return to the search, then come back to the pivot report, you can see that things are exactly how we left them. The layout is saved in custom records that are installed with the bundle. Unfortunately you can only have one layout per search. But since you can easily copy searches, this doesn’t tend to be a problem in practice. You might think, wow, compared to the last video about creating the saved search for this Pivot Report. This video was a lot quicker. You are correct, and there are a few reasons for this. The first is that if the search you use is in good shape, using pivot reports will be a lot easier overall. The second is that pivot reports were designed to be quick and easy to use. I think the reason more people don’t use them is the same reason more people don’t use pivot tables in Excel. If you don’t understand what they are, what they do, and how to use them, then you are unlikely to use them. Now you know how to create a pivot report in NetSuite. My hope is that this will help you, so you don’t have to take extra steps, to move your data to Excel as often. These concepts should also help as NetSuite pushes towards Suite Analytics.