Forum Discussion

db_pdx's avatar
db_pdx
Valued Contributor
4 years ago
Solved

How to pass standard formatting conditionals to an XFBR rule?

Hello: Looking for guidance on how to implement an enhanced conditional formatting check.  It's quite simple, apply some formatting based on the account type. Consider the example of a single row...
Cube View
  • db_pdx's avatar
    db_pdx
    4 years ago

    Thanks Eric, using the In syntax is a working solution, albeit a brute force one.  I would love to understand more how you arrived at the conclusion that |MF| substitutions won't work here.  This is the type of info I am hoping to see in the documentation.  Else we're just throwing ideas against the wall until something sticks.

    Final conditional string:

    If (RowE1MemberName In XFBR(XFBR_FormattingHelper, InAccountTypeIsLiability, Account=[AccountHierarchy])) Then
    TextColor = Red
    End If

    Final XFBR:

    If args.FunctionName.XFEqualsIgnoreCase("InAccountTypeIsLiability") Then
    Dim accountName As String = args.NameValuePairs.XFGetValue("Account")
    Dim accountId As Integer = BRapi.Finance.Members.GetMemberId(si,DimType.Account.Id,accountName)
    Dim accountDescendantsAreLiability As List(Of Member) = BRapi.Finance.Members.GetDescendants(si,BRapi.Finance.Dim.GetDimPk(si,"AllAccounts"),accountId).Where(Function(x) BRApi.Finance.Account.GetAccountType(si, x.MemberId).ToString = "Liability").ToList()
    Dim strLiabilityAccounts As String = String.Join(",",accountDescendantsAreLiability.Select(Function(x) x.Name))
    Return strLiabilityAccounts
    End If

    Couple of items if others stumble upon this thread:

    The conditional In statements expects a comma separated literal string. Not a List(Of String), and not a string delimited by anything else.

    This is still less than ideal as you are returning a large majority of the hierarchy for each and every member this is evaluated against and you must still update the XFBR string if you have multiple hierarchies in play.

    I'll file a bug/enhancement request for the issues and inconsistencies noted.

    Thanks,

    Dave

db_pdx's avatar
db_pdx
Valued Contributor
4 years ago

Here is the result of my debugging.  Conditional Formula:

If (XFBR(XFBR_FormattingHelper, Debugging, Account=Parameter) = True) Then
TextColor = Red
End If

XFBR:

If args.FunctionName.XFEqualsIgnoreCase("Debugging") Then
Dim accountName As String = args.NameValuePairs.XFGetValue("Account")
BRapi.ErrorLog.LogMessage(si,"I'm logging: " & accountName)
Return True
End If

Result of testing:

ParameterLogged ValueConditional worked (text was red)
A87999A87999Yes
[A87999]A87999Yes
RowE1MemberNameRowE1MemberNameYes
[RowE1MemberName]RowE1MemberNameYes
[|RowE1MemberName|](nothing)No
[|MFAccount|](nothing)No
   

 

It looks like the piped parameters don't get evaluated. Should I file this as a bug?

EricOsmanski's avatar
EricOsmanski
Valued Contributor
4 years ago

After thinking about this more, the "MF" substitution variables will not work. I think what may would be to use If (RowE1MemberName In 'ThisList') Then

Where ThisList would be replaced with a BRString which would return a list of all Accounts with a Liability Account Type.

  • db_pdx's avatar
    db_pdx
    Valued Contributor
    4 years ago

    Thanks Eric, using the In syntax is a working solution, albeit a brute force one.  I would love to understand more how you arrived at the conclusion that |MF| substitutions won't work here.  This is the type of info I am hoping to see in the documentation.  Else we're just throwing ideas against the wall until something sticks.

    Final conditional string:

    If (RowE1MemberName In XFBR(XFBR_FormattingHelper, InAccountTypeIsLiability, Account=[AccountHierarchy])) Then
    TextColor = Red
    End If

    Final XFBR:

    If args.FunctionName.XFEqualsIgnoreCase("InAccountTypeIsLiability") Then
    Dim accountName As String = args.NameValuePairs.XFGetValue("Account")
    Dim accountId As Integer = BRapi.Finance.Members.GetMemberId(si,DimType.Account.Id,accountName)
    Dim accountDescendantsAreLiability As List(Of Member) = BRapi.Finance.Members.GetDescendants(si,BRapi.Finance.Dim.GetDimPk(si,"AllAccounts"),accountId).Where(Function(x) BRApi.Finance.Account.GetAccountType(si, x.MemberId).ToString = "Liability").ToList()
    Dim strLiabilityAccounts As String = String.Join(",",accountDescendantsAreLiability.Select(Function(x) x.Name))
    Return strLiabilityAccounts
    End If

    Couple of items if others stumble upon this thread:

    The conditional In statements expects a comma separated literal string. Not a List(Of String), and not a string delimited by anything else.

    This is still less than ideal as you are returning a large majority of the hierarchy for each and every member this is evaluated against and you must still update the XFBR string if you have multiple hierarchies in play.

    I'll file a bug/enhancement request for the issues and inconsistencies noted.

    Thanks,

    Dave