Samples of Member Filter Functions

The Samples tab in the Member Filter Builder contains examples of Member Filter Functions. Within the Member Filter Builder, hover over the samples with your cursor for a description and an example. Samples are explained below, or the relevant section is provided where you can find more information.

Samples of Member Expansions

See Member Expansions and Reverse Order Member Expansions and the Samples tab in the Member Filter Builder for examples.

Samples of Where Clause Expressions

See Member Expansions and Member Expansion Where Clause and the Samples tab in the Member Filter Builder for examples.

Samples of Time Expressions

See Time Functions and the Samples tab in the Member Filter Builder for examples.

Samples of GetaDataCell Expressions

Use the GetDataCell function to retrieve specific cells, perform math, or Business Rule operations. The results are returned in a Cube View column or row. The column or row header comes from what is defined within :Name(), which can be changed.

Variance Using GetDataCell

This calculates the difference as a ratio between two members using: ((A-B)/Abs(B))

GetDataCell(Variance(S#Actual,S#BudgetV1)):Name(Variance)

This example returns the value of ((S#Actual - S#BudgetV1) / Abs(S#BudgetV1)) in a Cube View column or row named Variance.

Variance Percent Using GetDataCell

This calculates the difference as a percentage between two members using: ((A-B)/Abs(B)) * 100

GetDataCell(VariancePercent(S#Actual,S#BudgetV1)):Name(Var %)

This example returns the value of ((S#Actual - S#BudgetV1) / Abs(S#BudgetV1)) * 100 in a Cube View column or row named Var %.

Better or Worse Using GetDataCell

This calculates the difference between two members while considering the Account Type. For example:

Revenue accounts are calculated using (A-B)
Expense accounts are calculated using ((-1 * A) - (-1 * B))

GetDataCell(BWDiff(S#Actual, S#BudgetV1)):Name(BetterWorse Difference)

This example returns the value of (S#Actual - S#BudgetV1) for Revenue accounts and ((-1 *S#Actual) - (-1 * S#BudgetV1)) for Expense accounts in a Cube View column or row named BetterWorse Difference.

Better or Worse Percent Using GetDataCell

This calculates a Variance Percent while considering the Account Type. For example:

Revenue Accounts are calculated using ((A-B) / Abs(B)) * 100
Expense Accounts are calculated using (((-1 * A) – (-1 * B))/Abs(B)) * 100

GetDataCell(BWPercent(S#Actual, S#BudgetV1)):Name("BetterWorse %")

Ratio Using GetDataCell

This calculates the ratio between two members using the Divide function. Use the Divide function instead of the division operator (/) to avoid dividing by zero or NoData.

GetDataCell(Divide(A#Cash, A#AcctsRec)):Name(Ratio)

This example returns A#Cash divided by A#AcctsRec, if there is data and the denominator is not zero, in a Cube View column or row named Ratio.

Difference Using GetDataCell

This calculates the difference of two members using the Subtraction operator.

GetDataCell(S#Scenario1-S#Scenario2):Name(Difference)

Sum Using GetDataCell

This calculates the sum of two members using the Addition operator.

GetDataCell(S#Scenario1+S#Scenario2):Name(Total)

Custom Functions to Call Business Rules using GetDataCell

There are Custom Functions found by navigating to Samples > GetDataCell Expressions which call a Finance Business Rule using the BR# token within a GetDataCell.

Custom Function:

GetDataCell(BR#[BRName = MyRuleName, FunctionName = MyFunctionName]):Name(Custom Function)

Custom Function (Condensed Syntax):

GetDataCell(BR#[MyBusinessRuleName, MyFunctionName]):Name(Custom Function)

Here is an example of a business rule that dynamically calculates Gross Profit percentage and a Cube View that calls it. The business rule and Cube View Member Filter syntax are below:

Business Rule Function

The Function name in the example is GrossProfitPercentage, which needs to be referenced in the Member Filter.

Copy

    Case Is = FinanceFunctionType.DataCell
        If args.DataCellArgs.FunctionName.XFEqualsIgnoreCase("GrossProfitPercentage")
            Return api.Data.GetDataCell("Divide(A#GrossProfit,A#Sales)")
        End If

Cube View Member Filter Using Custom Function(Condensed Syntax)

In the GetDataCell string, define the business rule name, function name, and Cube View column or row header name. This example returns the values from the GrossProfitPercentage function in the CorporateCalcs business rule in a Cube View column or row named Gross Profit %.

GetDataCell(BR#[CorporateCalcs, GrossProfitPercentage]):Name(Gross Profit %)

In the example below, the business rule reads the current time period from the rule and gets the value for the prior year based on the Member script. The business rule and Cube View Member Filter syntax are below.

Business Rule Function and Name-Value Pair

The Function name in the below example is PYMonthForCol, which needs to be referenced in the Member Filter. The Name-Value Pair is Field1, which needs to be referenced and defined in the Member Filter. Based on the name-value pair, the rule can run different actions.

Copy

Case Is = FinanceFunctionType.DataCell
If args.DataCellArgs.FunctionName.XFEqualsIgnoreCase("PYMonthForCol") Then
            Dim ms1 As String = args.DataCellArgs.NameValuePairs("Field1")
            Dim priorYearTimeName As String = _
api.Time.GetNameFromId(api.Time.GetPriorYearPeriodId())
            Dim memberScript As New System.Text.StringBuilder
            memberScript.Append(ms1)
            memberScript.Append(":T#")
            memberScript.Append(priorYearTimeName)
            'Get the Data Cell For the Prior Year
            Return api.Data.GetDataCell(memberScript.ToString)
End If

Cube View Member Filter Using Custom Function With Parameters

In the GetDataCell string, define the business rule name, the function name, and the name-value pair from the business rule. The Cube View returns the sales, A#60999, from the prior year.

GetDataCell("BR#[BRName = XFR_CVDataCellHelperNew, FunctionName = PYMonthForCol, Field1 =A#60999]"):Name("Sales Last Year")

Samples of Column/Row Expressions

These samples contain GetDataCell calculations that reference the Cube View column and row names to perform column and row math.

See Column and Row Expressions and the Samples tab in the Member Filter Builder for examples.

Samples of Custom Member List Expressions

There are Custom Member List expressions that call a member list from a Finance business rule. You can build a custom list in a business rule and pass that business rule in a Cube View. This is used to return a list of members or member information when running the rule in a Cube View. MemberInfo allows users to specify additional information such as a Parent Entity ID and an Indent Level. An unlimited number of name-value pairs can be passed to the Member List Business Rule. Some use cases are ranking members by the top ten values in a Cube View or listing member alphabetically.

Custom Member List:

E#Root.CustomMemberList(BRName=MyBusinessRuleName, MemberListName=MyMemberListName)

Custom Member List (Condensed Syntax):

E#Root.[MyBusinessRuleName, MyMemberListName]

Custom Member List With Parameters:

E#Root.CustomMemberList(BRName=MyBusinessRuleName, MemberListName=MyMemberListName, Name1=Value1, AnotherName=[Another Value])

This example returns a member list in a Cube View. The business rule and Cube View Member Filter syntax are below.

Business Rule with Name Value Pair

In the Business rule, define the List Name and the name-value pairs.

Copy

Select Case api.FunctionType
Case Is = FinanceFunctionType.MemberList
        If args.MemberListArgs.MemberListName.XFEqualsIgnoreCase("EntityParentList") Then
            Dim entityList As String = args.MemberListArgs.NameValuePairs("EntityList")
            Dim objMemberListHeader As New MemberListHeader(args.MemberListArgs.MemberListName)
            Dim objMemberInfos As List(Of MemberInfo) = _
api.Members.GetMembersUsingFilter(args.MemberListArgs.DimPk, entitylist, Nothing)
            Dim objMemberList As New MemberList(objMemberListHeader, objMemberInfos)
            Return objMemberList
        End If

Cube View Member Filter Using Custom Member List

In the Cube View Member Filter, define the business rule name, Member List name, the name-value pairs from the business rule, and the members to which it applies. This example returns the base members of E#Houston, which are South Houston and Houston Heights. The name-value pairs can be changed to create additional custom member lists.

E#Root.CustomMemberList(BRName = XFR_MemberListBasicNew, MemberListName = EntityParentList, EntityList = E#Houston.Base)

This example returns a member list in alphabetical order in a Cube View. The business rule and Cube View Member Filter syntax are below.

Business Rule to Sort Member List in Alphabetical Order

This business rule sorts the balance sheet member list alphabetically when it is called from a Cube View. It returns A#BalanceSheet.base members for the workflow profile cube and Actual Scenario Type.

Copy


'This will put a member list of a dimension in Alphabetical order. 
'Use the following on the cube view A#Member.[Name of Business Rule, Name of List in Business Rule]
'A#Root.[XFR_MemberListAlphabetical, Acct_Sort]
Dim Memberlistname As String = "BSAcct_Sort"
Dim wfProfileName As String = api.Workflow.GetWorkflowUnitInfo.ProfileName
Dim wfProfileCubeName As String = BRApi.Workflow.Metadata.GetProfile(si, _
wfProfileName).CubeName
Dim CurCube As String = api.Pov.Cube.Name
            
Select Case api.FunctionType      
    Case Is = FinanceFunctionType.MemberList
If args.MemberListArgs.MemberListName = "BSAcct_Sort" Then
Dim objMemberListHeader = New MemberListHeader(args.MemberListArgs.MemberListName)
Dim MemberListstart As String = "A#BalanceSheet.base.Options(Cube = " & _
wfProfileCubeName & _
",ScenarioType= Actual,MergeMembersFromReferencedCubes=False)"
'brapi.ErrorLog.LogMessage (si,"Cube Name: " & wfProfileCubeName)
'Read the members
Dim objMemberInfos As List(Of MemberInfo) = _
api.Members.GetMembersUsingFilter(args.MemberListArgs.DimPk, _
MemberListstart, Nothing)

'Sort the members
Dim objMembers As List(Of Member) = Nothing
If Not objMemberInfos Is Nothing Then
objMembers = (From memberInfo In objMemberInfos Order By _
memberInfo.Member.Name Ascending Select memberInfo.Member _
Distinct).ToList()
End If

'Return
Return New MemberList(objMemberListHeader, objMembers)
End If
End Select
    Return Nothing

Cube View Member Filter Syntax

In the Cube View Member Filter, define the business rule name and the Member List name. The A#BalanceSheet.base members for the workflow profile cube and Actual Scenario Type will show in the Cube View alphabetically by the member names.

A#Root.[XFR_MemberListAlphabetical, BSAcct_Sort]

This example shows how custom display names can be applied and displayed in a Cube View header when creating custom Member Lists.

Business Rule for Custom Display Names in Member Lists

Copy

Select Case api.FunctionType
Case Is = FinanceFunctionType.MemberList
Dim listName As String = args.MemberListArgs.MemberListName
        'Get the Passed in parameters
        Dim entityList As String = args.MemberListArgs.NameValuePairs("EntityList")
        If listName.Equals("AlphaSortList", _
StringComparison.InvariantCultureIgnoreCase) Then
Dim objMemberListHeader = New _
MemberListHeader(args.MemberListArgs.MemberListName)
            'Read the members
            Dim objMemberInfos As List(Of MemberInfo) = _
api.Members.GetMembersUsingFilter(args.MemberListArgs.DimPk, _
entityList, Nothing)
            'Sort the members
            Dim objMembers As List(Of MemberInfo) = Nothing
            If Not objMemberInfos Is Nothing Then
                objMembers = (From memberInfo In objMemberInfos Order By _
memberInfo.Member.NameAscending Select memberInfo).ToList()
            End If
            'Apply a custom display name to the first item.
            If Not objMembers Is Nothing Then
                If objMembers.Count > 0 Then
                    objMembers(0).RowOrColNameForCalcScript = _
objMembers(0).NameAndDescription & "(Custom Name)"
                End If
            End If
            'Return list
            Return New MemberList(objMemberListHeader, objMembers)
        End If
End Select

Search Snippets for Member List in a Finance business rule for examples of business rules for Custom Member Lists, such as the List Alphabetical and List Ranked snippets. The Cube View Member Filter syntax is shown within a comment in the business rule sample.

Samples of XFBR, XFCell, and XFMember Property

XFBR

You can use XFBR to call a business rule to create a custom string substitution.

XFBR(businessRuleName, brFunctionName, Name1=[Value 1], Name2=Value2)

This example uses XFBR to call a business rule to return an alternative View member based on the user’s POV. The business rule and Cube View Member Filter syntax are below.

Business Rule for Custom Description Syntax

Copy

If (args.FunctionName.XFEqualsIgnoreCase("GetAltViewDescription")) Then
            Dim viewName As String = args.NameValuePairs.XFGetValue("ViewName")
            If viewName.XFEqualsIgnoreCase("Periodic") Then
                Return "MTD"
            Else
                Return viewName
            End If
        End If

Cube View Member Filter Syntax

The Cube View Member Filter contains V# and then defines the business rule’s name, the function name, and a name-value pair within XFBR(). If the POV contains V#Periodic, V#MTD is show in the Cube View.

V#XFBR(XFR_DescriptionHelpers, GetAltViewDescription, ViewName=[|PovView|])

This example uses XFBR to call a business rule to return a certain time period based on the scenario selected when the Cube View is run. The business rule and Cube View Member Filter syntax are below.

Business Rule for Custom Description Syntax

If the scenario is Actual, then the twelve months prior is returned. Otherwise, the Cube View time is returned.

Copy

If (args.FunctionName.XFEqualsIgnoreCase("timeHelper1")) Then
            Dim currentScenario As String = args.NameValuePairs.XFGetValue("currentScenario")
            If currentScenario.XFEqualsIgnoreCase("Actual") Then
                Return "MonthPrior12(|CVTime|)"
            Else
                Return "|CVTime|"
            End If
        End If

Cube View Member Filter Syntax

The Cube View Member Filter contains T# and then defines the business rule’s name, the function name, and a name-value pair within XFBR(). The Scenario Member in the Cube View POV is |!SelectScenario!|, which prompts the user to select a Scenario member when the Cube View is run. If the user selects S#Actual, the time period 12 months prior is returned.

T#XFBR(XFR_CubeViewHelper, timeHelper1, currentScenario = |!SelectScenario!|)

XFCell

See XFCell and Using Retrieve Functions in Extensible Documents and the Samples tab in the Member Filter Builder for examples.

XFMemberProperty

This function allows users to specify a Dimension property and display the Member Property selection as a row or column header on a Cube View. This can be used if the header name for a row or column must be different than the member name or description. Use the XFMemberProperty within the Name() or GetDataCell() portion of a Member Filter.

This example returns the base members of A#60999 and shows the Account Type in the row or column instead of the member name or description.

A#60999.Base:Name(XFMemberProperty(DimType=Account, Member=|MFAccount|,Property=AccountType))

The following name-value pair settings are needed in this function:

DimType: Define the dimension type such as Entity, Account, and so on.

Member: Define the dimension member name. In the above example, the member name is defined with a Member Filter Substitution Variable.

NOTE: To reference the dimension members specified in the Member Filter, use the dimension-specific Member Filter Substitution Variables. For example, if the Member Filter is A#NetSales.Children, use =|MFAccount| to dynamically point to each Child Member of Net Sales while running this function. See Member Filter Substitution Variables for more details.

Property: Define the exact dimension member property name, such as Property=AccountType.

Add additional name-value pair settings in this function if the property varies, such as:

VaryByCubeType: Use this if the property varies by Cube Type.

A#NetSales.Children:Name(XFMemberProperty(DimType=Account, Member=|MFAccount|, Property=Text1, VaryByCubeType=Standard)
VaryByScenario: Use this if the property varies by Scenario Type, such as Actual or Budget.

A#NetSales.Children:Name(XFMemberProperty(DimType=Account, Member=|MFAccount|, Property=Text1, VaryByScenarioType=Actual))
VaryByTime: Use this if the property varies by a specific Time Period such as 2024M5.

A#NetSales.Children:Name(XFMemberProperty(DimType=Account, Member=|MFAccount|, Property=Text1, VaryByScenarioType=Actual, VaryByTime=2024M5))
TimeDimProfile: Use this to specify the Time Dimension Profile. This can be set to CV, WF, any Time Dimension Profile name, or a Cube name using the CB# qualifier.

T#2024.Base:Name(XFMemberProperty(DimType=Time, Member=|MFTime|, Property=Description, TimeDimProfile=|CVTimeDimProfile|))

The example above uses the |MFTime| Substitution Variable to reference the members in the T#2015.Base Member Filter. It also uses the |CVTimDimProfile| Substitution Variable to point to the Time Dimension Profile for the Cube View. See Member Filter Substitution Variables for more details on these Substitution Variables.

Parameter and ParamDisplay

There are also buttons in the Member Filter Builder for parameters which open the Object Lookup dialog box. Use these to enter a custom Parameter reference that comes from either a Form or Dashboard in a Member Filter. Parameter Display is only used when working with a Delimited List Parameter where the Display Item is displayed instead of the Member name.