Blog - Saving Data Across Data Units with MemberScriptAndValue

I ran across an issue the other day that deserves writing about. I was trying to write to a member intersection, from a Custom Calculate Data Management Step, that was outside of the Data Unit defined in the Custom Calculate Data Management Step.

The Finance Business Rule that was being run was using an api.data.calculate approach, which unfortunately fails when trying to write outside of the data unit.

Dim strSource As String = "Cb#CAN_ACT:E#ONT_County1:C#None:S#Actual:T#2021M2:V#Periodic:A#A_69010:F#None:0#Forms:I#None" 
api.Data.Calculate("Cb#CAN_ACT:E#ONT_County1:C#None:S#Actual:T#2021M3:V#Periodic:A#A_69010:F#None:0#Forms:I#None = " & strSource, True)

If you try to use api.data.setDataCell, you get the same error.

api.Data.Calculate("Cb#CAN_ACT:E#ONT_County1:C#None:S#Actual:T#2021M3:V#Periodic:A#A_69010:F#None:0#Forms:I#None = " & strSource, True)

Solution: Using BRApi.Finance.Data.SetDataCellsUsingMemberScript

Step 1. Dim a List of MemberScriptAndValue. This will hold all the Member Script and Value combinations that you want to write to.

Dim 1stMemScriptAndValue As New List(Of MemberScriptAndValue)

Step 2. Dim a MemberScriptAndValue object. This object has several properties that will be used to define the Member Script, the Amount (Value) and if it contains data.

Dim msvMemScriptandValue As New MemberScriptAndValue

Step 3. Define the properties of the MemberScriptAndValue object that you instantiated. IsNoData is an interesting property, if you set this to True it clears the amount from the data cell.

										
msvMemScriptandValue.IsNoData = False ' If Set to True it will clear the data from the data intersection 
msvMemScriptandValue.Amount = 700 'Target Member Script to be written to 
msvMemScriptandValue.Script = 'Cb#CAN_ACT:E#ONT_County1:C#None:S#Actual:T#2021M3:V#Periodic:A#A_69010:F#None:0#BeforeAdj:I#None'
										
									

Step 4. Add the MemberScriptAndValue object that you just defined to the list object of MemberScriptAndValue that you created in Step 1. If you have multiple scripts, you can iterate through your member script combinations and add each of them to your list object. It can hold more than 1 MemberScriptAndValue definition. I have used this to hold hundreds of defined MemberScriptAndValue combinations.

1stMemScriptAndValue.Add(msvMemScriptAndValue)

Step 5. Write the list MemberScriptAndValue to the cube using BRApi.Finance.Data.SetDataCellsUsingMemberScript. Note that the if then in the code below will return an error if any of your member scripts are inaccurate.

If 1stMemScriptAndValue.Count > 0 Then 
 Dim objXFResult As XFResult = BRApi.Finance.Data.SetDataCellsUsingMemberScript(si, 1stMemScriptAndValue) 
  If Not objXFResult.BoolValue Then 
  Throw ErrorHandler.LogWrite(si,objXFResult.Message,String.empty))
 End If 
End If

This example only writes a single member script and value combination to the cube, however I have used it to load the contents of a user loaded CSV file. The CSV File was first loaded to a custom SQL Table in the Application database. Then the contents of that SQL Table were retrieved into a VB.Net Data Table stored in memory. Then the rows of the data table were looped through, generating a Member Script and Value object with the Script, Amount and isNoData properties being defined for each row in the data table. The MemberScriptAndValue object would then be added to the list of MemberScriptAndValue object. Once the Data Table was completely looped through the list of MemberScriptAndValue object was then written to the OneStream cube.

Keep in mind that MemberScripAndValue writes to O#Forms and has a Storage Type of Input, even though it is the result of a calculation.

The business rule in its entirety, with a bonus section showing how to write the contents of your MemberScriptAndValue list to the error log is shown below.

Public Function MSV(ByVal si As SessionInfo, ByVal globals as BRGlobals, ByVal api As FinanceRuleApi, ByVal args As FinanceRulesArgs)
 Try
  Dim 1stMemScriptAndValue As New List(Of MemberScriptAndValue)
  Dim msvMemScriptandValue As New MemberScriptAndValue
   msvMemScriptandValue.IsNoData = False ' If Set to True it will clear the data from the data intersection 
   msvMemScriptandValue.Amount = 700 'Target Member Script to be written to 
   msvMemScriptandValue.Script = 'Cb#CAN_ACT:E#ONT_County1:C#None:S#Actual:T#2021M3:V#Periodic:A#A_69010:F#None:0#BeforeAdj:I#None'
   1stMemScriptAndValue.Add(msvMemScriptAndValue)
   
   If 1stMemScriptAndValue.Count > 0 Then 
 Dim objXFResult As XFResult = BRApi.Finance.Data.SetDataCellsUsingMemberScript(si, 1stMemScriptAndValue) 
  If Not objXFResult.BoolValue Then 
  Throw ErrorHandler.LogWrite(si, NewXFException(si,objXFResult.Message,String.empty))
 End If 
End If

'Write MemberScriptAndValue contents to error log
Dim logger As New Text.StringBuilder
For Each msv In 1stMemScriptAndValue
    logger. AppendLine("MSV Script = " & msv.Script & " Amount = " & msv.Amount & " IsNoData = " & msv.IsNoData)
Next
brapi.ErrorLog.LogMessage(si, logger.ToString)
Return Nothing

Catch ex As Exception
    Throw ErrorHandler.LogWrite(si, New XFException(si,ex))
End Try
End Function