×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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!

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

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

CODE

{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

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

(OP)
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?

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

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:

CODE

{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.

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

(OP)
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...

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

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


Cheers,
Pete.

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

(OP)
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 :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close