Skip to main content
U
uvrao33
August 25, 2025
Question

How to get two decimal places in Member formula

  • August 25, 2025
  • 3 replies
  • 0 views

Hi Everyone,

I'm trying to write member formula (not dynamic calc), below is the syntax

Dim Var1 as Decimal = Math.Round(api.Data.GetDataCell("A#630000").CellAmount, 2)

api.Data.Calculate("A#520000 = A#430000 "*" & Var1 & "). after that getting error "Double is not valid, input string was incorrect format".

3 replies

R
rhankey
August 25, 2025

In short, the error you are getting is because you are attempting to concatenate a Decimal datatype without any conversion into a String.  The simple fix would be:

api.Data.Calculate("A#520000 = A#430000 *" & Var1.ToString & ")

However, the above is problematic depending on the culture of the user running the script, should let's say they format numbers as "0.000,00" rather than "0,000.00".  The safer way to do what you are doing is as follows:

Dim Var1 as Decimal = Math.Round(api.Data.GetDataCell("A#630000").CellAmount, 2)

api.Data.FormulaVariables.SetDecimalVariable("Var1",Var1)

api.Data.Calculate("A#520000 = A#430000 * $Var1")

 

U
uvrao33Author
August 27, 2025

Hi, the result is zero after using above script, I checked pov is correct and all source accounts have data.

R
rhankey
August 27, 2025

Well, then it is time to dump out the second data buffer to error log to see why you are getting zero.  You could be encountering a constraint issue.  Also dump out the value of Var1, as perhaps it is zero a NoData cell.

 

U
uvrao33Author
August 25, 2025

Hi rhankey​ ,

Thanks very much for your prompt response, I'll try it.

R
RobbSalzmann
August 25, 2025

Change the line

api.Data.Calculate("A#520000 = A#430000 "*" & Var1 & ")

to 

api.Data.Calculate("A#520000 = A#430000 * " & Var1.ToString(System.Globalization.CultureInfo.InvariantCulture))

 

U
uvrao33Author
August 27, 2025

Hi, the result is zero after using above script, I checked pov is correct and all source accounts have data.

R
RobbSalzmann
August 27, 2025

if you are getting 0, then one of the following is true:
account 430000 and/or 630000 is 0 or no data for the given POV.

Next step is to use either the excel addin, create a cubeview, or write to the log the POV and cell values for 430000 and 630000

Terms & ConditionsAccessibility statement