How to include Member IDs in data export

Hi denisefockler 

Here is an example where we extend the FDX result from the FDX Data Unit BRApi.  In this example we derive EntityID from the member in the original output.  I've added comments also.

				' This is just an example.  Please amend for your own purposes
				
				' Declare StringBuilder (for logging)
				Dim sb As New Text.StringBuilder				

				' Declare variables for FDX (swap out for your variables)
				Dim cubeName As String = "Equipment Division"
				Dim entityMemFilter As String = "E#NAE.Base"
				Dim consName As String = ConsMember.Local.Name
				Dim scenarioMemFilter As String = "S#Actual"
				' Drop DimToken prefix e.g. S#; so we can get Member ID
				Dim scenarioMem As String = scenarioMemFilter.Replace(StageConstants.MasterDimensionTokens.Scenario, String.Empty)
				Dim scenarioTypeId As Integer = BRApi.Finance.Scenario.GetScenarioType(si, ScenarioDimHelper.GetIdFromName(si, scenarioMem)).Id
				Dim timeMemFilter As String = "T#2025M1"
				Dim viewName As String = ViewMember.YTD.Name
				Dim suppressNoData As Boolean = True
				Dim filter As String = String.Empty
				Dim parallelQueryCount As Integer = 8
				Dim logStatistics As Boolean = False
					
				' Execute FDX Data Unit BRApi
				Dim dt As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si, cubeName, entityMemFilter, consName, scenarioTypeId, scenarioMemFilter, timeMemFilter, viewName, suppressNoData, filter, parallelQueryCount, logStatistics)				

				' Enter dimension name (that we want to get ID's for)
				Dim dimName = "Equipment"
				' Now we can use the DimName to get the DimPk for that dimension e.g. Entity (we can do the same steps for any dimension)
				Dim dimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, dimName)
				
				' Use this BRApi GetMembersUsingFilter (normally returns a list of MemberInfo) but we can return result as a Dictionary using linq lambda extension (Dictionaries are better for lookups)
				' Since dictionaries must have unique keys, it is safer to group the result and grab the first instance of that member
				' Remove duplicates boolean set to True on BRApi GetMembersUsingFilter
				Dim memDict As Dictionary(Of String, Integer) = BRApi.Finance.Members.GetMembersUsingFilter(si, dimPk, entityMemFilter, True, Nothing, Nothing).
																GroupBy(Function(lambda) lambda.Member.Name).
															    ToDictionary(Function(lambda) lambda.Key, Function(lambda) lambda.First().Member.MemberId)
				
				' Create new Datatable and clone existing structure (this clones columns, schema, etc from original datatable)
				' If you want to reorder columns in target DataTable, you can declare them manually below in the order you want instead of cloning the original structure
				' Row order is important below and must follow the same order as your columns
				Dim dtNew As DataTable = dt.Clone()
				' Add new derived column
				dtNew.Columns.Add("EntityId", GetType(Integer))

				' Loop original DataTable
				For Each dr As DataRow In dt.Rows()
					'Define rows
					Dim row As DataRow = dtNew.NewRow
					' New row = old row
					row("Cube") = dr("Cube")
					row("Entity") = dr("Entity")
					row("Parent") = dr("Parent")
					row("Cons") = dr("Cons")
					row("Scenario") = dr("Scenario")
					row("Time") = dr("Time")
					row("View") = dr("View")
					row("Account") = dr("Account")
					row("Flow") = dr("Flow")
					row("Origin") = dr("Origin")
					row("IC") = dr("IC")
					row("UD1") = dr("UD1")
					row("UD2") = dr("UD2")
					row("UD3") = dr("UD3")
					row("UD4") = dr("UD4")
					row("UD5") = dr("UD5")
					row("UD6") = dr("UD6")
					row("UD7") = dr("UD7")
					row("UD8") = dr("UD8")
					row("Amount") = dr("Amount")
					' Here is where we derive MemberId using dictionary
					' We set a default value of type Integer (same as MemberId)
					Dim memID As Integer = 0
					' We use TryGetValue (in case key does not exist)
					If memDict.TryGetValue(dr("Entity"), memID) Then
						row("EntityId") = memID 	
					End If
					' Add DataRow to new DataTable
					dtNew.Rows.Add(row)
				Next dr	
				
				' Log first 10 rows of your target Datatable (you can comment out the below code post-development))
				Dim rowCount As Integer = 0
				Dim maxRows As Integer = 10
				
				' Loop DataTable
				For Each row As DataRow In dtNew.Rows()
					
				    If rowCount >= maxRows Then
				        Exit For
				    End If					
									
					For Each col As DataColumn In dtNew.Columns()
						sb.AppendLine(String.Format("Col ➡ {0}, Row ➡ {1}", col.ColumnName, row(col)))
					Next col
					
					rowCount += 1
				Next row	
				
				' Don't forget to comment this out in Production!!!
				BRApi.ErrorLog.LogMessage(si, "FDX Log Updated Result", sb.ToString())	

Hope this helps.  All DataTable manipulation is standard .Net (so you can google it 🙂)

BR

Sam