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
SetDataCellsUsingMemberScript does not write all list items
Hello All,
SetDataCellsUsingMemberScript() seems to only write the last value added to list. I am successfully adding 14 records to the objMemberScriptValues list (I can loop through them and see the 14 unique intersections) but only the last one is being updated in the cube.
I'm looping through the records in a datatable and then each intersection is added to the list:
'***********************************************
For Each row As DataRow In dt.Rows
strProfileName = row.Item("ProfileName").ToString
strCube = row.Item("Cube").ToString
strEtT = row.Item("EtT").ToString
strCnT = row.Item("CnT").ToString
strSnT = row.Item("SnT").ToString
strTmT = row.Item("TmT").ToString
strVwT = row.Item("VwT").ToString
strAcT = row.Item("AcT").ToString
strFwT = row.Item("FwT").ToString
strOgT = "Forms"
strIcT = row.Item("IcT").ToString
strU1T = row.Item("U1T").ToString
strU2T = row.Item("U2T").ToString
strU3T = row.Item("U3T").ToString
strU4T = row.Item("U4T").ToString
strU5T = row.Item("U5T").ToString
strU6T = row.Item("U6T").ToString
strU7T = row.Item("U7T").ToString
strU8T = row.Item("U8T").ToString
dAmount = row.Item("Am")
Dim objMemberScriptValue As New MemberScriptAndValue
Dim objMemberScriptValues As New List(Of MemberScriptAndValue)
Dim strMemberScript As String
strMemberScript = "Cb#" & strCube & ":E#" & strEtT & ":C#" & strCnT & ":S#" & strSnT & _
":T#" & strTmT & ":V#" & strVwT & ":A#" & strAcT & ":F#" & strFwT & _
":O#" & strOgT & ":I#" & strIcT & ":U1#" & strU1T & ":U2#" & strU2T & _
":U3#" & strU3T & ":U4#" & strU4T & ":U5#" & strU5T & ":U6#" & strU6T & ":U7#" & strU7T & ":U8#" & strU8T
objMemberScriptValue.Amount = dAmount
objMemberScriptValue.IsNoData = False
objMemberScriptValue.Script = strMemberScript
objMemberScriptValues.Add(objMemberScriptValue)
Next
Dim objXFResult As XFResult
If objMemberScriptValues.Count > 0 Then
objXFResult = BRApi.Finance.Data.SetDataCellsUsingMemberScript(si, objMemberScriptValues)
If Not objXFResult.BoolValue Then
Throw ErrorHandler.LogWrite(si, New XFException(si, objXFResult.Message, String.Empty))
End If
End If
'***********************************************
When I place the objFXResult code block within the For/Next loop it will successfully update each record one by one as they are added to the objMemberScriptValues list, but as it is written above where I'm adding each record to the list and then executing the objXFResult code block outside the For/Next loop, only the last record added to the list is written to the cube, even though the count of items in the list shows that the list contains all 14 records.
Does anyone know what I may be doing incorrectly here? Can the list only contain one list item to be updated one at a time?
You need to move this line to be before the For loop starts:
Dim objMemberScriptValues As New List(Of MemberScriptAndValue)
4 Replies
The For Loop Next should be after the End If, then it will write all the values!!!!
Correct, it does, but it commits one row at a time, which is very inefficient. I was looking for a way to write all the rows to the list in memory and commit them all at once to improve performance.
You need to move this line to be before the For loop starts:
Dim objMemberScriptValues As New List(Of MemberScriptAndValue)
Thanks, I knew it was going to be something obvious! I still had to instantiate the objMemberScriptValue object As New within the loop otherwise it wrote the same row data to the list 14 times, declaring as New within the loop added the individual records to the list. Moving just the List object instantiation before the loop worked. Thanks!
