3 Replies Latest reply on Jan 31, 2013 10:58 PM by MR

    Dynamic Member List Based on Level


      Is there a way to create a dynamic member list in HFM based on the account's level in the account hierarchy (I'm working on HFM v. I am trying to change the order in which the accounts appear on our FR studio reports, where the total appear at the bottom of each detailed section (please see example below).

      Other Cash
      Outstanding Checks
      (tab) Cash
      Other Cash Eq.
      (tab) Cash Equivalents
      (double tab) Total Cash and Cash Equivalents

      Since the HFM account hierarchy is in the opposite order of the above presentation, merely adding members based on a UD flag results in a list in the opposite order of how I want to see the report. The reason I'm not using auto calculation in FR Studio is because I only have one data row that pulls in a defined member list which is based on a UD field (all the above members would be tagged with the UD). I couldn't achieve the tiered sub-calculations with auto-calc.

      Thus, I wanted to achieve the flipping of the hierarchy in the creation of my member lists. I thought that if i can somehow track the level number of each member, I could use that to flip the hierarchy and possibly use it to conditionally format line items as well.

      I would appreciate any suggestions on how to get this to work or any other alternate solutions that I can try.

      Thank you!
        • 1. Re: Dynamic Member List Based on Level
          Not sure if this solves your problem or not, but if you select the entire grid and go to the "position" tab on the grid properties you can select where you want the members to appear on expansion, either before or after parent.
          • 2. Re: Dynamic Member List Based on Level
            Hi JDN,

            Thank you for your response. Unfortunately, this feature doesn't take effect unless I turn on the "allow expansion" check box for a line item. Since i had built in parents as a part of my member list, I didn't originally check that box. After your suggestion, I checked the "allow expansion" box and changed the position settings to see if it would reorder the elements, and this didn't work.

            Thank you!
            • 3. Re: Dynamic Member List Based on Level
              Here is one possible solution that I came up with for my question. The following code creates a member list that will add the accounts in the correct order with parents showing up at the bottom of each hierarchy. The code below is for the balance sheet set of accounts, but can be used on other hierarchies as well.

              Sub AccountUD2_BalSht()

              acc = HS.Account.List("BALANCE_SHEET","[Descendants]")

              Set Array1 = CreateObject ("System.Collections.ArrayList")
              Set Array2 = CreateObject ("System.Collections.ArrayList")
              ' Assigning list to an array

              For Each a1 in acc
              UD2Var = HS.Account.UD2(a1)
              If UD2Var = "TB_Acct"
              Array1.add a1
              End if
              y = Array1.Count-1

              ' Input all members that are reported trial balance lines into the MemberList in correct order
              TargetAcct = Array1(i)
              For j = i to y
              If Array1(i) = Array1(j) Then     

              If HS.Account.IsDescendant(TargetAcct, Array1(j)) Then
              TargetAcct = Array1(j)
              End if
              End if

              Array2.add TargetAcct
              Array1.Remove(TargetAcct)          ' Remove the account that was added to the new list from the original list
              y = y-1
              Loop While (y>0)
              For each a1 in Array2
              HS.AddMembertoList a1
              HS.AddMembertoList "BALANCE_SHEET"

              End Sub

              Once this member list was in place, I used FR studio to conditionally format the rows I needed indented. I would welcome any suggestions on how to code this better.

              Edited by: MR on Jan 31, 2013 2:57 PM