RANK() and DENSE_RANK() are SQL window functions used to assign rankings to records within a dataset. While these functions are powerful for ordering data, they have specific limitations when used in NetSuite's Saved Searches.
Understanding the Limitation
NetSuite currently restricts the use of window functions like RANK() and DENSE_RANK() in the Criteria tab of Saved Searches. Attempting to use these functions directly in criteria will result in an error. This limitation is documented in Enhancement #180201.
Alternative Approach
Although you cannot use RANK() or DENSE_RANK() in the Criteria tab, you can incorporate them in the Results tab to achieve similar outcomes.
Refer to the following articles that discuss more about the RANK() and DENSE_RANK() functions:
Note: While the alternative approach allows you to incorporate ranking into your Saved Search results, be mindful of performance implications, especially with large datasets. Always test your searches to ensure they meet your performance and reporting requirements.
By leveraging RANK() and DENSE_RANK() in the Results tab, you can effectively order and analyze your data within NetSuite's Saved Searches, even with the existing limitations in the Criteria tab.
-----
If you find this information useful, let us know by reacting or commenting on this post!