We use a data adapter to query the Relationship table so that we can see exactly what the relationships look like. Then we reference the that data adapter to get the SQL statement into the EBR.
This is the query that pulls the relationships ("ADU_Dim_Metadata":
WITH Rec
AS (SELECT Member.MemberID, Name, Description, 0 as ParentID, 1 as Level, DimTypeID, CAST(0 as DECIMAL) as Agg
FROM Member
WHERE Member.MemberID = (select MemberID from Member where Name = '|!ADU_Param_Select_Top_Member!|' )
UNION ALL
SELECT m.MemberID, m.Name, m.Description, this.ParentID, parent.Level + 1, m.DimTypeID, CAST (UDAggWeight AS DECIMAL) as Agg
FROM Rec parent
INNER JOIN Relationship this ON this.ParentID = parent.MemberID
INNER JOIN Member m ON this.ChildID = m.MemberID
)
SELECT distinct
'' as RowID,
'|!ADU_Param_Select_Dim_Name!|' as Dim_Name,
'|!ADU_Param_Select_Dim_Type!|' as Dim_Type,
Rec.MemberID, Name as Member_Name, Description as Member_Description, ParentID,
(select Name from Member where MemberID=ParentID) as Parent_Name,
'0' as Level,
CASE
WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =0 THEN 'Group'
WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =1 THEN 'Revenu'
WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =2 THEN 'Expense'
WHEN Rec.DimTypeID = 5AND mp.DecimalValue =3 THEN 'Asset'
WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =4 THEN 'Liability'
WHEN Rec.DimTypeID = 5AND mp.DecimalValue =5 THEN 'Flow'
WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =6 THEN 'Balance'
WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =7 THEN 'BalanceReccuring'
WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =8 THEN 'NonFinancial'
WHEN Rec.DimTypeID = 5 AND mp.DecimalValue =9 THEN 'DynamicCalc'
WHEN Rec.DimTypeID <> 5 THEN 'N/A'
ELSE 'Other'
END as Acct_Type,
Agg,
CURRENT_TIMESTAMP as Update_Time_Stamp
FROM REC
LEFT JOIN (SELECT * FROM MemberProperty WHERE PropertyID = 100) mp on Rec.MemberID = mp.MemberID
--order by Level;
This is the EBR that gets the SQL query from the data adapter, replaces the parameters, and executes the query to push the data out to an external database:
#Region "Metadata Transfer"
Private Function MetaData_Transfer (ByVal si As SessionInfo, dimName As String, dimType As String, topMember As String, StageTableName As String) As Boolean
' system.Threading.Thread.Sleep(10000)
Dim AdapterName As String = "ADU_Dim_Metadata"
'Get the sql from the data adapter
Dim AdapterContents As String = BRApi.Database.LookupRowFieldValue(si, "App", "DashboardAdapter", "Name='" & AdapterName & "'", "SQLQuery", "")
'send the Adapter Contents to the log
brapi.ErrorLog.LogMessage(si,"AdapterContents before changes: " & AdapterContents.ToString)
'Change Top Member Filter
AdapterContents=StringHelper.ReplaceString(AdapterContents,"|!ADU_Param_Select_Top_Member!|", topMember, True)
'Change Dim Name Filter
AdapterContents=StringHelper.ReplaceString(AdapterContents,"|!ADU_Param_Select_Dim_Name!|", dimName, True)
'Change DIm Type Filter
AdapterContents=StringHelper.ReplaceString(AdapterContents,"|!ADU_Param_Select_Dim_Type!|", dimType, True)
'Send new Adapter Contents string to the log
brapi.ErrorLog.LogMessage(si,"AdapterContents: " & AdapterContents.ToString)
'Get metadata into a table
Dim objDbConnInfoApp As DbConnInfoApp = BRApi.Database.CreateApplicationDbConnInfo(si)
Dim dt As DataTable = BRApi.Database.ExecuteSql(objDbConnInfoApp, AdapterContents, False)
BRApi.Database.SaveCustomDataTable(si, externalConnector, StageTableName, dt, True)
Return True
End Function
#End Region