Please read our Privacy Statement and Standard License Terms. By downloading any of the documents below, you are agreeing to our Standard License Terms.
Show Item Receipts by Item in Excel for NetSuite
For many businesses, displaying itemized receipts is essential for effectively managing spending, ensuring accurate financial analysis and maintaining up-to-date expense tracking.
In this article, we will guide you through how to display your item receipts by item in Excel with Solution 7. There are two ways of accessing and displaying this information, and in this step-by-step guide, we will walk you through how to do this using both our pivot table and advanced balance functions.
METHOD 1 – How to Show Item Receipts by Item Using a Pivot Table
Select the ‘Flattened Pivot Table’ button at the top of your Solution 7 tab in Excel and click on ‘Transactions – Posted’ as your table type.
Within the List Arguments pop-up window, you can select the relevant parameters. In the demonstration in the webinar linked above, Simon selects ‘Consolidated’ as his subsidiary and adds three asterisks ‘***’ in the account filter. This will allow the system to return both sides of the document.
Add the relevant time period that you want to look at.
If you scroll down in the List Arguments window you should see an option for ‘Transaction Type’. Within this filter you want to select ‘Item Receipt’ and then click ‘OK’.
Click the ‘Choose Columns’ button at the bottom of the window, which allows you to pull the relevant item information through. To show Item Receipts, these would typically be ‘Quantity’ and ‘Item Name/Number’.
*Important side note – When aggregating the quantity, numeric fields should always be summed. You can select any option for all other fields, whether it be counted, maximized, minimized, or grouped. When grouping fields, however, you need to make sure that these go to the top of the pivot table, and any aggregation calculation needs to go to the bottom.
We can then run that table by selecting ‘OK’.
Once the data appears you should now be able to see item codes, numbers and/or names, as well as the quantity and amount. You should also be able to click and open each of these tabs to see a further breakdown of your item information.
METHOD 2 – How to Show Item Receipts by Item Using our Advanced Balance Function
Select the ‘Function’ button at the top of your Solution 7 tab in Excel and click on ‘Advanced Balances’ from the drop-down menu.
Within the Function Arguments pop-up window, you can select the relevant parameters. In the webinar example, Simon selects ‘Consolidated’ as his subsidiary and uses ‘1200’ from the previous example as his value in the account filter.
As seen in the Method 1 walk-through above, you can add the relevant time period that you want to look at, and this will be specific to you and what information you need.
In the ‘Option 1’ filter, select ‘Transaction Type’.
In the ‘Value 1’ filter you can request a specific balance for a specific document type so, as before, select ‘Item Receipt’ and then click ‘OK’.
You should now be able to see the total balance within your spreadsheet.