Proposal For Item Pricing Report

Proposal Summary 

This proposal covers the scope of generating two reports of Item pricing based on customer and transaction. The two reports should include item and item pricing fields with filters applied. 

Requirement 

Create two reports in the standard NetSuite Report Builder format as follows: 

Report 1 as ‘New Item Pricing based on previous 12 months purchases’: 

1. Columns are as follows: 

1. itemid as ‘Item Code’ 

2. description or salesdescription (or if blank – displayname) as ‘Description’ 

3. ‘Quantity Break’ 

4. formula of relevant sell price x custitem19 in currency format as ‘Net Price’ rounded as per standard Netsuite policy 

2.  Filters are as follows: 

1.  custitem_category 

2.  itemid 

3.    Sorting is alpha numerically by itemid then by ‘Quantity Break’ 

4.    Report logic is as follows: 

When the report is selected a dialogue box appears asking for a single entity id which must be a ‘Customer’ type 

Data populates based on selected filters by populating every item purchased in the previous rolling 12 months by the selected customer with a new row for every quantity break that particular item has 

The ‘Quantity Break’ column is populated with the number at the head of every quantity break for that item 

The ‘Net Price’ column is populated based on the current Netsuite logic for the pricing that customer gets as the ‘relevant sell price’ in 1.d) 

Report 2 as ‘New Item Pricing’: 

As per Report 1, but 4.b) logic is: 

Data populates based on selected filters by populating every active Assembly and Inventory item with a new row for every quantity break that particular item has 

Our Solution 

We can achieve the requirement using saved search functionality as the Quantity Break fields are not available in reports. 

Also, we need to combine two saved searches to get all the specified fields and filters in the search results. 

Report 1 

We will create two saved searches as the below logic 

1. Item Search 

The Criteria of the Item search will be –  

• Transaction Date is after same day last year. This is to get the transactions of previous 12 months period. And the transaction type will be “Sales order. 
   

The Columns will be  

• Item Name (Itemid) 

• Description – If there is no description in the item record then it will populate the “Display name” field. 
• Quantity Range 
• Formula Field – Unit Price * Price change % 

2. Customer Search 

The Criteria of the Item search will be – Transaction Date is after same day last year. This is to get the transactions of previous 12 months period. And the transaction type will be “Sales order. 

The Columns will be 

• Item Name (Itemid) 
• Price Level 
• Quantity Break 

The two searches will be combined into one using Suitelet page. The common unique filed will be Item Internal ID in both the searches. And in the combined search will show only the quantity breaks and the unit price corresponding to the Customer’s price level for that item. 

In the combined search, we will add five filters 

1. Customer – The customer filter will be shown before loading the search. The search results will show up only if any customer name is given in the filter box. Otherwise, no results will display. 

2. Item Name(Itemid) 

3. Item category 

4. Price Change % – The filter criteria will be Greater than 0 or not. This filter will be displayed for both reports. 

5. Page Number 

And the Combined search will be sorted based on the Item Name and Quantity Break 

The user can export the search results in CSV or Excel format. 

Report 2 

For the 2^nd report, we will be combining item search and customer search as the 1st report so that all the columns can be included in the search results.  

• We will include all the columns filters and sorting options to the saved search. 
• The criteria for the search will be item type – Assembly and Inventory. There will not be any criteria for transactions. 
• The Price Change % filter will be displayed in the search filter region. 

Assumptions 

• The search results can only be filtered for a single customer at a time. Multi select of customers in the filter is not possible. 
• The result can be exported as CSV file only. There is no provision for emailing the search results. 
• The saved search results will be paginated when showing in the Suitelet page. Also, the exported csv file will be split into different files if there are large number of results 
• There won’t be any drill down or detailed view from this report. 
   

Risk 

• There is a chance for search time out if the search results exceed a certain limit in future. This is because the search fetches details from various transaction records.