Monthly exchange rates, select only newest rate on each currency for currency conversion on row

[J33]

Hello, I have a stock value report made with crystal reports that shows the all item codes in stock (one row for each item code in the report), including item description, amount in stock, previous currency price, currency and also calculates the amount in stock * previous currency price.

I would add a Field, HomeCurrency (EUR) for example, but how do I get the report to always calculate according to the latest exchange rate of a specific currency? and IF Currency = EUR on the row, then it should show only the same value as in the in stock * prev currency price.

As an example:
Item code=X;Item description=NorwegianSweater;in stock=5 pcs;previous currency price=10;Currency NOK;In stock * Prev currency price=50;HomeCurrency (EUR) (50 / 9,9955) =5,00 EUR

The currency table looks like following:

I hope you understand my explanations thanks for the help in advance!

 

[pmax9999]

You will need to add a Group Selection formula like the following: [click on the menu item Report ==> Selection Formula ==> Group]

{Currency.Date} = Maximum({Currency.Date})

, amending field names as appropriate.

The report will then only include the latest exchange rates (ie, the exchange rates for the most recent date).



Hope this helps.
Cheers, Pete

 

[J33]

Hi, I tried this and it removes all EUR results (as well as those which have blank currency value) but shows all other currencies. If it matters, EUR is only once with date 2002-02-02 00:00:00 as "1" in the list as it is in this case the homecurrency in the ERP. Ideas how to fix it?

 

[pmax9999]

Maybe I just don't understand your data and/or what you are trying to achieve.

But, if I do understand, make sure the exchange rate table is joined to your stock table as a Left Outer Join. Then amend the group selection to the following:

{Treansactions.Currency} = 'EUR' or
{Currency.Date} = Maximum({Currency.Date})

The local value (EUR) data should then appear in your report, but without an exchange rate. You can then use an If statement in your conversion formula to return the local value where the currency is EUR.

Does this help. If not, I'd suggest you explain the data structure in more detail.

Cheers
Pete.

 

[J33]

I created a new report with the currency table and entered the group selection {Currency.Date} = Maximum({Currency.Date}). It leaves the EUR out (in red, copied straight from the DB). If I get the EUR included with the group selection, then everything should work...

 

[pmax9999]

Did you try the amended group selection formula I gave you in the previous post?


Cheers,
Pete.

 

[J33]

Thanks Pete for asking that again, I got it wrong the first time and it works now. I'm still learning and I hope my knowledge and stupid questions get better soon. Thanks again