04-17-2024 06:23 PM
Hi - I have a dashboard XFBR business rule that calculates totals for members with a specific text value in a cube view. The rule is working as designed, and I have a couple GetDataCell functions in separate columns that reference these totals. However, I also have a UD8 member for variance analysis purposes that need the totals to determine whether or not a value exceeded a threshold - how do I pass what my business rule calculates into my UD8 formula?
Here's the business rule for reference:
If args.FunctionName.XFEqualsIgnoreCase("CVMath") Then
'Math function used in CV
Dim MathFunction As String = "GetDataCell("
'Suffix for GetDataCell function
Dim Suffix As String = "):Name(Total)"
'Get ColNames to use in Math Function
Dim ColNametoSum As String = args.NameValuePairs.XFGetValue("ColName")
'Prefix
Dim ColMath As String = "CVC(" & ColNametoSum & ","
'Get cube view member filter
Dim cvMemberFilter As String = args.NameValuePairs.XFGetValue("MemberFilter")
BRApi.ErrorLog.LogMessage(si, cvMemberFilter)
'This is an entity driven member filter
If cvMemberFilter.StartsWith("E#", stringcomparison.CurrentCultureIgnoreCase) Or cvMemberFilter.StartsWith("E#[", stringcomparison.CurrentCultureIgnoreCase) Then
'Get the entity dimension name used in the CV
Dim EntityDimName As String = args.NameValuePairs.XFGetValue("EntityDim")
'Get count of members returned
Dim memberCount As Integer =BRApi.Finance.Metadata.GetMembersUsingFilter(si, EntityDimName, cvMemberFilter, False, Nothing, Nothing).Count
Dim finalList As New List(Of String)
For i As Integer = 1 To memberCount
Dim cvc As String = ColMath & i.ToString & ")"
finalList.Add(cvc)
Next
Return MathFunction & String.Join("+", finalList.ToArray()) & Suffix
End If
End If
Here's the UD8 formula:
Dim myWorkflowUnitPk As WorkflowUnitPk = BRApi.Workflow.General.GetWorkflowUnitPk(si)
Dim wfTime As String = BRApi.Finance.Time.GetNameFromId(si, myWorkflowUnitPk.TimeKey)
'Take the variance between CY Actuals and PY Actuals at CY Rates for a given time period
Dim OnePctAmount As Decimal = api.data.GetDataCell("(S#Actual:T#[" & wfTime & "]:V#QTD:u8#none)*.01").CellAmount
Dim OnePctAmount_abs As Decimal = Math.Abs(OnePctAmount)
''Return VarAmount
If OnePctAmount_abs > 30000000 Then
Return "Y"
Else
Return ""
End If
04-18-2024 03:29 AM
You can't. You have this strategy backwards: build your calculations as UD8s, then you can reference them from Cube View filters.
a month ago
OK, so if I change the XFBR to a UD8 calculation, would the member filter for my column where I'm summing the values of the entities with the text criteria be the UD8 plus the specific entity filter? For example, it would be U8#MyUD8Calc:E#TextEntities?
a month ago
I believe that would depend on how the rule is written.
I would suggest to keep it simple: write a simple UD8 to calculate things for one entity. You can then see how it looks like on the CV, and in the worst case perform sums on the CV itself with GetDataCell.
a month ago
Thanks Jack - I appreciate the help so far. Now that I have the calculations for the entity, how would I pass or reference the calculation to the other UD8 to produce the Y or N? The variance UD8 is supposed to check if the absolute value of one column exceeds the absolute value of another - if it does, its supposed to return a Y.
a month ago
With your original UD8 😊 since you can now easily pull up with api.Data.GetDataCell the values of the intersections generated by the new UD8s. They are just cube data now!
04-18-2024 11:30 AM
@benmac360 - If this is going to for reporting. It is better to use UD8 vs XFBR.