Solved

Unable to Calculate Currency Override only for Accounts with Text Filter.

Forum|Forum|3 years ago
April 18, 2023
6 replies
0 views

Hello All,

I am new to OneStream trying to write a Value Override business rule for some investment accounts, The rule works without account filter. How ever I want to restrict this to Accounts with Text2 tag as "CADOverride". Somehow the value becomes zero if I have the Text 2 filter and not sure where am I doing wrong, (I am currently just checking if text 2 is not null and it seems to be retuning empty)

Dim foreignCurrency As String = api.Pov.Cons.Name
If ((Not api.Entity.HasChildren()) And (api.Cons.IsForeignCurrencyForEntity())) Then
	Dim overrideAcc As String = api.Account.Text(api.Pov.Account.MemberId, 2)
	If String.IsNullOrEmpty(overrideAcc) Then

		Select Case foreignCurrency
		Case "CAD"
			api.data.calculate("F#EndBal= RemoveNoData(F#CADOverrideInput:C#Local)")
			
		End Select
	End If
End If

How should I proceed to troubleshoot this?

Any ideas are appreciated,

Thanks,

Manoj

Finance

Best answer by ChrisLoran

You might like to consider this example as a starting point, which looks in C#Local for any populated cells on special flows that have Alternate Currency Input , and copies those appropriate currency inputs into F#EndBal on the corresponding data unit for that translated ccy.

I recommend using the Alternate Currency Input properties of the flows, rather than assuming flow naming convention, to obtain the currency:

				If Not api.Entity.HasChildren() _
				AndAlso api.Cons.IsForeignCurrencyForEntity() Then
					Dim dataUnitCcyId As Integer = api.Cons.GetCurrency(api.Pov.Cons.MemberId).Id
					Dim id_EndBal As Integer = api.Members.GetMemberId(DimTypeId.Flow, "EndBal")

					' --- assumes F#[OverridesParent] is the parent of all flows that have Alternate Currency Input --
					Dim di As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo(String.Empty)
					Dim buf As DataBuffer = api.data.GetDataBufferUsingFormula("FilterMembers(C#Local,F#[OverridesParent].Base)",DataApiScriptMethodType.Calculate,False,di)
					Dim resultBuf As New DataBuffer()
					
					'--- now loop through the populated cells for flows that have Alternate Input currencies ---
					For Each bufCell As DataBufferCell In buf.DataBufferCells.Values
						If Not bufCell.CellStatus.IsNoData AndAlso bufCell.CellAmount <> 0 Then
							' -- test if the flow member represents an alternate Input currency ---
							Dim AltCcyIdForFlow As Integer = api.Flow.GetAlternateInputCurrencyId(bufCell.DataBufferCellPk.FlowId)
							If AltCcyIdForFlow = dataUnitCcyId Then
								Dim resultCell As New DataBufferCell(bufCell)
								resultCell.DataBufferCellPk.FlowId = id_EndBal
								resultBuf.SetCell(si,resultCell,True)
							End If
					End If
						
					Next
					api.Data.SetDataBuffer(resultBuf, di)

C

ChrisLoran

I think part of the problem is the reference to
api.Pov.Account

What account are you expecting it to be, since a Finance business rule is executed for varying combinations of Cube/Scenario/Time/Cons/Entity/Parent/, so these Data Unit dimension types can be referenced using api.pov.
That is *not* the case for account-type dimensions.

You are trying to get the text property of which account?
Or do you need to scan through the Data Unit and find all accounts that may have a Text2 property, and then process accordingly? In that case, a DataBuffer would be more suitable.

Also try to avoid using magic strings, such as "CAD". Instead use the Currency classes , like this

That way you get (a) guidance on what currency codes are available, and (b) protects against mistyping, and (c) avoids string complications, such as case sensitivity issues etc.

J

JackLacava

If this is in a Member Formula, or in a Business Rule in the Calculate case - there is no "Api.Pov.Account", and I believe that api.Account.Text() call will fall back to the None Account. In OS, all accounts (and then flows, UD1s etc) are calculated at the same time for a single Data Unit (single combination of members for Cube/Time/Scenario/Entity/Parent/Consolidation). Even though you're splitting your code across members, it all effectively runs in one go - it's split just to determine the order of operations (by dimension type, pass, and hierarchical location for multithreading purposes) and to facilitate logical maintenance.

I strongly suggest to read up on the calculation mechanics, either from the book, the Design and Reference Guide, or Navigator courses.

As for your specific problem, you have to switch your mindset around: instead of checking "am I in a certain account?", you have to think "which accounts do I want to target?". There are a few ways to go about that, from simple but inefficient (looping through a list of members executing Calculate calls) to fast but more complex (leveraging DataBuffers and Formula Variables).

M

mpavanAuthor

Thanks for the explanation!

I understand the simple way of looping through a member list.

https://community.onestreamsoftware.com/t5/Accepted-Code-Samples/Using-the-EVAL-function-to-loop-through-a-tagged-set-of-Balance/ta-p/6006

Is the above post similar to what you are referring for Data Buffers?

C

ChrisLoran

An api.data.calculate uses DataBuffers under the hood.
Api.Data.Calcualte is like a convenience tool, to provide developers with a shorter "front-end" to implementing explicit databuffer methods. But under the hood, it is dealing with data buffers.

The same with Api.Data.Calculate with the Eval() or Eval2() functions. This is just telling the Finance Engine to expose it's internal DataBuffer that it generates from your calculate statement, so you can intercept the cells being processed by the calculate.

I would avoid looping through a member list to perform finance calcs. That will usually cause performance problems. Especially if you have api.data.calculate inside a Member List loop.

In the Finance Rules and Calculations Handbook, Chapter 3 discusses this topic in detail with examples, showing how the Api.Data.Calculate gets broken down into Data Buffers, talks about Data Buffer Math, interesting use-cases, how to use Formula Variables, how to use Data Buffers with different dimensionality and lots more.

C

ChrisLoranAnswer

You might like to consider this example as a starting point, which looks in C#Local for any populated cells on special flows that have Alternate Currency Input , and copies those appropriate currency inputs into F#EndBal on the corresponding data unit for that translated ccy.

I recommend using the Alternate Currency Input properties of the flows, rather than assuming flow naming convention, to obtain the currency:

				If Not api.Entity.HasChildren() _
				AndAlso api.Cons.IsForeignCurrencyForEntity() Then
					Dim dataUnitCcyId As Integer = api.Cons.GetCurrency(api.Pov.Cons.MemberId).Id
					Dim id_EndBal As Integer = api.Members.GetMemberId(DimTypeId.Flow, "EndBal")

					' --- assumes F#[OverridesParent] is the parent of all flows that have Alternate Currency Input --
					Dim di As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo(String.Empty)
					Dim buf As DataBuffer = api.data.GetDataBufferUsingFormula("FilterMembers(C#Local,F#[OverridesParent].Base)",DataApiScriptMethodType.Calculate,False,di)
					Dim resultBuf As New DataBuffer()
					
					'--- now loop through the populated cells for flows that have Alternate Input currencies ---
					For Each bufCell As DataBufferCell In buf.DataBufferCells.Values
						If Not bufCell.CellStatus.IsNoData AndAlso bufCell.CellAmount <> 0 Then
							' -- test if the flow member represents an alternate Input currency ---
							Dim AltCcyIdForFlow As Integer = api.Flow.GetAlternateInputCurrencyId(bufCell.DataBufferCellPk.FlowId)
							If AltCcyIdForFlow = dataUnitCcyId Then
								Dim resultCell As New DataBufferCell(bufCell)
								resultCell.DataBufferCellPk.FlowId = id_EndBal
								resultBuf.SetCell(si,resultCell,True)
							End If
					End If
						
					Next
					api.Data.SetDataBuffer(resultBuf, di)

M

mpavanAuthor

Hello ChrisLoran,

I was wondering what the option is for and thanks for the code. Although we have only one parent currency this gives us flexibility in future and will incorporate this code.

The reason I am trying to limit the account for Override copy is, in case users Input data at random accounts value should not be copied.

C

ckattookaran

You can do the same if logic once you are inside the buffer loop. I would use a buffer math like Chris showed. You'll only write to the result buffer if the condition matches.

Y

Yashwant

I think OnEvalDataBuffer, function will help you to loop through accounts and you can check for string in Text properties.

Design reference guide has given sample code for the same. Please check.

C

ckattookaran

On eval works on data buffers, so the above example will work. Yes, you are correct in pointing out the eval function on member formulas. However, in this case as he is trying to limit the left-hand side I'm not so sure. Or I'm still sleep deprived and have no clue.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded