How to trend DBLog data from a single table on a LineChart
Trending DBLog data from a single table on a LineChart is possible using queries and a timer, though it usually recommended to trend from Datalogs instead.
We first need to setup the LineChart correctly to view data from a SQL table:
- Drop a LineChart on to the Graphic Form and turn off its Realtime setting via its Smart Tag option or property in the Property Grid.
2. In the Edit Chart dialog, we then need to set the Bottom axis to automatic scaling.
3. We also set the Datasource for each series to DataSet and not Value.
Next we add a Timer component to the Graphic Form to trigger the Query Spider on a regular interval:
- Drop a Timer component on the Graphic Form.
- With the Timer component selected set its Enabled property to true in the Property Grid.
- Set the Timer component Interval property in the Property Grid to 20000ms (20s) as well. We recommend not going below 5000ms (5s) as this may impact performance with fetching data.
Next we setup the OLEDB Datasource to return data per tag from the Adr_DBLog table using a Select Query.
- Create an OLEDB Datasource to connect to the DBLog database.
2. We now create a Select Query to fetch data from the Adr_DBLog by a specific tag name as parameter which will be specified later. This query will also fetch the data ordered by descending by the dt (datetime) column and fetch the top 1000 rows.
SELECT TOP 1000 [Adr_DBLog].* FROM [Adr_DBLog] WHERE [Adr_DBLog].[Tagname] = '<!!tagname!!>' ORDER BY [Adr_DBLog].[dt] DESC
Next we create a query spider executing the query above for each tag/series for the LineChart:
Create a Query Spider for the tag/series.
Configure the Query Spider to execute the query above.
Set the tagname input of the Query Spider to the tagname and slot you wish to return data for that specific series.
Right click set the Query Spider to ‘Trigger On Event’, and set its Event to the Timer tick event.
5. Right click set the Query Spider to ‘Propagate Outputs at Startup’.
6. Right click set Trigger Now on the Query Spider to get the initial data set.
7. Drag the Query Spider ‘Return Object’ output to the LineChart Series property spider ‘Series [Value/DataSet]’ input.
8. In the DataSet Data Element dialog that pops up, set the YValue member to the column Value and set the XValue member to the dt column and click finish
9. Repeat this process for all other tag/series you which to fetch data for from the Adr_DBLog table.
Run the Graphic Form and you should have a similar LineChart below updating with new data every 20000ms (20s):
A Sample Graphic Form with above setup is below (Adroit 10.0.5.1 or higher):
TrendingFromDB.viz (81.9 KB)