I am attempting to produce a report to show total revenue for a product. The problem is in the currency conversion. Is there any documentation on this?
For example get the total revenue for a product, yet it produced revenue in 2 NAV companies how would i accomplish this in a report? Lets say it produced revenue over a 2 year period in Canada and in the U.S.
Any help would be greatly appreciated
Hi Sean,
That’s a relatively straight forward question with a less than straightforward answer.
If you look at how NAV works with regards to currency, then the system strives to calculate the LCY rate against the exchange rate held, based on date. So I could have a USD to GBP exchange rate of 1:1.5 on 1st Jan, with a second line in the exchange rate table, with an exchange rate of 1:1.45 on 1st Feb. Subject to your order date, the appropriate rate will be amended. (Create yourself a new sales order in a test environment and then play with the dates, you will see the warning messages, and what NAV is trying to do in the background).
In some areas of NAV (customer/vendor ledger entries for example), you will see that NAV has already converted the transaction and holds the converted amount in LCY (so you will see “Amount (LCY)” on the table).
But, with item/sales analysis stuff you wont find the comparible fields on the item ledger entry.
So, the solution, from a reporting point of view, is either to group the report by currency (body1 of the report shows USD and body2 of the report shows Canadian dollars), or to try to convert each line to show in a single currency.
The sales invoice lines could be reported in their native currency, with a simplistic conversion based on a single currency exchange (perhaps today’s rate). The alternative - subject to what level of detail you need on the report - would be to have each line converted at the appropriate daily rate.
For example the different approach would be
1 x ABC £1.00 @ 1.5 echange rate
1 x DEF £1.00 @ 1.45 exchange rate
using a single (perhaps todays exchange rate) would result in a report of 2 @ 1.45 USD = 2.90 USD
using the daily rate would result in a report of 1 @ 1.50, 1 @ 1.45 = 2.95 USD
There is a further consideration, which is the additional reporting currency. ACY is used for those companies who have a parent/reporting requirement in an alternative currency to their own. If you look at the field definition of the sales invoice tables, for example, you will see ACY DOES get populated on these tables. So, using the additional reporting currency, might be a solution for you (although I wouldnt recommend turning this on, just to solve a sales reporting requirement - but if there is a need to report at a financial level in USD, for example, then it may be worth considering).
hope this helps and good luck !!!
Hi Sean,
as Kryton wrote, this is no really easy task. Without lots of additional programming, there are only two ways to solve this:
Consolidate the companies into one for the reporting. With the consolidation the balances will be converted into the currency of the consolidated company. This is quite a lot of effort for “just” some sales reporting, and it’s not easy to accomplish / set up. I would assume there is already a consolidated company. Usually the consolidation is only carried out for financial reporting, but the sales figures would be there (and converted into one curency).
Use ACY. For this I would recommend to use a separate currency code only for reporting. This has the advantage of setting the exchange rate separately from the “real” currencies, thus avoiding lots of adjustments. With ACY you have a chance to get timely reporting, you could also write a report/form/page which totals over several companies.
If I had to solve this problem, I would look if there is consolidated reporting and how timely it is. If that’s not an option, I would convert the figures on the fly by company, avoiding the ACY. But that’d be an all customized solution with additional global tables and at least a few days of coding.
with best regards
Jens
Wow. I am going to digest the information provided and figure out the best way to display this report. I believe i will have to convert every transaction based on the posting date and convert the currency based on the Currency Exchange Rate table.
I was informed that Jet Enterprise has this type of functionality. The data can be aggregated in to the cubes, I am not sure the dificulty in the setup. I am just surprised that this is not a more common request from more users utilizing NAV. I am looking for some serious BI tools for NAV to help my company make more informed decisions. I have tried to use JET reports a few times but the reports take around 30 minutes to run, i am not really happy with the wait time considering SSRS reports pulling the same data complete in seconds…
30 minutes for a Jet Report is not really normal. I’d guess your report needs optimized/structured differently or you’d seriously benefit from some additional keys within NAV that match your “filtering” in your NL statements.