Here's some code that works for me on 7.2.2. HOWEVER, as I was testing it, it looked like OneStream had some issue with a particular column (an Attribute), so maybe try restricting to a few columns to test the connection first.
Namespace OneStream.BusinessRule.Spreadsheet.GL_TestForum
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, _
ByVal api As Object, ByVal args As SpreadsheetArgs) As Object
Try
Select Case args.FunctionType
Case Is = SpreadsheetFunctionType.Unknown
Case Is = SpreadsheetFunctionType.GetCustomSubstVarsInUse
Case Is = SpreadsheetFunctionType.GetTableView
Dim dtable As DataTable
Using dbconnapp As DbConnInfo = BRApi.Database.CreateExternalDbConnInfo(si, "AnalyticBlendExternal")
dtable = BRApi.Database.ExecuteSql(dbconnapp, _
"SELECT TOP 10 [Rt],[SourceID],[Label],[TextValue],[sEntity] FROM vBIB_Gold_AnalyticBlend_Trainer_BlendImport_Blend_2020M1", _
False)
dTable.TableName = "BiBLend"
End Using
Return Me.getTv(si, globals, args, dTable)
Case Is = SpreadsheetFunctionType.SaveTableView
End Select
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Public Function getTv(ByVal si As SessionInfo, ByVal globals As BRGlobals, _
ByVal args As SpreadsheetArgs, ByVal dt As DataTable) As TableView
Dim tv As New TableView()
tv.CanModifyData = False
Dim rowHeader As New TableViewRow()
For Each dc As DataColumn In dt.Columns
Dim col As New TableViewColumn()
col.Name = dc.ColumnName
col.Value = dc.ColumnName
col.IsHeader = True
tv.Columns.add(col)
rowHeader.Items.Add(col.Name, col)
Next
tv.Rows.Add(rowHeader)
For Each row As DataRow In dt.Rows
Dim tvRow As New TableViewRow()
For Each column As TableViewColumn In tv.Columns
tvRow.Items.Add(column.Name, _
tv.CreateColumn(column.Name, row.Item(column.Name), False))
Next
tv.Rows.Add(tvRow)
Next
Return tv
End Function
End Class
End Namespace
Side note: please don't do this, it's a recipe for problems:
For Each DataColumn as DataColumn in dt.Columns
You're relying on Vb's rules for resolving names, which can be surprising at times. Just give a new name to that variable:
For Each datacol as DataColumn in dt.Columns
