The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
Solved
Using a variable in the calculate string formula
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 varia...
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.
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 decimalDim value As Decimal = 123.456789D
Dim formattedString As String = $"The value is {value:F2}" ' This will format the value to 2 decimal placesDim 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"