Alternate Currency for Display

From the Design & Reference Guide:

Alternate Currency for Display: Use this setting to change the cube view grid currency. It does not recalculate the member based on the currency. This requires a formula on the member to recast the transaction from another member to the current currency.

This is available in U1 - 8. 

You could add a U8# as "CAD" for example and then if you write a dynamic member formula on that U8 it can pull data from U8#None member and calc to CAD based upon a member formula you write in U8#CAD.

Here is what the dynamic calc on U8#CAD would look like:

Dim timeId As Integer = api.Pov.Time.MemberPk.MemberId									'ID of the current time being processed
Dim rateTypeClosing As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability()		'FX Rate Type for Assets/Liabilities
Dim rateTypeAverage As FxRateType = api.FxRates.GetFxRateTypeForRevenueExp()			'FX Rate Type for Revenue/Expenses

Dim SourceCurrencyID As Integer = api.Entity.GetLocalCurrencyId(api.Pov.Entity.MemberId)
Dim USDCurrencyID As Integer = 176
Dim CADCurrencyID As Integer = 27

Dim acct As String = api.Pov.Account.Name
Dim acctTypeName As String = api.Account.GetAccountType(api.Pov.Account.MemberId).Name

'Only run if the view is not of an annotation type
If (Not ViewMember.IsAnnotationTypeViewId(api.Pov.View.MemberId)) Then
	
	If SourceCurrencyID = USDCurrencyID Then
		
		Dim CADRate As Decimal = api.FxRates.GetStoredFxRate(rateTypeClosing, api.Pov.Time.MemberId, CADCurrencyID, USDCurrencyID).Amount
		api.Data.FormulaVariables.SetDecimalVariable("CADRate",CADRate)

		Return api.Data.GetDataCell("A#" + acct + ":U8#None / $CADRate ")
	
	Else If acctTypeName.XFEqualsIgnoreCase("Liability") Or acctTypeName.XFEqualsIgnoreCase("Asset") Then
		
		Dim USDRate As Decimal = api.FxRates.GetStoredFxRate(rateTypeClosing, api.Pov.Time.MemberId, SourceCurrencyID, USDCurrencyID).Amount
		Dim CADRate As Decimal = api.FxRates.GetStoredFxRate(rateTypeClosing, api.Pov.Time.MemberId, CADCurrencyID, USDCurrencyID).Amount
		api.Data.FormulaVariables.SetDecimalVariable("USDRate",USDRate)
		api.Data.FormulaVariables.SetDecimalVariable("CADRate",CADRate)

		Return api.Data.GetDataCell("A#" + acct + ":U8#None * $USDRate / $CADRate ")
		
	Else
		
		Dim USDRate As Decimal = api.FxRates.GetStoredFxRate(rateTypeAverage, api.Pov.Time.MemberId, SourceCurrencyID, USDCurrencyID).Amount
		Dim CADRate As Decimal = api.FxRates.GetStoredFxRate(rateTypeAverage, api.Pov.Time.MemberId, CADCurrencyID, USDCurrencyID).Amount
		api.Data.FormulaVariables.SetDecimalVariable("USDRate",USDRate)
		api.Data.FormulaVariables.SetDecimalVariable("CADRate",CADRate)

		Return api.Data.GetDataCell("A#" + acct + ":U8#None * $USDRate / $CADRate ")
		
	End If
	
End If

Return Nothing

Then your cube view could dynamically calculate CAD and display it like in the last column: