Creating a Saved Search

Pivot Reports are based on saved searches. We can use any search we want. In most cases you will probably use one you have already created. Or an edited version of one you have already created. While I could create a search on my own and just have us use it. For our example I think it’s best for us to go through creating it together. This will not only take you through the entire process, but also help make sure you are familiar with the data we’re looking at. Let’s start by going to Reports, then Saved Searches, then All Saved Searches, and clicking on New. We’re going to create the same one we used a couple of videos ago, when we looked at Excel’s pivot table functionality. This will be based on the Sales Order, which is a very common document. We’ll scroll down and select Transaction for the type, since Sales Orders are a type of transaction. We’ll give this a name, “Demo Pivot Report SO Search”. And we’ll provide an ID as well, “_demo_pivot_report_so”. Of course, if you are following along and doing this in a production system, you’ll want to use your companies naming scheme. Before I start building this, and adding complexity, I like to save it with just the name and ID. This helps to make sure I don’t lose a lot of work if there is a problem saving. Generally, I also like to save and preview after making most edits as well, but for the sake of time, I won’t be doing that here. We will go ahead and save this though. And this drops us back at our New Saved Search screen. Let’s get back to our search by going to Reports, then Saved Searches, and this time we’ll click All Saved Searches. Because of how I was already sorting this list, our “Demo Pivot Report” is here at the top. Let’s take a look at our results by clicking View. We can see a total of 26,225 results that were returned. More importantly we can also see in the type column that there are Inventory Adjustments here. If we looked through more pages we would see Sales Orders too. This is because we haven’t changed anything about this. It’s a default transaction search right now, and it’s returning all rows of all transactions. Let’s go back and click Edit to modify it. As a side note, I usually don’t recommend using the browser’s back button in NetSuite, however in this case I knew exactly where I was going. If you are accustomed to working with Saved Searches on a regular basis, you might have noticed this new Pivot Report button, up here with our action buttons. This was here when we first created this a moment ago as well, but we’ll come back to it in a bit. For now let’s get our results how we want them. Since the only transactions we want to see are sales orders, the first thing let’s do is set a type filter. Any of, is the default, and that’s fine for what we’re doing. We’ll scroll down and choose Sales Order from the list, and click Set. We could pick Multiple values by holding the ctrl key while clicking, if we needed to. For sales orders, and for this type of search, we really should either look at everything that is on the mainline, or everything that isn’t on the mainline. Otherwise we will end up with a situation where many of our totals are doubled, specifically financial numbers. We’ll do this by picking Main Line as a filter. In the popup dialog box we will choose No, and click Set. This means that we will be looking at each line item from each sales order as a line in our results. Follow so far? There are a few line items we probably don’t want to see, so we’ll eliminate those. First let’s add a filter for Tax Line. Tax lines are usually hidden line items, on a sales order, that represent the taxes we are obligated to collect. There are also a number of items, that I don’t want to show in the results either. For me, most of these are shipping and test items, but for you, they might be something different. I’m going to change this to, “none of”. I’ll also expand the values so we can see it a little better once we have entered everything. Now we’ll go ahead and click these drop down arrows, and start scrolling through the list sections, to look through all of these, and add everything that we don’t want in our results. The first thing I will add is, None. So we are effectively saying none of none. This means we are eliminating lines that are left in for formatting purposes. We’ll scroll down, and add Billable Expense Markup. As well as Billable Group. I’ll go ahead and add the rest of these, since it will get pretty tedious to watch me add them all manually. Now that we have all of these added, we can click Done. Here you can see the full list of exclusions, and we’ll click Set to add these. For our search, these criteria are all we need, however if you were looking to exclude more lines, you could do that. Or you could modify this any other way you needed to. Let’s save this, which drops us back to the Saved Searches screen. Now, let’s take a look at the results. This time we only have 1,627 lines, down from the over twenty six thousand we had earlier. These also all show sales orders for their type, which is what we expect. If this was a production report, I would actually go through this data and make sure it looked correct. But for demonstration purposes, I happen to know this is what I want us to look at. One of the things to note, is that while the number and individual line items are correct, the data we need for our pivot report is not. For example, I would like us to see the number of each item, and that isn’t listed here. Neither is the Sales Rep. Let’s fix this by clicking back, and then Edit, to edit this report again. This time we’ll go to the Results subtab. First let’s remove the fields we don’t need, starting with this asterisk, and moving down. We’ll leave the date field, as this could be useful, but we don’t need the, as of date, field, so let’s remove that. Period is fine, but we don’t need the tax period. Type and Document number can also be removed. Name is okay, so we’ll leave that. For us this is the company, or person, we sold the items to. We don’t need account or memo, so those two can be removed. Finally we will leave amount, because we will be using that. This amount is the line item total. This takes care of the changes we need to make to our default fields. If your default list of fields is different than mine, that’s fine. In fact it’s normal. This list is based on my specific configuration of NetSuite, and yours will likely be different than mine. Now that we have gone through the fields that were in the saved search by default, there are a few more we need to add in. Let’s add the Item field, because we want to look at what items sold. As well as the quantity field, since we want to know how many of those items were sold. We’ll also add the sales rep field, which lets us see who sold the items. We might want to know what shipping method was used, we can access that through the ship via field. The order these fields are in is okay for the most part. However, I do want to drag Amount down below Quantity. This is just a preference I have, however we would get the exact same results either way. Functionally there is no difference. I also think some of these field names are a little ambiguous. We can label these, and the labels will propagate to the pivot report. I’ll change Name to Company, since this is mostly business to business transactions. Let’s change Transaction Number to Order Number, and Amount to Total. I also want Ship Via to be Ship Method. One more thing worth noting before we save this is, pivot reports really only use these two, Results and Criteria tabs. This is why these are all we focused on, when creating this search. If you have other settings in Highlighting, or any of the other subtabs to the right, they won’t hurt anything, but they don’t really do anything either. Let’s scroll back down and save this. We should also take one last look at the results before we call it done. This way we can make sure that everything is how we think it should be. This all looks just fine. I can scroll down, and I am seeing pretty much what I expect. If this was going to be used as a production report, there are probably a few more things I would want to check and maybe even change. But this is good enough for our purposes. All of this should really enforce the fact, that it’s incredibly important to get your saved search right. This is what will power the pivot report. And as the saying goes, garbage in, garbage out. Now we have our saved search, but this still isn’t a pivot report. We’re going to turn this into a pivot report in the next video.