Build Dashboard from a Data Table query

Sergey
Contributor III

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 3

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
Community Manager
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

 

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,