Currency conversion in People Story reports

[prachisathe]

We are a global company and trying to build a headcount in employee numbers and base compensation $$s. Our compensation information is stored in employee's base currency. Have any of you built such story that can group all employees data in USD by applying currency exchange rates?

[SanneKaasgaard]

Hi,
I have not yet created such a report as I have not found a good way to do so. My thoughts on building such a query would be to create a long if-calculation on a new value (e.g. USD-base compensation) with all the currency conversion rates for the current year. This would mean that it would need to be refreshed every year.
I.e. if only USD, DKK and SEK existed as currencies:

If(currency="DKK", base salary*7.4, if(currency=SEK, basesalary*6.4, base salary)))

Then I would use that new field "USD-base compensation" in the story.

But as it seems like a tedious and not too clever way of calculating it, I have not tried it out yet, so I am following along if you get a good reply from someone else on this issue.

/Sanne

[marianolanza]

Hi @Aneta_Krysiak ,

In our instance, we have this in Canvas , Advance Reporting. Check your Foundation Tables. As shared before, we are not seeing this in Story data schema yet :(...hope SF add this in the upcoming future.

I don't know if this is a "standard" configuration or this is something was built for our instance. You can check with your SF support.

[Aneta_Krysiak]

Hi @marianolanza 

thank you, I've this table.

Regards,

Aneta

[CSmith]

Hi @Aneta_Krysiak 

My guess is that the currency rate you see is the inverse of the rate in the table. I'm not sure when the rates were loaded but based on the conversion rate today, it looks pretty close for the EUR GBP pairing. Currency rates only need to be provided in one direction in the table but both directions can be calculated.

Hope this helps.

Christian

[Aneta_Krysiak]

Hi @prachisathe @CSmith @xavierlegarrec 

do you know what is the source of data in "Currency Conversion Rate" column in the Story Report? How it is calculated?

I've checked in Manage Data -> Currency Exchange Rate but there is no configuration done for it. Is it possible to report in the Story data from Currency Exchange Rate? 

Thank you and regards,

Aneta

[CSmith]

If you are starting with the Compensation Planning domain, it should be the currency conversion rates stored in Compensation Home> Actions for All Plans> Import/Export Data> All Plans> Currency Conversion Table. If there are multiple tables, it will be the one linked directly to the template in Plan Setup> Settings.

Hope this helps clarify.

Christian

[Aneta_Krysiak]

Hi @CSmith, thank you, I will check it!

[Aneta_Krysiak]

Hi @CSmith,

I've checked it and I see differences.

In the Story I have for EUR 1.17.

But in the lookup table for EUR I have 0.85.

Why I see such differences?

Thank you and regards,

Aneta

[CSmith]

Hi @Aneta_Krysiak My guess is that the currency rate you see is the inverse of the rate in the table. I'm not sure when the rates were loaded but based on the conversion rate today, it looks pretty close for the EUR GBP pairing. Currency rates only need to be provided in one direction in the table but both directions can be calculated. Hope this helps. Christian

[Aneta_Krysiak]

Hi @CSmith,

thank you very much for quick reply!

I've checked the lookup table and I have all currencies to GBP only. Nothing from GBP to other currencies. Table is from 2023 so some slight differences in currency rates can appear. 

Lookup table:

Story Report:

Currency Converter:

Is it possible to check why the system probably is using the inverse of the rate? I think, it should use data from lookup table.

I need in my Story for VRP currencies to GBP not the other way around.

Regards,

Aneta

[CSmith]

Hi @Aneta_Krysiak 

It's difficult to tell without knowing the specifics of the template especially if the currency conversion table has not been static. The ability for the table to compute inverse rates is standard system functionality for the compensation module. Which rate is used depends on how the template is configured to use functional or local currencies as the database value. I'm not 100% sure but I would assume if your worksheets are completed and the currency conversion table has been updated since, the data that is finalised in the completed form and the currency rates may no longer match. You can test this in your test instance with a new table or by launching new forms with the existing table. Leading practice is to create a new currency conversion table each year for each new template for data consistency and audit purposes. If you are looking to use specific rates in your report and not the rate configured for the data in the template, then I suggest you hard code the rates in your formula in report stories.

Thanks,

Christian 

[Aneta_Krysiak]

Hi @CSmith

thank you very much!

I've hardcoded exchange rate in the calculated column in my Story for VRP.

But I'm wondering why in the Table report for Variable Pay Bonus domain there is no issue with inverse of the rate?

Is it something that would be implemented in the future in the Story Report? 

Regards,

Aneta

[prachisathe]

We finally ended up building a custom currency conversion table. That seems to have done the trick. We associated table to location. Original attempt was to associate it to currency but that did not work. At this point though we have a solution in place and would be happy to share with anyone interested. 

[marianolanza]

Hi @prachisathe, we are working in similar project and tried many different option. Did you create a "manual" conversion table using the "calculated" field in the query builder? this should be something simple to report out, but we have this gap in all SF reporting since a while.

[prachisathe]

We created Custom Currency Conversion MDF object and associated it with location. We are uploading currency mapping every month using an upload process. This allows us to see the base labor cost difference caused by currency changes.

[marianolanza]

Thanks @prachisathe, that's a good turnaround. We already have our currencies conversion loaded in default tables in SF which is linked to the Legal Entity tables. We are able to get the conversion rates in Canvas report, but still not available in Story. Hope SF will add this table in Story to not duplicate and create a separated MDF as you are doing. Thanks for sharing.

[jmarshall]

I am dealing with the same thing for a client. Can you share your solution?

[Aneta_Krysiak]

Hi @marianolanza,

where have you found the default tables with conversion rate and how you linked them to the Legal Entity tables?

Thanks,

Aneta

[Udi]

Hi @prachisathe I'd love it if you could share some screen shots of your solution.

[Aneta_Krysiak]

Hi @SanneKaasgaard 

have you tried that calculation in the Story? Could you kindly please share the formula?

I'm just wondering how to create such formula to change salary in local currencies for USD/GBP. 

Thank you and regards,

Aneta

[SanneKaasgaard]

Hi Aneta,

No, unfortunately I have not. We started to set up currency conversion in general, but then decided on waiting with that part as we have most of our processes and hence currencies in the same country.
/Sanne

[LissieDomingo03]

I have the same question.