Forum Discussion

Sergey's avatar
Sergey
Contributor III
3 years ago

Build Dashboard from a Data Table query

Hi !

I have a SQL table available where I wanted to show a radar graph based on its data.

It turns out that BI Viewer is not providing radar or spider graphs, so I wanted to know how to have such graph based on a data table and not a cube view.

 

BAsed on what I've seen, the Advanced Charts allows to work with cube views or business rules only. Does someone have a business rule in order to extract data from my SQL data table and have it as a source of the graph ?

 

Regards,

3 Replies

  • OS_Pizza's avatar
    OS_Pizza
    Contributor III

    You can use the below code snippet to create one

    Dim sql As New Text.StringBuilder

    sql.AppendLine("Select * from Table1")

    Using dbConnFW As DbConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
    Using dt As DataTable = BRApi.Database.ExecuteSql(dbConnFW, sql.ToString, False)


    'Use System.Data.DataTable objects to play with column and rows.

    End Using
    End Using

  • JackLacava's avatar
    JackLacava
    Community Manager

    You might have to figure out exactly what columns you need so that the chart actually works, but this is a generic Dashboard DataSet rule that queries a table in the application database.

    Namespace OneStream.BusinessRule.DashboardDataSet._JL
    	Public Class MainClass
    		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As Object
    			Try
    				Select Case args.FunctionType
    					Case Is = DashboardDataSetFunctionType.GetDataSetNames
    						Dim names As New List(Of String)()
    						names.Add("Dims")
    						Return names
    					
    					Case Is = DashboardDataSetFunctionType.GetDataSet
    						If args.DataSetName.XFEqualsIgnoreCase("Dims") Then
    							Dim sql As String = "SELECT * FROM Dim"
    							Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
    								Dim result As DataTable = BRApi.Database.ExecuteSql(dbConn, sql, False)
    								Return result
    							End Using
    						End If
    				End Select
    
    				Return Nothing
    			Catch ex As Exception
    				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
    			End Try
    		End Function
    	End Class
    End Namespace

     

    • Sergey's avatar
      Sergey
      Contributor III

      Thanks guys !

      The thing is, I don't know what should be expected format for the graph and I can't find any documentatio about it. So far, these business rule will allow me to have a table with what's coming from my SQL query, but I don't know what the Graphs Data Adapter is expecting in terms of columns... any ideas ?

       

      Regards,