Pass Results from Business Rule into UD8 Formula

[benmac360]

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

[JackLacava]

You can't. You have this strategy backwards: build your calculations as UD8s, then you can reference them from Cube View filters.

[benmac360]

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?

[JackLacava]

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.

[benmac360]

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.

[JackLacava]

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!

[Krishna]

@benmac360  - If this is going to for reporting. It is better to use UD8 vs XFBR.