Commonly Used Member Filter Functions

GetDataCell

Use the GetDataCell function to retrieve specific cell(s), perform math, or Business Rule operations.

Variance Example using GetDataCell

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

BetterWorse Example Using GetDataCell

This provides a Variance taking Account Type into consideration
GetDataCell(BWDiff(S#Actual, S#BudgetV1)):Name("BetterWorse Difference")

VariancePercent Example Using GetDataCell

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

BetterWorsePercent Example Using GetDataCell

This provides a Variance Percent taking Account Type into consideration
GetDataCell(BWPercent(S#Actual, S#BudgetV1)):Name("BetterWorse %")

Business Rule Example using GetDataCell

GetDataCell(BR#[BusinessRuleName, FunctionName]):Name(Custom Function)

For more GetDataCell examples, refer to the Samples Tab in the Member Filter Builder.

Parameter/Parameter Display

Use one of these buttons in order to enter a custom Parameter reference that comes from either a Form or Dashboard. The Parameter Display Substitution Variable is only used when working with a Delimited List Parameter where the Display Item will be displayed instead of the Member name.

Business Rules

Business Rules can be passed in a Cube View Member Filter in order to do complex calculations on the Members referenced in the Cube View. Setup the Finance Business Rule to indicate the name of the function and any name-value pairs to reference in the Member Filter. A different action can be performed based on the definition of the name-value pair. In the example below, the Business Rule is going to read the current time period from the rule and get the value for the prior year based on the Member script.

Function in Business Rule

The Function name in this string is PYMonthForCol. This needs to be referenced in the Member Filter.
If functionName.Equals("PYMonthForCol", StringComparison.InvariantCultureIgnoreCase) Then    

Name-Value Pair

The Name-Value Pair in this string is Field1. This needs to be referenced in the Member Filter and defined. Based on the Name-Value Pair, the rule can run different actions.
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                        
End Select

Cube View Member Filter Syntax
In the GetDataCell string define the Business Rule’s name, the function’s name, and define the name-value pair from the Business Rule.
BR#[BusinessRuleName, FunctionName=yourFunctionName, Name1=Value1, AnotherName=[AnotherValue]]

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

NOTE: See the Samples tab in the Member Filter Builder for more examples on this function.

Custom Member List

Build a custom list in a Business Rule and pass that Business Rule in a Cube View. This can return a list of Member’s 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. The example below will put the member list in alphabetical order.

In the Business Rule, define the List Name and the name-value pairs:

Select Case api.FunctionType      
        Case Is = FinanceFunctionType.MemberList
             Dim listName As String = args.MemberListArgs.MemberListName
             Dim entityList As String =  args.MemberListArgs.NameValuePairs("EntityList")
             If listName.Equals("EntityParentList", StringComparison.InvariantCultureIgnoreCase)
             Then    
               Dim objMemberListHeader = New MemberListHeader(args.MemberListArgs.MemberListName)

Cube View Member Filter Syntax

In the Cube View Member Filter, define the Business Rule’s name, the Member List Name, define the name-value pairs from the Business Rule, and the Members to which it applies.
E#Root.CustomMemberList(BRName = XFR_MemberListBasicNew, MemberListName = EntityParentList, EntityList = E#Houston.Base)

NOTE: See the Samples tab in the Member Filter Builder for more examples on this function.

Custom Display Names in Member Lists

When creating custom Member Lists, custom display names can be applied and displayed in a Cube View header.

Business Rule Example:

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.Name 
            Ascending 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

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. Use this function with the Name() and GetDataCell() portion of a Member Filter.

The following name-value pair settings can be used for this function:

  • DimType
    Dimension name such as Entity, Account, etc.

  • Member
    Dimension Member name

    NOTE: In order 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. See below for an example of this syntax.

  • Property
    The exact Dimension property name

  • VaryByCubeType
    Use this if the property varies by a specific Cube

  • VaryByScenario
    Use this if the property varies by Scenario such as =Actual, =Budge, etc.

  • VaryByTime
    Use this if the property varies by a specific Time Period such as =2016M1, =2016M5, etc.

  • TimeDimProfile
    This can be set to CV, WF, any Time Dimension Profile name, or a Cube name using the CB# qualifier

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

    The example above uses the |MFTime| Substitution Variable in order to reference the Members in the T#2015.Base Member Filter.  See Member Filter Substitution Variables for more details on these Substitution Variables. It also uses a Substitution Variable for TimDimProfile in order to point to whatever Time Dimension Profile is being used on the Cube View.

    NOTE: For more examples on using this function, refer to the Samples tab in the Member Filter Builder.