Using Excel Rank and VLookup Formulas to Perform Automatic Sorting
For Microsoft NAV 2013 R2, NAV 2009 .
What is Jet Reports?
As an introduction, Jet Reports for Microsoft Dynamics NAV (Navision) is an integrated reporting solution for Microsoft Dynamics NAV 2009 and NAV 2013, giving NAV users a simple way to create high-impact reports within excel. This article is intended for Jet Report designers. To see detailed demonstrations of Jet Reports check out:
Can Jet Reports run an Excel Macro?
As I have conducted many client training sessions and designed numerous jet reports, one of the questions that I am asked is whether Jet Reports can run an Excel macro to sort after running a jet report. When researching this question with Jet Report’s support desk, their response is that it is running an excel macro is not recommended with Jet Reports.
Is there another solution other than running an Excel Macro with Jet Reports?
As an alternative to running an Excel macro in instances where automatic sorting of data is required, the Excel RANK and VLOOKUP formulas can be used. Let’s take a typical case of a Top Customer Sales report where, due to special filters, the calculation of the sales amount is done through the NL sum function. An NL Sum calculated field will not automatically sort when running the jet report. (Note that if the customer Sales ($) “Calcfield” field were used, running the jet report would automatically sort).
The following are the steps to use RANK and VLOOKUP formulas:
1. Create a new jet report which lists all customers and their corresponding sales. The report should include the following fields: No., Name, City, Sales ($).
2. Add report filters including, but not limited to, the No. and Date Filter fields. For the Sales ($) field, use an NL Sum function to calculate the customer’s sales.
nsert a new column before the No. Column with the title Rank. As per my sample report, enter the Excel RANK formula in E13 to rank the sales per customer (column I). Note that there is an anomaly with using the RANK function when there are two values that are the same, as excel error occurs i.e. two or more lines where sales = 0. As a workaround, use the COUNTIF function to create different rankings for the same value. See sample syntax.
5. At the bottom of the report, use the count function to count the maximum records of the report. This count will be used with the VLOOKUP function on a separate worksheet page called “Ranking”.
6. I further converted the total count into a range from 1 to the maximum count i.e. 1..50. I then created a named ranged called ‘NumberRanked’. The name ranged will be used on the “Reporting” worksheet as I will describe in the next step.
7. On a separate worksheet, copy the column structure of the report. Rename the new worksheet to ‘Ranking’. To use the VLOOKUP, we need to list ranking number 1 to the maximum count from the previous worksheet. I used an NL Rows function to publish numbers from 1 through the maximum count using the integer record table. See sample below.
8. Then, place the VLOOKUP formula in the first report row under each column. In my sample below, the syntax for the VLOOKUP is VLOOKUP($E13,Report!$E$13:$I$14,2,0):
a. $E13 represents the ranking (as per my integer display via the NL function).
b. Report!$E$13:$I$103 represents the data set on the “Report” worksheet.
c. “2” represents data in the 2nd column i.e. the No. field. The syntax under the “Name” column will be =VLOOKUP($E13,Report!$E$13:$I$103,3,0) where the “3” presents the third data column.
d. “0” represents the range lookup.
9. Since only the “Ranking” worksheet should be published, in A1 of the “Report” worksheet, place the following syntax to hide the “Report” worksheet:
When the user runs the report, the “report” worksheet (which is hidden) calculates all of the customer’s sales and related data, and assigns a ranking. The “Ranking” worksheet publishes the data in the correct sort order using the RANK and VLOOKUP functions. See pictures below.
Written by Mark Stept
Clients First Business Solutions, LLC