Perspective- Trend a custom SQL query

Ignition
1

I can’t get perspective to trend a custom query.
I’m using a time series chart and a named Querie

SELECT DateTime, LeftHeight
FROM Shipping_PostHeight
WHERE DateTime >‘2020-09-09 14:00’ ORDER BY DateTime DESC

when tested the Named Querie it shows all the values and columns with no issues

If I use a table it shows the data, but the DateTime column shows values instead of Dates
image

and my Time Series Chart keeps throwing “Componen error”… I’m bindig the named querie as a dataset in the Table Series property

Does anyone have a basic example on how to trend and show data coming from SQL, not historian…?

thanks!

2

Hi

1.Under Query Binding see the Return Format is “auto” where you configure your named querie for chart.
2. TimeSeriesChart - delate the ‘0’ Object under plots and add again

this might help you.

3

Returning format is in AUTO and deleted the o Obejct under plots… still the same error…

image
image772×532 38.6 KB

Do you have any sample projects with trends using a SQL database (not history) that I can use as reference?
thanks for the help.

4

Did you try setting the return format to json?

5

Sorry, forgot to ask what database you are using.

6

Dang, I need coffee…

Where are you binding it in the chart? It needs to be in the ‘data’ portion of the series.

2020-09-11_11-08-28
2020-09-11_11-08-281235×433 45.6 KB

7

Jordan, I’m using Microsoft SQL. You were right, I was doing the binding in the wrong location.I did the binding in the ‘data’ of the series and it worked well… =)

I have a table with the same data, but my first column (Date Time) is showing values instead of the dates… Do you know how to set that column to show Date Time instead of values?..

Thanks for the help!

8

If you add the columns in the column configuration, it should format as a date. You can customize it by adjusting the dateFormat property.

image
image445×543 30.6 KB

1 Like
9

Hi) can you help me? What I do not right?

image
image1930×1053 266 KB

10

Try Putting your t_stamp1 first in your query....For Example:
Select
t_stamp1,Total_count,singlecarrier_count,doublecarrier_count
From
(Select
alltable.t_stamp1 as t_stamp1,
alltable.InductTotal As Total_count,
alltable.singlecarrier_count as singlecarrier_count,
alltable.doublecarrier_count as doublecarrier_count

From alltable
Where (alltable.t_stamp Between:starttime and :endttime)) basa

11