12-03-2021
03:27 PM
- last edited
a week ago
by
JackLacava
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 CubeView with one account as the member filter: A#A87999. Formatting should be applied if the account is a Liability type.
I have a mostly working XFBR helper that can correctly apply formatting but only if I hardcode in the account reference. Obviously that won't work for all but the simplest CV as reports are often dynamic with Member Expansions.
The mainly working formatting syntax as currently written:
If (XFBR(XFBR_FormattingHelper, AccountTypeIsLiability, Account=[A8799])=True) Then
TextColor = Red
End If
How do I make the parameter dynamic? I've tried the following:
... Account=RowE1MemberName
... Account=|RowE1MemberName|
... Account=[RowE1MemberName]
... Account=[|RowE1MemberName|]
None seem to work. Is there a different way to reference the standard available conditionals?
Thanks,
Dave
Solved! Go to Solution.
12-07-2021 04:23 PM
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
12-07-2021 08:19 AM
Account=[|MFAccount|]
You can search for "Member Filter Substitution Variables" in the reference guide for more information.
12-07-2021 09:23 AM
Hi Eric: thanks for the suggestion, that always doesn't appear to be working either.
Formatting string:
If (XFBR(XFBR_FormattingHelper, AccountTypeIsLiability, Account=[|MFAccount|]) = True) Then
TextColor = Red
End If
XFBR:
If args.FunctionName.XFEqualsIgnoreCase("AccountTypeIsLiability") Then
Dim accountName As String = args.NameValuePairs.XFGetValue("Account")
Dim accountId As Integer = BRapi.Finance.Members.GetMemberId(si,DimType.Account.Id,accountName)
Dim strAccountType As String = BRapi.Finance.Account.GetAccountType(si,accountID).ToString
If strAccountType = "Liability" Then
Return True
End If
End If
In general, it is unclear how the conditional logic is evaluated. Is there documentation on this? I'd go as far as to say its a bit buggy. For example, the following do not work:
If (RowE1MemberName = [|MFAccount|]) Then
TextColor = Red
End If
If ('A87999' = [|MFAccount|]) Then
TextColor = Red
End If
If (RowE1MemberName = RowE1MemberName) Then
TextColor = Red
End If
12-07-2021 09:32 AM
Have you tried putting error logs in your BRString formula to see what is being passed in for the Account?
The documentation is under section "Cube View Conditional Formatting" in the reference guide.
12-07-2021 10:19 AM - edited 12-07-2021 10:24 AM
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:
Parameter | Logged Value | Conditional worked (text was red) |
A87999 | A87999 | Yes |
[A87999] | A87999 | Yes |
RowE1MemberName | RowE1MemberName | Yes |
[RowE1MemberName] | RowE1MemberName | Yes |
[|RowE1MemberName|] | (nothing) | No |
[|MFAccount|] | (nothing) | No |
It looks like the piped parameters don't get evaluated. Should I file this as a bug?
12-07-2021 01:27 PM
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.
12-07-2021 04:23 PM
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