Forum Discussion

SamuelGorgonio's avatar
SamuelGorgonio
New Contributor II
2 years ago

Summarizing a member expansion so it appears as a single row in Quickview

I am trying to create a quickview that would require the data presented to consider some account configuration that only appears on the decendents set up.

 

ex i have a parent member P that has multiple children and would like use a where clause to exclude IsIC data. i tried to do A#P.Base(IsIC False) but this results in multiple lines where i would want to only have one line presented that is removing the data associated with the children that are IC. is there a function that would let me create a summary line while still checking the descendent configuration?

Excel Add In
Reports
  • Omkareshwar's avatar
    Omkareshwar
    Contributor II
    2 years ago

    Hi Samuel,

    You can use this XFBR Rule just update the highlighted part as per your requirement and it will return a string 

    "GetDataCell(E#E1 + E#E2 + E#E3)" so you'll get a sum in a single line 

    Syntax to call xfbr functions "XFBR(businessRuleName, brFunctionName)"

     

    If args.FunctionName.XFEqualsIgnoreCase("GetSumMemberGetDatacell") Then
     
    Dim entityList = BRApi.Finance.Metadata.GetMembersUsingFilter(si, "FinBU1", "E#TotBU1.Base.Where(Text1 Contains '39000')", False)
    Dim entityGetDataCell As String = ""
    For Each entity As MemberInfo In entityList
    entityGetDataCell = $"{entityGetDataCell} + E#{entity.Member.Name}" 
    Next
     
    ' Remove the leading " + " if present
    If entityGetDataCell.StartsWith(" + ") Then
    entityGetDataCell = entityGetDataCell.Substring(3)
    End If
     
    Return $"GetDataCell({entityGetDataCell})"
     
    End If
     
    Thanks, Omkareshwar
    Archetype Consulting
    • SamuelGorgonio's avatar
      SamuelGorgonio
      New Contributor II
      2 years ago

      Do you know if when utilizing the Business rule method its possible to drill down to detail as you would with a regular line item. I noticed that when i use gedatacell the drill down only shows the getdatacell formula and does not allow you to drill down on dimensions ?

  • SamuelGorgonio's avatar
    SamuelGorgonio
    New Contributor II
    2 years ago

    Do you know if when utilizing the Business rule method its possible to drill down to detail as you would with a regular line item. I noticed that when i use gedatacell the drill down only shows the getdatacell formula and does not allow you to drill down on dimensions ?

    • Omkareshwar's avatar
      Omkareshwar
      Contributor II
      2 years ago

      Sorry I don't have any information on that. For member formulas we can have a formula for calculation drill down but in a spreadsheet for an XFBR rule I don't know how this can be done.