Introduction
Global corporations often need their CPM to convert foreign currencies into a single reporting currency based on exchange-rate information. We will discuss methods to handle these currency-exchange conversions in Kepion.
Example
1. Create a Model to store the exchange-rate information.
2. Create a view in the database that translates from one currency to another. Please ensure the view has the same structure as the partition table in the target Model. In this case, the original data is in Local Currency. When converting, we store the converted value in Report Currency.
Here is an example snippet:
USE [OPEX]
GO
/****** Object: View [dbo].[USR_Profit_FX_Current] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[USR_Profit_FX_Current]
AS
SELECT
ROW_NUMBER() OVER(ORDER BY
[AccountID]
,[TimeID]
,[ScenarioID]
,[Currency TypeID]
,[EntityID]
,[Value]) [RowID]
,[AccountID]
,[TimeID]
,[ScenarioID]
,[Currency TypeID]
,[EntityID]
,ROUND([Value],0) [Value]
FROM
(
SELECT
F.[AccountID]
,F.[TimeID]
,F.[ScenarioID]
,(SELECT TOP 1 MemberID FROM [D_Currency Type] WHERE MemberLabel = N'RC') [Currency TypeID]
,F.[EntityID]
,CASE
-- Require Conversion? Yes if local currency != reporting currency and it is a debit, credit account
WHEN DE.[Local Currency] <> DE.[Reporting Currency] AND ADC.MemberID IN (1,2) THEN F.[Value] / FE.[Value]
ELSE F.[Value]
END [Value]
FROM
(
SELECT [AccountID],[TimeID],[EntityID],[ScenarioID],[Value]
FROM dbo.[USR_Profit_OPEX_Current] (nolock) WHERE [Currency TypeID] = 1
UNION ALL
SELECT [AccountID],[TimeID],[EntityID],[ScenarioID],[Value]
FROM dbo.[USR_Profit_HR_Current] (nolock) WHERE [Currency TypeID] = 1
UNION ALL
SELECT [AccountID],[TimeID],[EntityID],[ScenarioID],[Value]
FROM dbo.[F_Profit_CoreMG_Revenue Source] (nolock) WHERE [Currency TypeID] = 1
) F
INNER JOIN D_Account DA (nolock)
ON DA.MemberID = F.AccountID
INNER JOIN AccountLogic_AccountType AT (nolock)
ON DA.AccountType = AT.Label
INNER JOIN AccountLogic_DebitCredit ADC (nolock)
ON AT.DebitCreditMemberId = ADC.MemberID
INNER JOIN D_Time DT (nolock)
ON DT.[MemberId] = F.TimeId
INNER JOIN D_Entity DE (nolock)
ON F.EntityID = DE.MemberID
INNER JOIN D_Currency SC (nolock)
ON DE.[Local Currency] = SC.MemberLabel
INNER JOIN D_Currency DC (nolock)
ON DE.[Reporting Currency] = DC.MemberLabel
INNER JOIN dbo.[F_Exchange Rate_CoreMG_Writeback] FE (nolock)
ON
F.ScenarioID = FE.ScenarioID
AND SC.MemberID = FE.[Target CurrencyID]
AND DC.MemberID = FE.[CurrencyID]
AND F.TimeID = FE.TimeID
) F
GO
3. Create a Partition in the Model we want to apply the currency exchange to.
4. In the Settings tab of the partition, select the Table or View field and choose the database view we created.
After this is configured, you should be able to see the currency-converted values displayed.