Creating a Ranking Report in Jet Reports
Those of us who use Jet Reports are painfully aware that Jet does not have a ranking feature for replicated lists. However, there is a workaround: you can create an automatically sorted Jet report with nested Jet functions and use Excel’s rank function to give you a ranked Jet report. This blog will walk through the steps to do just that.
Basic Sorting in Jet
To sort any replicating function in Jet using a field in the same table, add that field to your filters and use a + or – sign in front of the field name. If it is a field you don’t need to filter on, and only just sort on, use an asterisk * in the filter. We will use this same principal to sort a replicated list by rank.
Example:
Figure 1- Example of sorting a replicating function in Jet Reports
Figure 2 – The Jet Reports Function Wizard
Sorting by Rank in Jet Reports
To sort a replicated Jet list by rank we will use the same premise as the simple plus and minus sign sorting and add a nested Jet function and Excel Rank.EQ function.
First, create your table or report in Jet as you normally would. In this example, I have made a simple list of salespeople and their sales. The Salesperson column contains the NL Jet replicator function to create rows and the YTD Sales column contains a GL function for the total sales for that salesperson.
Figure 3 – Sorting a Replicated Jet List by Rank
In order for your report viewers to readily see the ranking add in an Excel RANK.EQ formula. This step is not required, but usually helps create a report that is more visually pleasing. Add a column for the rank and type in =RANK.EQ (cell reference for field you want to rank on, array for first cell in the reference column to empty cell below, 0). Visit Microsoft’s Office Support site for further explanation of the RANK.EQ function in Microsoft Excel.
In the example below, notice that the ref or cell array section of the formula should include the empty cell just below the replicated row. This will ensure the formula’s array expands as the rows replicate.
Figure 4 – Excel’s RANK.EQ Function
Now that we have a visible ranking, we are ready to insert a nested function into our replicator formula to sort the report results automatically. The easiest way to do this is to highlight the function syntax in the formula bar of the Jet report cell your Rank references (YTD Sales in our example) and copy the text. Make sure you copy the text and not the cell. In the example, this is the YTD Sales cell that uses a GL Jet function (you can use the NL function in this way too).
Figure 5 – Rank References Highlighted
Then select your row replicator cell and click the NL button to open the Jet Function Wizard.
Figure 6 – Select the NL Button
In the Jet Function Wizard, paste the copied GL (or NL) function into a blank Filter Field. If you have other filters set that is fine, this will not interfere with them. Then add a quote mark and a plus or minus sign (depending on how you need the results sorted) before the equals sign. Add a quote mark at the very end of the function as well. In the portion of the function that references the replicator results, remove the cell reference and enter an NF function with the key blank and the field matching the field value on the original NL function. Example: NF(,”Code”). Finally, in the Filter cell enter an asterisk “*”. Click OK.
Figure 7 – Set Filters in the Jet Reports Function Wizard
The final function will look like this (could use NL instead of GL):
Figure 8 – Final Function Example
= NL ( “What” , “Table” , “Field” , “+=GL(“”What”” , Account , “”Start Date”” , “”End Date””, “”View”” , NF( , “”Field””)) “ , “*”)
Notice the nested function uses double quotation marks. Jet should add those automatically for you. I find the copy and paste method to be the quickest way to perform the rank and sort; however, you can use the Nested Jet Function button instead. Please note, if you use the Nested Jet Function button you will have to add the doubled quotation marks and equals signs to your entry to get it to work correctly.
Click the Report button on the Jet ribbon and your results should be a list of values sorted with matching ranking.
Figure 8 – Ranked Report
Using the nested Jet function paired with rankings creates reports that do not require a manual sort or adjustments after running and gives you a nice clean presentation of data.
To find out more about Jet Reports and how it integrates with Dynamics NAV please visit our resource center, and be sure to subscribe to our blog and newsletters.
For more step-by-step instructions on how to perform specific tasks in Microsoft Dynamics NAV, see our collection of How-To blogs.