Use RANK or DENSE_RANK as Criteria in a Saved Search

Window functions such as RANK() and DENSE_RANK() are allowed only in conjunction with a SELECT list of query. In other words, although these two functions are logical SQL expressions they cannot be used in Saved Searches either as Criteria or in the Results tab when combined with a Summary Type.

This behavior is still filed as Enhancement# 180201.

To illustrate, assume that a user wants to display the last 4 Bills for all their Vendors. The outright solution is to use formula below in the Criteria tab:

Formula(Numeric) = CASE WHEN (DENSE_RANK() OVER (PARTITION BY {name} ORDER BY {trandate} DESC) < 4) THEN 1 ELSE 0 END is equal to 1

This formula means that the system will rank the list of Bills by Date in descending order per Vendor and only the first 4 will be displayed.

However, the system will return an unexpected error because of the reason provided earlier. Therefore, the alternate solution is to design the Saved Search in this manner:

1.    Navigate to Transactions > Management > Saved Searches > New

2.    Select Transaction

3.    On the Criteria tab add Type is Bill and Main Line is Yes

4.    On the Results tab add the following:

•         Formula(Date) = CASE WHEN (DENSE_RANK() OVER(PARTITION BY {name} ORDER BY {trandate} DESC))<4 THEN {trandate} END

•         Formula(Text) = CASE WHEN (DENSE_RANK() OVER(PARTITION BY {name} ORDER BY {trandate} DESC))<4 THEN {mainname} END

•         Formula(Currency) = CASE WHEN (DENSE_RANK() OVER(PARTITION BY {name} ORDER BY {trandate} DESC))<4 THEN {amount} END

5.    Hit Pivot Report

6.    Drag Vendor in the Row Dimensions, Date in the Columns and Amount in the Data

7.    Hit Run Report

8.    When the Pivot Report shows up, search for the Vendor that says "Empty" and hit the Collapse button

What this Saved Search / Pivot Report does is that it will only show details for the first four Bills and hides/masks the rest. Note that this Saved Search will still search for all Bills in the database and may pose runtime issues especially if you have a lot of transactions involved. Some consideration when this happens is to set Criteria such as Date.