RandyThompson
2 years agoNew Contributor III
Reading the BiBlend view in a Spreadsheet (TableView) business rule
In a Spreadsheet (TableView) business rule, I am trying to reference the view created during the load of a BiBlend Star Schema. In our case when I create the BiBlend Star Schema tables, I also see th...
- 2 years ago
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