get data value from BRApi.Finance.Data.GetDataCellUsingMemberScript

grillin7
New Contributor II

Hi, I'm trying to get the amount of an objDataCellInfoUsingMemberScript but I couldn't get the data, I'm trying this:

objDataCellInfoUsingMemberScript = BRApi.Finance.Data.GetDataCellUsingMemberScript(si,"KemetFinRptg", "E#LEGAL_Y:C#Local:S#Actual_YAGEO_Fx:T#2022M1:V#MTD:A#702006:F#Top:O#Top:I #Top:UD1#0021:U2#Top:U3#Top:U4#Top:U5#Top:U6#None:U7#None:U8#None" )
'String to convert
Dim stringToConvert As String = objDataCellInfoUsingMemberScript.DataCellEx '<-- Update string to be converted


'Convert to double. Set to 0 if string if not able to be converted
Dim dblValue As Double = stringToConvert.XFConvertToDouble(0)


 

grillin7_0-1675028422022.png

 

1 ACCEPTED SOLUTION

JackLacava
Community Manager
Community Manager

Not sure what you're trying to do with that string roundtrip. This should work:

Dim myCell As DataCellInfoUsingMemberScript = BRApi.Finance.Data.GetDataCellUsingMemberScript(si, _
    "myCube", _
    "S#MyScenario:E#MyEntity: ... etc etc") 
Dim myValue as Decimal = myCell.DataCellEx.DataCell.CellAmount
						

Generally speaking, it's safer to work with Decimal values than Doubles, as they are less prone to "surprises" when rounding. If you have to drop it into a formula, there are a number of approaches, this is the one I like:

api.Data.FormulaVariables.SetDecimalVariable("myFixedAmount", myValue)
api.Data.Calculate("A#Target = A#Source * $myFixedAmount")

 

View solution in original post

8 REPLIES 8

JackLacava
Community Manager
Community Manager

Not sure what you're trying to do with that string roundtrip. This should work:

Dim myCell As DataCellInfoUsingMemberScript = BRApi.Finance.Data.GetDataCellUsingMemberScript(si, _
    "myCube", _
    "S#MyScenario:E#MyEntity: ... etc etc") 
Dim myValue as Decimal = myCell.DataCellEx.DataCell.CellAmount
						

Generally speaking, it's safer to work with Decimal values than Doubles, as they are less prone to "surprises" when rounding. If you have to drop it into a formula, there are a number of approaches, this is the one I like:

api.Data.FormulaVariables.SetDecimalVariable("myFixedAmount", myValue)
api.Data.Calculate("A#Target = A#Source * $myFixedAmount")

 

kberry
New Contributor III

The Member Script Builder is also handy in these cases. Here is an example from the Design & Reference Guide:

Dim MemberscriptBldr = New MemberscriptBuilder("V#YTD:A#Cash")
MemberscriptBldr.SetFlow("None").SetOrigin("Import").SetIC("None").SetAllUDsToNone()
Dim Memberscript As String = MemberscriptBldr.GetMemberscript()
Dim objDataCell As DataCell = api.Data.GetDataCell(Memberscript)

grillin7
New Contributor II

Hello sir,

Thank you a lot.

that solution works.

grillin7_0-1675099184358.png

I think the problem was with the double variable, I put decimal and it works perfectly, thank you.

Hi Jack - I have a quick question. Can I do something like below ?

 

 

Dim Cbe As String = "Base"
								Dim Msct As String = "E#1:C#Local:S#Actual:T#2023M6:V#YTD:A#1025000:F#Check_Movement:O#Top:I#Top:U1#PC_Root:U2#Dept_Root:U3#LOB_Root:U4#Prod_Root:U5#None:U6#None:U7#Source:U8#None + E#1:C#Local:S#Actual:T#2023M6:V#YTD:A#1025000:F#Check_Movement:O#Top:I#Top:U1#PC_Root:U2#Dept_Root:U3#LOB_Root:U4#Prod_Root:U5#None:U6#None:U7#CVC:U8#None + E#1:C#Local:S#Actual:T#2023M6:V#YTD:A#1025000:F#Check_Movement:O#Top:I#Top:U1#PC_Root:U2#Dept_Root:U3#LOB_Root:U4#Prod_Root:U5#None:U6#None:U7#Acq:U8#None"
								
								Dim myCell As DataCellInfoUsingMemberScript = BRApi.Finance.Data.GetDataCellUsingMemberScript(si,Cbe,Msct)
Dim myValue As Decimal = myCell.DataCellEx.DataCell.CellAmount

 

 It is not working and returning 0 

"Member Script" is a set of coordinates to a single cell. What you're trying to do is actually evaluating a GetDataCell formula. Just use api.Data.GetDataCell for that, and it should work.

Thanks Jack. I am doing this in extender business as part of Form Event to check the cell status  rule not in Finance,

Then just retrieve the individual amounts separately, with multiple calls to BrApi.Finance.Data.GetDataCellUsingMemberScript, and perform the sum in your code.

There are alternatives that might be a bit more optimized behind the scenes (GetDataCellsUsingMemberScript, or GetDataBufferDataCells), but for 3 cells I wouldn't bother dealing with the extra complexity they require.

Make Sense Thanks. Appreciate your quick response.

Please sign in! grillin7