Forum Discussion

joliver388's avatar
joliver388
New Contributor III
2 years ago

Complex Expression on Matrix Data Load

I have a complex expression on the amount field in a data source used to convert any non-local currencies to the local currency (see below). That complex expression currently runs on tabular data with the time period (CurTime) I reference to pull the FX rate in one field. I am now wanting to have this same complex expression used on a Matrix data source with time across 24 fields. Any thoughts on how this could be done? Is there a Column Number api I could use to define CurTime?

 

 

 

'This is to look to see if the entity is a non local USD entity and convert the USD data to Local
Dim fields As List(Of String) = StringHelper.Splitstring(args.Line,",")
Dim curEntity As String = fields(2)
Dim UKEntityDim As OneStream.Shared.Wcf.Dim = BRApi.Finance.Dim.GetDim(si, "iFinDept")
Dim UKEntityDimTypeID As Integer = UKEntityDim.DimPk.DimTypeId
Dim CurEntityID As Integer = BRApi.Finance.Members.GetMemberId(si,UKEntityDimTypeID , curEntity)
Dim CurEntityLocCur As String = BRApi.Finance.Entity.GetLocalCurrency(Si, CurEntityID).Name

If CurEntityLocCur.XFEqualsIgnoreCase("USD") Then
	Return Args.Value
Else
	'Get Workflow Scenario ID
	Dim wfScenarioName As String = ScenarioDimHelper.GetNameFromID(si, si.WorkflowClusterPk.ScenarioKey)
	Dim wfScenarioId As Integer = ScenarioDimHelper.GetIdFromName(si, wfScenarioName)
	'Pull the Current Time that we are looking at
	Dim CurTime As String = fields(1)
	
	'Brapi.ErrorLog.LogMessage(si, args.line.tostring)
		Dim FxRateType As String = BRApi.Finance.Scenario.GetFxRateTypeForRevenueExpense(si, wfScenarioId).Name
		Dim FxRatePkUsingNames As New FxRatePkUsingNames
			FxRatePkUsingNames.DestCurrency = "USD"
			FxRatePkUsingNames.SourceCurrency = CurEntityLocCur
			FxRatePkUsingNames.Time = CurTime
			FxRatePkUsingNames.FxRateType = FxRateType
		Dim FxRate As Decimal = BRApi.Finance.Data.GetStoredFxRate(si, FxRatePkUsingNames).Amount	
			If FxRate <> 0 Then			
				Return args.Value / FxRate
			End If	
End If

 

 

 

Parsing
  • Omkareshwar's avatar
    Omkareshwar
    Contributor II
    2 years ago

    Hi Joliver388, 

    Can you print args.line string and see how OneStream is processing that string I think it converts a matrix data source into a tabular in the backend and then process it accordingly I may be wrong but can you trying printing the arg.line line expression and share the results then what you can do is split that string into an array and select the value you want to check There will be 24 complex expressions for every time dimension.

    And if the above isn't working you can write a connector which will parse the file convert it into tabular format from matrix and then you can simply use the complex expression you already have.

    It will be great if you share args.line expression to work on this solution further.

    Thanks,

    Omkareshwar

    • joliver388's avatar
      joliver388
      New Contributor III
      2 years ago

      Thanks for the suggestion. It doesn't look like the data is converted at this stage of the process. Below is an example of how the args.line looks. It's one row of data with the amounts in 25 different fields. 

      I may look into converting the data source to tabular but first wanted to see what other options exist to handle this for a matrix load.

      10105,ACP001501,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5,5

       

      • Omkareshwar's avatar
        Omkareshwar
        Contributor II
        2 years ago

        You can attach your code to every time dimension which you have created and with args.value you can access the value on that dimension and with args.line you can access entire line from there you can fetch entity. 

        Your code should work with some modifications of col position rather than using a list of string split your args.line into an array. 

        Dim line as string = args.line

        Dim currentLine() As String = line.Split(","c) 

        Can you try this and share your results to debug further. 

        Thanks,

        Omkareshwar