Forum Discussion

chuckbo's avatar
chuckbo
New Contributor III
4 years ago
Solved

How to export a list of members

Is there a way to export a list of the members in the Accounts dimension to Excel? I'm interested in downloading:

Name

Default Description

Parent

  • By far the simplest way is to use the Grid view of your A#'s, ctrl C/V into excel, taking any of the property details you need.

  • ChristianW's avatar
    ChristianW
    Valued Contributor

    You can create a csv file, it will be placed in your onestream file system user folder:

     

    'Export File
    Dim fileName As String = "CorpAccounts.csv"
    Dim filePath As String = $"Documents/Users/{StringHelper.RemoveSystemCharacters(si.UserName,False,False)}"
    Dim accountDimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, "CorpAccounts")
    'Export CSV to User Temp Folder
    Dim listOfParents As list(Of memberinfo) = BRApi.Finance.Members.GetMembersUsingFilter(si, accountDimPk,  "A#[GAAP Account Structure].descendants.Where(HasChildren = true)", True)
    Dim csv As New Text.StringBuilder
    For Each parentMember In listOfParents 
    	
    	For Each childMember In BRApi.Finance.Members.GetChildren(si, accountDimPk, parentMember.Member.MemberId)
    		
    		csv.AppendLine($"""{childMember.name}"",""{childMember.Description}"",""{parentMember.member.name}""")
    	Next
    Next
    
    Dim fileBytes As Byte() = Encoding.UTF8.GetBytes(csv.ToString)
    
    'Save csv to file
    Dim XFfileDataInfo As New XFFileInfo(FileSystemLocation.ApplicationDatabase, fileName, filePath)
    Dim XFfileData As New XFFile(XFfileDataInfo, String.Empty, fileBytes)
    brapi.FileSystem.InsertOrUpdateFile(si, XFfileData)			

    You can create a extender business rule and run in directly from the rule if you place it the unknown case or call it with a data management job if you place it in the ExecuteDataMgmtBusinessRuleStep case.

    You can also place it in a dashboard extender and call it with a button control (or any other).

    • Davy's avatar
      Davy
      Contributor

      How can I view all orphaned members in OneStream. Also, does your script above export them to csv?
      How can I remove them permanently ?

      From Design guide:
      Remove relationships:

      Use this to remove the copied Member(s) from their current relationship without moving them to a
      new one. If the copied Member is no longer a part of the Dimension structure, it will be placed
      under Orphans.

    • denisefockler's avatar
      denisefockler
      New Contributor III

      Christian, I know this is an old post you made but it worked great for me to extract the parent/child relationships into a csv file format.  Could you help me the syntax for how I could add the AggWeight for each relationship?  It seems it is available in the Relationship table by DimID, ParentID, ChildID.

      Thank you

      Denise

  • BarryC's avatar
    BarryC
    New Contributor II

    By far the simplest way is to use the Grid view of your A#'s, ctrl C/V into excel, taking any of the property details you need.