Hello,
Is there any way that the GL function could be used to retrieve the balance for the additional-currency? (my studies and research turned out negative, but I still keep 0,1% hope…)
Thanks,
Elena
Date
Votes
6 comments
-
Jet Reports Historic Posts Hi Elena,
No I believe you'll have to use the NL(Sum) function off the "G/L Entry" table for that (or the "Analysis View Entry" if you are using an analysis view or the "G/L Budget Entry" or "Analysis View Budget Entry" if you are using budgets). Does that answer your question?
Regards,
Hughes -
Jet Reports Historic Posts Yes, that is the alternative - NL(SUM) function… but the time spent is way longer than using the GL function… I'll have to try with the Analysis view entry to see the time - it's the one left unchecked.
As I said, it was a hope…
Thanks!
Elena -
Jet Reports Historic Posts Tried it - the NL(SUM) function with "Analysis View Entry" works faster than with the "G/L Entry" (overall a bit slower than GL but within reasonable limits).
-
Jet Reports Historic Posts Hmmm, the query being performed by an NL(Sum) is actually the exact same query as the GL function if you were summing the Amount field on the G/L Entry and filtering by the fields available in the GL function (Posting Date, G/L Account No., Global Dimension 1 Code, etc.). The only time the GL function would use the Analysis View Entry table is if you actually specified an analysis view in the GL function. You should be careful summing off the Analysis View Entry table if you do not use Analysis Views because I believe you probably need to filter for a particular Analysis View and that Analysis View itself has to be updated to contain accurate data.
As far as your NL(Sum) being slow, I believe this probably has more to do with the fact that you are summing a different field than the GL function. There is probably not a key in Navision with a sumindex for the field you are summing with the fields you are filtering on, and I think that is more likely the cause of the speed difference. If you added a key to Nav with a sumindex for the field you're summing, then you would probably get equivalent speed to the GL function because really under the hood, the query created by a GL function is no different than an NL(SUM) function.
Regards,
Hughes -
Jet Reports Historic Posts Hmmm, the query being performed by an NL(Sum) is actually the exact same query as the GL function if you were summing the Amount field on the G/L Entry and filtering by the fields available in the GL function (Posting Date, G/L Account No., Global Dimension 1 Code, etc.). The only time the GL function would use the Analysis View Entry table is if you actually specified an analysis view in the GL function. You should be careful summing off the Analysis View Entry table if you do not use Analysis Views because I believe you probably need to filter for a particular Analysis View and that Analysis View itself has to be updated to contain accurate data.
…well, I needed this to sum the ACY values; and it's ok with the Analysis View since there is only one used for this particular report.As far as your NL(Sum) being slow, I believe this probably has more to do with the fact that you are summing a different field than the GL function. There is probably not a key in Navision with a sumindex for the field you are summing with the fields you are filtering on, and I think that is more likely the cause of the speed difference. If you added a key to Nav with a sumindex for the field you're summing, then you would probably get equivalent speed to the GL function because really under the hood, the query created by a GL function is no different than an NL(SUM) function.
…my job is as a NAV consultant for a costumer, and the NAV license does not allows customers to modify anything with the tables. I think that another approach would be setting the key directly into the Jet formula, but I didn't used this so far, and since it works great with the Analysis View it's more than enough.
Thanks for your ideas!
Elena -
Jet Reports Historic Posts Hi Elena,
Yes I understand most customers cannot add keys to their databases. I was just trying to explain the likely cause of the speed difference you were seeing with using a GL vs. NL(SUM) since they really do actually perform the same operation if you were performing the sum on the amount field. There isn't anything magical being done by the GL function other than picking the right table and filtering on the correct fields. The speed is due more to the fact that there are Nav keys with sum indexes for those fields. I'm glad you have found a working solution using the Analysis View Entry table.
Regards,
Hughes
Please sign in to leave a comment.