I have extracted 2 values from NAV using the below formula to provide the result required.
=-NL("Last","Contract","App Gross (LCY)","Contract No.",$D6)+NL("Last","Contract","Last Export Value Costs","Contract No.",$D6)
However I now what to sort this report automatically to show the report based on the ascending order of this result.
I have tried numerous formulas including the double quotes, but cannot get the right result.
Can any advise where I am going wrong?
I have a similar issue, but I'm NOT using a NAV datasource and my calculation is based on values from 3 fields of 2 different tables. But the situation is very similar. Have you received any response to this, Andrew, or did you figure it out?
Thanks!
Unfortunately no-one came back with a reply or solution, apart from JET offering professional advise for a fee! So I have just worked with what I have and let our users filter the spreadsheets manually.
Sorry cannot be of anymore help Brian.
Brian & Andrew -
Here is a simple (albeit somewhat contrived) example of how this is accomplished in Jet Reports version 19.5.
I am listing out customer numbers and listing a couple of values for each customer. Column G includes a sum of the other two numbers. This formula is then modified (using the technique described in the NAV Sort by Sum article) and placed in cell B3.
I then added the reference to that function in my NL(Rows) function (cell D6) so as to sort by that value:
When I run my report, the results are sorted based on that formula in B3:
Note: Due to an issue with some earlier versions, this technique (where there are TWO NL functions in cell B3) requires the latest release.