Scenario
A Report/Saved Search about Sales Price History of Items is one of the most helpful reports used when analyzing Item Pricing. However, there is no standard out of the box report that provides this information.
Using an Item Saved Search to get the information from the System Notes is also not possible since the Old Value and New Value of the Pricing fields shows as a "View" link.
This is a known system limitation and is logged under Enhancement # 136721 which you can vote for in the SuiteIdeas page.
Workaround
Users can generate a CSV file with some Pricing History information by either:
Option 1: Using a Price List History Search
- Go to https://XXXXXXX.app.netsuite.com/app/common/search/search.nl?id=-39&e=T (replace XXXXXXX with your account number)
- You need to access it via URL since this saved search (Type = Item Pricing) isn't part of the list of the saved searches available to be created:
The search results will show the history of the price changes in rows like this:
Note that adding "Item" field in the Results tab of this search results in an Error. To be able to add the Item Name to the Results, you can add a Formula (Text) field with {item} in the formula:
Option 2: Creating a SuiteAnalytics Workbook using an Item Pricing Dataset
To know more about SuiteAnalytics Workbook, refer to SuiteAnswers 92690.
Once the Search or Workbook is created, user can then export this as a CSV file and use MS Excel to get the values of the Old and New Prices.
Based on my tests, if the Version = -1, then that is the current/new price while the highest version number is the most recent old price.
After you export the results to excel, you can then use IF and MAX functions combined with PIVOT to show the values like this:
In case you need it, here are the steps you can follow to get this data in Excel:
1. Convert the data range into a table (Use Ctrl +T)
2. Add a new column for the New Price. Use this formula: =IF([@Version]=-1,[@Price],"")
3. Add a new column for the Old Price. Use this formula: =IF([@Version]=MAX(IF([Item]=[@Item],IF([Price Level]=[@[Price Level]],[Version]))),[@Price],"")
4. Create a PivotTable:
*Rows
-Item
-Price Level
*Values
-Max of New Price
-Max of Old Price
Since we added formula that will return a blank if the Version column does not meet the criteria we set, then we will be able to get the Values of the Current/New Price and the Old/Latest Price of the item.
Known Limitation: There is currently no information on the dataset/search that would provide us when the price was changed.
-----
If you find this information useful, let us know by reacting or commenting on this post! ?