Forum Discussion

GregHertling's avatar
GregHertling
New Contributor III
3 years ago

Store a cell format parameter in a Text field and use it is a cube view

hi, I was trying to use a cell format parameter I had stored in an Account Text field in a cube view using an XFBR rule.  Can this work?  My attempt below.

thanks,

Greg

Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As Object
Try

If (args.FunctionName.XFEqualsIgnoreCase("CheckDataForVisibleColumn")) Then
Return Me.CheckDataForVisibleColumn(si, globals, api, args)

ElseIf (args.FunctionName.XFEqualsIgnoreCase("GetAccountFormatFromText")) Then
Return Me.GetAccountFormatFromText(si, globals, api, args)

End If

Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function

#Region "Standard Helper Functions"

Public Function GetAccountFormatFromText (ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardStringFunctionArgs) As String
Try
Dim AccountID As String = args.SubstVarSourceInfo.PovMembers.Account.MemberId
Dim FormatParam As String = BRApi.Finance.Account.Text(si, AccountID, 1,-1,-1)

Return FormatParam

Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function

Cubeview
  • JackLacava's avatar
    JackLacava
    Honored Contributor
    3 years ago

    It would probably be easier to have an XFBR that simply accepts one parameter (the account name) and returns the necessary property. Then you should be able to use it like this on the CV properties: XFBR(MyRule,MyFunction,AccName=|MFAccount|). This would avoid having to mess with args, which might not be set in the way you seem to expect.

    If it's about formatting, anyway, you can write a Cube View Extender rule; that has access to the cell, so you can retrieve the Account from there.

    • GregHertling's avatar
      GregHertling
      New Contributor III
      3 years ago

      Hi, I tried this approach, however, |MFAccount| does not seem to be recognized by the XFBR rule.  When I put the actual Account Name in it works fine but Account=|MFAccount| does not.  Any ideas?

      If args.FunctionName.XFEqualsIgnoreCase("GetAccountFormatFromText8") Then

      Dim ScenID As Integer = DimConstants.Unknown
      Dim TimeID As Integer = DimConstants.Unknown
      Dim accountName As String = args.NameValuePairs.XFGetValue("Account")
      Dim accountId As Integer = BRapi.Finance.Members.GetMemberId(si,DimType.Account.Id,accountName)
      Dim FormatParam As String = BRApi.Finance.Account.Text(si,accountID, 8,ScenID,TimeID)
      BRApi.ErrorLog.LogMessage(si, $"accountName: " & accountName)
      BRApi.ErrorLog.LogMessage(si, $"FormatParam: " & FormatParam)

      Return FormatParam

      End If

      thanks,

      Greg

      • JackLacava's avatar
        JackLacava
        Honored Contributor
        3 years ago

        I guess it depends on whether that column or rows actually uses a filter on Accounts. MF is for "member filter", and it's supposed to retrieve the member where a filter is specified. E.g. if you have a row defined with "A#BalanceSheet.Base", MFAccount will be populated by the actual Account on expansion, i.e. A#MyNiceAssed, A#MyBadLiability etc etc; but if that row expands on something else, e.g. E#Total.Base, MFAccount won't be populated. If that principle doesn't hold true, it should be reported; at which point, the only option becomes a CV Extender.

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    3 years ago

    Yeah CV Extenders only work when the CV is used to generate a report. If you're working with the pure CV, options are limited.

    On the MF substitution variables, I checked and it looks like I fell foul of a change made a few years ago... they are not available in all properties anymore, they only work in row/column definitions by way of the XFMemberProperty function - which sadly does not execute in Conditional Formatting either.

    So, with these constraints, unfortunately I don't think your strategy of storing formatting on account properties can work at all.

    An alternative approach could be to use actual Parameters, storing generic formatting properties, and then switching between them if the member is in certain categories. E.g., let's assume you have a AssetFormat and a LiabilityFormat parameters; both are Literal Values, storing different formats (e.g. "Bold = True" for assets, "Bold = False" for liabilities). Your Conditional Formatting will look like this:

     

    If (RowE1MemberName in XFBR(MyRule,GetAssets)) then
   |!AssetFormat!|
else
   |!LiabilityFormat!|
end if

     

    At that point, the XFBR doesn't need any parameter, it just has to return a String containing the comma-separated list of Account members - which you can generate any way you want, including with lookups on Text properties (although in that case, you'll have to be careful with Scenario Type and Time...).

    This is a relatively expensive strategy, since generating that list might take time, so you probably don't want to do it on each and every row; ideally you'd put that XFBR in another Parameter, so it's (typically) executed just once (when the CV opens) - ending up with "if (RowE1MemberName In |!MyGenList!|) then...".