Skip to main content
A
AndreaF
August 16, 2023
Solved

Using a variable in the calculate string formula

  • August 16, 2023
  • 6 replies
  • 0 views

Hi,

I am in a custom business rule.

 

I have a calculate formula like the following one, which works fine:

api.Data.Calculate("A#Sales1 = A#Sales2 + 50”)

 

Now let say I want to declare a variable instead of having the 50 typed in the formula string, and pass that to the Calculate, what is the correct syntax to do that? Is it possible?

I would like to do something like this (the one below is not the correct syntax, but it gives an idea of what I am trying to do):

Dim Growth As Decimal = 50

api.Data.Calculate("A#Sales1 = A#Sales2 + Growth”)

 

Thank you

 

Best answer by JackLacava

The safest approach is this:

Api.Data.FormulaVariables.SetDecimalVariable("Growth", 50)

API.data.Calculate("A#Sales1 = A#Sales2 + $Growth”)

This approach avoids possible issues with converting numbers to string - when the user executing it has a different Culture, or when decimals get truncated (hence losing precision). There is a blog post discussing it as part of internationalising an application.

6 replies

A
AndreaFAuthor
August 16, 2023

Just a note to say that in the actual formula I am doing the additional dimensional filtering in the Calculate, the formula above is only for exemplification

R
RobbSalzmann
August 16, 2023

Here you go:

Dim Growth As Decimal = 50
api.Data.Calculate($"A#Sales1 = A#Sales2 + {Growth}”)
A
AndreaFAuthor
August 17, 2023

This formula gives me the following error:

"Unable to execute Business Rule 'Plan'. Invalid calculation script. Unexpected text. {Growth}."

B
Big_Rick_CPM
August 31, 2023

The method Robb mentions is using string interpolation. The error message you sent to me sounds like you are missing the dollar sign ($) before the literal string that tells the complier it is an interpolated string. 

J
JackLacavaAnswer
August 16, 2023

The safest approach is this:

Api.Data.FormulaVariables.SetDecimalVariable("Growth", 50)

API.data.Calculate("A#Sales1 = A#Sales2 + $Growth”)

This approach avoids possible issues with converting numbers to string - when the user executing it has a different Culture, or when decimals get truncated (hence losing precision). There is a blog post discussing it as part of internationalising an application.

D
DanielWillis
September 1, 2023

Hey Jack, just wanted to understand how this issue occurs a little more.

Are you saying

- BRs get executed in the OneStream culture setting of the user

- So when Growth gets converted to a string as in Robb's example then it will convert a decimal to a string in the format of the user, so if user is French and Growth is 50.123/50,123 it will result in something like API.data.Calculate("A#Sales1 = A#Sales2 + 50,123”)? Which wouldn't work because OneStream would be expecting API.data.Calculate("A#Sales1 = A#Sales2 + 50.123”)?

J
JackLacava
September 1, 2023

Formulas expect numbers formatted in Invariant Culture (basically US), but in some contexts the process is actually executed under the user's own Culture, resulting in the behaviour you describe.

In addition, even if you convert from number to string in Invariant, there is a significant chance that numbers will be truncated, losing precision.

So the best approach is to never convert Decimals to String at all, and to assign them to a Formula Variable instead.

R
RobbSalzmann
September 1, 2023

My understanding with .net is that string interpolation of a decimal value preserves precision unless explicity told not to.   I think JackLacava makes an interesting point about culture, due to disparate use of decimal points and commas.  I'm not convinced that effects the precision of a calculation as much as the presentation of the results?


Dim value As Decimal = 123.456789D
Dim formattedString As String = $"The value is {value}" ' This will maintain the precision of the decimal
Dim value As Decimal = 123.456789D
Dim formattedString As String = $"The value is {value:F2}" ' This will format the value to 2 decimal places
Dim value As Decimal = 123456.789D
Dim frenchCulture As New System.Globalization.CultureInfo("fr-FR")
Dim frenchFormattedString As String = String.Format(frenchCulture, "The value in French format is {0:N}", value)
' The output will be: "The value in French format is 123 456,789"

Dim value As Decimal = 123456.789D
Dim japaneseCulture As New System.Globalization.CultureInfo("ja-JP")
Dim japaneseFormattedString As String = String.Format(japaneseCulture, "The value in Japanese format is {0:N}", value)
' The output will be: "The value in Japanese format is 123,456.789"
R
RobbSalzmann
September 1, 2023

No precision lost in this test comparing parsed interpolated strings to straight Decimals:

RobbSalzmann_1-1693580189993.png

RobbSalzmann_2-1693580655028.png

 

 

J
JackLacava
September 1, 2023

WriteLine converts to String though, doesn't it...? So if there is truncation, you're just showing it being done twice.

But good for trying things out, I'd be happy to be disproven. Generally speaking, internally we've been told to push formula variables as best practice, since it's definitely the safest option.

R
RobbSalzmann
September 1, 2023

This is a good discussion.  it highlights some important things in computer science in general.  

JackLacava Your question "WriteLine converts to String though, doesn't it...? So if there is truncation, you're just showing it being done twice."  got me thinking...

The following code demonstrates the parsing of an interpolated decimal over a million accumulating iterations of a calculation and compares it to the same million iterations of direct decimal calculation.  I think this qualifies interpolation as not changing precision.

** The key to this test is the comparison of the results is made by the code, before being written to the screen as String values.

 

RobbSalzmann_1-1693582199869.png

 

 

Terms & ConditionsAccessibility statement