Forum Discussion

RandyThompson's avatar
RandyThompson
New Contributor III
2 years ago

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 that a view is automatically created that links the dimension tables to the fact table. I would like to read this view as my datasource for my table view.

I added the line the following line:

    sql.AppendLine("FROM vBIB_OneStreamDevelopment_COPA_AnalysisImport_COPA_BiBlend_2023M6")

When I open the Table View in the OneStream spreadsheet I get the error message "Invalid object name 'vBIB_OneStreamDevelopment_COPA_AnalysisImport_COPA_BiBlend_2023M6'. I can read this view using a Data Adapter without issue, but when I embed in a Table View rule I get the above error.

Spreadsheet
  • JackLacava's avatar
    JackLacava
    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

     

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago

    I'm not aware of any restriction specific to Table Views.

    Can you post the code where you open the connection and execute the sql please? Chances are that the issue will be there.

      • JackLacava's avatar
        JackLacava
        Honored Contributor
        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