Calculate Data
The most common function used is api.data.calculate, which sets the value of one or more values (left side of Formula) equal to another (right side). A final argument (optional) can be added as True or False as to whether to use a data cell Storage Type of Durable. Durable data will not be cleared automatically when a Data Unit is re-calculated. It can only be cleared by calling api.Data.ClearCalculatedData with the clearDurableCalculatedData boolean property set to True.
api.Data.Calculate(formula, isDurableCalculatedData)
For example, the following Stat Account is used to calculate Total Cost of Sales for three months and is not set to be a Durable storage method (optional argument):
api.data.calculate("A#TOT_COS_LAST3:V#YTD = A#TOT_COS:V#Periodic + A#TOT_COS:T#POVPrior1:V#Periodic +
A#TOT_COS:
T#POVPrior2:V#Periodic", False)
An alternative to this overloaded function is to provide Member Filters (all optional) that can be used to filter the results before saving them to the target to affect fewer intersections, such as only to be applied to certain Flow members:
api.Data.Calculate(formula, accountFilter, flowFilter, originFilter, icFilter, ud1Filter, ud2Filter, ud3Filter, ud4Filter, ud5Filter, ud6Filter, ud7Filter, ud8Filter, onEvalDataBuffer, userState, isDurableCalculatedData)
Another alternative allows the use of arguments to be applied:
api.Data.Calculate(Formula, onBeforeSetDataBuffer, userState, arg0, arg1, arg2, arg3, arg4, arg5, arg6, arg7)
Clear Calculated Data
Clearing calculated data is performed when calculated Members need to be reset to NoData. This will result in the Data Units requiring a calculation. Note that the final argument must be true to clear any data with a Storage Type of Durable. See Calculate Data for how to set a calculation to store data as Durable.
api.Data.ClearCalculatedData(dataBufferScript, clearCalculateData, clearTranslationData,
clearConsolidationData,
clearDurableCalculatedData)
api.Data.ClearCalculatedData("A#[cash deposits]:ud1#Production", true, true, true, false)
DataBuffer
The DataBuffer log helps debugging and troubleshooting. It allows datastring results to be written to any target and multiple string results can be merged. The output is formatted as Tab delimited for copy/paste to Excel. Results include the DataBuffer formula, the size and number of records and displays the XFCommon member PK. It also contains a default record limit to guard against log explosion.
Examples of DataBuffer outputs:
-
Error Log
-
Send to SQL
-
Send to a File
-
Email
-
FTP
When setting a value equal to another value, the item on the left side of the expression is the value being set, and the item on the right side is the value being queried or calculated to set the left side. Example: F#BeginBalance = F#EndingBalance.T#POVPrior1 would set the beginning balance in the Flow Dimension to the prior period’s ending balance. In a Business Rule, the DestinationInfo is the left side of the equation while a GetDataBuffer is the right side of the equation.
Dim destinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo
("A#EBITDA:UD1#Tires")
Dim sales As DataBuffer = api.Data.GetDataBuffer("A#Sales:UD1#Tires", destinationInfo)
Dim operatingExpenses As DataBuffer = api.Data.GetDataBuffer("A#OperatingExpenses:UD1#Tires",
destinationInfo)
Dim ebitda As DataBuffer = (sales – operatingExpenses)
api.Data.SetDataBuffer(ebitda, destinationInfo)
This translates to the following equation:
A#EBITDA:UD1#Tires=A#Sales:UD1#Tires - A#OperatingExpenses:UD1#Tires
GetDataBufferUsingFormula
Use an entire math expression to calculate a final data buffer. Api.Data.GetDataBufferUsingFormula can perform the same data buffer math as api.Data.Calculate, but the result is assigned to a variable where api.Data.Calculate saves the calculated data.
Example
Dim myDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula (“A#Sales-A#Costs”)
Loop over the contents of myDataBuffer to conditionally change each data cell.
Formula Variables
There is additional capability to using Formula Variables to achieve the same level of flexibility and integration as using Evals. After creating a data buffer variable, name it as a Formula Variable and reference it inside api.Data.Calculate or other calls to api.Data.GetDataBufferUsingFormula. This provides flexibility and can improve performance because the Data Buffer is calculated once and the variable is re-used multiple times.
Example
Dim myDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula (“A#Sales-A#Costs”) api.Data.FormulaVariables.SetDataBufferVariable(“myDataBuffer”,myDataBuffer,False) api.Data.Calculate(“A#Profit=A#5000 + $myDataBuffer
Use api.Data.FormulaVariables.SetDataBufferVariable to name the data buffer. Pass in any name followed by the data buffer variable. Enter a True/False value for Uses Indexes to Optimize Repeat Filtering. Using True will re-use the same data buffer using FilterMembers and improve performance. After naming the data buffer, use a dollar sign and the name when referencing it in a script.
FilterMembers
Use this inside of an api.Data.Calculate or api.Data.GetDataBufferUsingFormula script.
Example
Dim myDataBuffer As DataBuffer Api.Data.GetDataBufferUsingFormula(“FilterMembers(A#All,A#6000,[A#[3600].Base])”)
Change a data buffer and only include numbers for the specified Dimensions. The first parameter is the starting data buffer. This can be a variable name or an entire math equation in parentheses. There can be as many parameters as needed to specify Member Filters and different Member Filters can be used for multiple Dimension types. The resulting filtered data buffer will only contain numbers that match the Members in the filters.
RemoveMembers
This uses the same syntax as FilterMembers, but it takes the data cells away for the specified Members instead of keeping them.
Retrieving Member Names from IDs
This retrieves the Member names from the IDs when looping over the cells in a data buffer.
Example
For Each sourceCell As DataBufferCell In myDataBuffer.DataBufferCells.Values Dim accountName As String = sourceCell.DataBufferCellPk.GetAccountName(api) Dim ud1Name As String = sourceCell.DataBufferCellPk.GetUD1Name(api) If ud1Name = "None" Then BRApi.ErrorLog.LogMessage(si, "UD1"& ud1Name, Nothing) Next
Logging Contents of a Data Buffer
Log the contents of a Data Buffer to the Error Log when writing Business Rules in order to make corrections and troubleshoot along the way.
Example
myDataBuffer.LogDataBuffer(api,”MyDataBufferOutput”,1000)
The third Parameters (1000) indicates the maximum number of cells to include in the log and displays what is in the data buffer.
Example api
api.LogMessage(XFErrorLevel.Information, "MyDataBuffer As a CSV String For Excel", myDataBuffer.GetCSVString(api, False, 1000))
The false Parameter specifies whether to include Member IDs in the output. Member names are always included.
Remove Functions in Formulas
Remove Zeros
The RemoveZeros function evaluates a source data buffer and removes all cells where the amount is zero whether it is flagged as a NoData cell or not. Therefore, it is not necessary to use both RemoveZeros and RemoveNoData together since RemoveZeros handles both situations. This can be identified on individual cells as the Cell Amount under Cell Status.
This function is important to use for performance purposes. Use the RemoveZeros function in calculations where there is a substantial amount of No Data or 0.00 cells in Data Units. This can be determined by looking at the Data Unit Statistics when right-clicking on a cell in a Cube View.
Example using api.Data.Calculate:
'Run for base Entities and local currency only
If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyforEntity())) Then
api.Data.Calculate("A#CashCalc=RemoveZeros(A#10000)")
End If
Example using GetDataBufferUsingFormula:
Remove NoData
The RemoveNoData function evaluates a source data buffer and removes data cells that have a cell amount of NoData.
This function is important to use for performance purposes. Use the RemoveNoData function in calculations where there is a substantial number of cells with a Cell Status of NoData in Data Units. This can be determined by looking at the Data Unit Statistics when right-clicking on a cell in a Cube View.
Example using api.Data.Calculate:
'Run for base Entities and local currency only
If ((Not api.Entity.HasChildren()) And (api.Cons.IsLocalCurrencyforEntity())) Then
api.Data.Calculate("A#CashCalc=RemoveNoData(A#10000)")
End If
Example using GetDataBufferUsingFormula:
Performance Note: In the calculation performance testing for a single Data Unit, the use of RemoveZeros and RemoveNoData using api.Data.Calculate and/or GetDataBufferUsingFormula rendered a significant performance advantage. This is testing the before and after calculation time of a single formula change for a Data Unit. Times may vary from Data Unit to Data Unit and Application to Application. However, the use of RemoveZeros and RemoveNoData is highly recommended in formulas where Cube and Dimensionality designs lend to sparse data models. This is not limited to sparse data models as this can be identified in dense data models as well.
Dynamic Calc Using GetDataCell
A Dynamic Calc returns an Account value on demand without storing the data in the Cube. These Members cannot be referenced by other Members in calculating their values. Dynamic Calcs are best used in Members required for reporting only. One ideal use is the GetDataCell function in ratio-style statistical Accounts (e.g. Current Ratio).
Return api.Data.GetDataCell("Divide(A#[CurrentAssets], A#[CurrentLiabilities]")
Dynamic Filters and Aliases
Create Dynamic Member Filters to assign to rows or columns within Cube Views. A common example of a Variance column that subtracts one Member from another is shown below. Adding the :Name(“Membername”) option at the end gives the ability to assign a display name to the header of the Cube View. The double quotes in the Name() function are optional.
Cube View Filter: GetDataCell(“Variance(T#POV,T#Prior12)”):Name("Variance")
Error Traps
Try
if api.POV.Cons.name =api.POV.GetEntityCurrency() then
api.Data.Calculate("A#Cash1=A#[Restricted Cash] + 70000")
End If
catch ex as exception
api.LogError(ex)
End Try
Math Functions
Use the math functions built into VB.NET.
Examples:
Absolute Value - math.abs()
Rounding - math.round()
Several other supported math functions and examples are listed here:
http://msdn.microsoft.com/en-us/library/thc0a116%28v=VS.90%29.aspx
Member List Functions
When a Cube filter is run and the filter value does not match a Member, the system looks in all Business Rules for the MemberListProperty property to find a matching Member list name. Users should create MemberLists in Business Rules that are not assigned to Cubes because the entire Business Rule will be run if the system finds a matching Member list name. Reference MemberLists like this: E#Root.PartnerList.
The following example first declares the two MemberListHeader names of Sample Member List and PartnerList. It then addresses each MemberList. Sample Member List shows a way to get the children of an Entity. E#Texas.[Sample Member List] is just another way of saying E#Texas.Children. In the PartnerList example, E#Root.PartnerList will generate a list that includes Paris and Nice.
Case Is = FinanceFunctionType.MemberListHeaders
‘ Additional logic that defines the names of the two custom Member Lists
Dim myMemberListHeaders = New List(Of MemberListHeader)myMemberListHeaders.Add(new
MemberListHeader("Sample Member List"))myMemberListHeaders.Add(new
MemberListHeader("PartnerList"))
Return myMemberListHeaders
Case Is = FinanceFunctionType.MemberList
If args.MemberListArgs.MemberListName = "Sample Member List" Then
Dim myMemberListHeader = new MemberListHeader(args.MemberListArgs.MemberListName)
Dim myMembers = new List(Of Member)()
Dim myMemberList = New MemberList(myMemberListHeader,
myMembers)myMembers.AddRange(api.Members.GetChildren(args.MemberListArgs.DimPk,
args.MemberListArgs.TopMember.MemberPk.MemberId,args.MemberListArgs.DimDisplayOptions))
Return myMemberList
Else If args.MemberListArgs.MemberListName = "PartnerList" Then
Dim myMemberListHeader = new MemberListHeader(args.MemberListArgs.MemberListName)
Dim myMembers = new List(Of Member)()
Dim myMemberList = New MemberList(myMemberListHeader, myMembers)
myMembers.AddRange(api.Members.GetBaseMembers(args.MemberListArgs.DimPk,
api.Members.GetMember(args.MemberListArgs.DimPk.dimtypeid,"Paris").Memberpk.Memberid,
args.MemberListArgs.DimDisplayOptions))
myMembers.AddRange(api.Members.GetBaseMembers(args.MemberListArgs.DimPk,
api.Members.GetMember(args.MemberListArgs.DimPk.dimtypeid,"Nice").Memberpk.Memberid,
args.MemberListArgs.DimDisplayOptions))
Return myMemberList
End If
POV Object
In Business Rules, only Data Unit Dimensions are valid in the POV object, not Account or User Defined Members, so the code below only works in Business Rules:
Dim AcctID as Integer=api.POV.Account.MemberPk.MemberID Dim AcctID as Integer=api.POV.GetDataCellPk.AccountID
Retrieve Time Varying Property of Relationship’s % Consolidated Entity Attribute
'Get Entity ID, Parent Entity ID, Scenario ID, Scenario Type ID and Time ID
dim EntityID as integer= api.POV.Entity.Memberpk.Memberid
dim ParentID as integer= api.POV.Parent.Memberpk.Memberid
dim ScenarioID as integer=api.POV.Scenario.Memberpk.Memberid
dim ScenarioTypeID as integer = api.Scenario.GetScenarioType(ScenarioID).id
dim TimeID as integer = api.Time.GetIdFromName("2013M1")
'Get time varying node % consolidation property value for current Scenario POV and 2013M1
dim myDecimal as decimal= api.Entity.PercentConsolidation(EntityId, ParentId, ScenarioTypeId, TimeId)
'Log the time varying property
api.logerror(xfErrorLevel.Information,myDecimal & " " & api.POV.Entity.name & "." & api.POV.Parent.name)
TIP: API.POV.Parent object is only relevant for relationship level Consolidation Members
SetDataCell
Use this to set a value equal to another value. Any dimensions listed on the right side must have a member specified. You don't need to have all dimension on the right side but if they are not specified, the current POV is used.
api.Data.SetDataCell(Memberscript, amount, isNoData)
api.Data.SetDataCell("A#[Restricted Cash]:O#Forms:F#None:IC#None:U1#None:U2#None:U3#None:U4#None:
U5#None:U6#None:U7#None:
U8#None", 50, False)
Translate
The api.data.translate function is the same as api.data.calculate, but aggregates AdjInput data into the AdjConsolidated Member.
'GetParentCurrency only returns a value when running a translate.
If api.Parameters.FunctionType = FinanceFunctionType.Translate Then if api.POV.Cons.Name =
api.POV.GetParentCurrency.Name Then
api.Data.Translate("A#[Restricted Cash]=A#[Restricted Cash]:C#[USD]*10")end if
End If
Unbalanced Math
The Unbalanced math functions are required when performing math with two Data Buffers where the second Data Buffer needs to specify additional dimensionality. The term Unbalanced is used because the script for the second Data Buffer can represent a different set of Dimensions from the other Data Buffer in the api.Data.Calculate text. These functions prevent data explosion.
In the examples below, the first two parameters represent the first and second Data Buffers on which to perform the function. The third parameter represents the Members to use from the second Data Buffer when performing math with every intersection in the first Data Buffer. The math favors the intersections in the first Data Buffer without creating additional intersections.
It is key that the dimensionality of the Target (left side of the equation) matches the dimensionality of the first data buffer on the right side of the equation (argument 1).
AddUnbalanced
api.Data.Calculate("A#TargetAccount = AddUnbalanced(A#OperatingSales, A#DriverAccount:U2#Global,
U2#Global)")
SubtractUnbalanced
api.Data.Calculate("A#TargetAccount = SubtractUnbalanced(A#OperatingSales, A#DriverAccount:U2#Global,
U2#Global)")
DivideUnbalanced
api.Data.Calculate("A#TargetAccount =DivideUnbalanced (A#OperatingSales, A#DriverAccount:U2#Global,
U2#Global)")
MultiplyUnbalanced
api.Data.Calculate("A#TargetAccount =MultiplyUnbalanced (A#OperatingSales, A#DriverAccount:U2#Global,
U2#Global)")
Consider this example. A#OperatingSales has 100 stored records in January for a single Entity.
Because A#OperatingSales has a total of 100 stored values, A#TargetAccount will end up with 100 stored numbers and the amounts would be the values from A#OperatingSales plus/minus/multiplied/divided by whatever was found at A#DriverAccount:U2#Global for each of those 100 intersections.
This means that if there was no data in A#OperatingSales:U2#Widgets, then even though the UD2 Dimension is unspecified in the target and in the first Data Buffer expression, no record would be created, hence avoiding data explosion. The most common use case would be applying a driver for some of the Dimensions.
ConvertUnbalanced
This function is related to the Unbalanced Math functions (see Unbalanced Math later in this section) and used to convert a data buffer so that it is balanced with an api.Data.Calculate script where unbalanced math does not apply. This is necessary when using an api.Data.GetDataBufferUsingFormula to calculate a data buffer where the script was not balanced to match a formula in another script where a data buffer variable needs to be used.
In the example below, a myDataBuffer was created to have data for all stored accounts, but the subsequent api.Data.Calculate scripts expects each operand to use a specific account. The ConvertUnbalanced function filters the data buffer to only include the specified account name and it also converts the data buffer to make it balanced and consistent with the destination. The same data buffer can be re-used multiple times.
Example
Dim myDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("A#All")
api.Data.FormulaVariables.SetDataBufferVariable("myDataBuffer ", myDataBuffer, True)
api.Data.Calculate("A#6050 = ConvertUnbalanced($myDataBuffer, A#6000) +
ConvertUnbalanced($myDataBuffer, A#3000)")
