Report Tables query

I want to use a report table to display certain values taken from tags such as motor speed, motor current, etc. Additionally, I want this data to be shown each day for the past 30 days or so on the table. How can I do this?

This is what I’m trying so far for one of the tags, but I don’t think I’m on the right track.

I’m assuming {Comp 3/Motor Speed} is a numeric tag? If so, you’re query doesn’t make much sense. When you link to a tag path inside of an SQL binding {like this}, the actual query that gets run will have the value of the tag inserted there.

Your query is trying to select rows from a table called “Whatever-the-value-of-Comp-3/Motor-Speed-is”. So if the motor speed were something like 73.1, you’re query is saying “select rows from 73.1”.

You COULD setup your own logging schema for tracking that tag to your own table, and then recall the values in SQL as an input to your report, though I think you’ll instead want to setup Historian tracking on those values. Once your Motor Speed tag is being tracked by the Ignition Historian, you can pull in its historical values by adding a Tag Historian Query Data Source to your report.

I’ve tried pulling in the motor speed tag’s historical values by adding a tag historian query data source to my report, but I’m not sure how to get each day from the last 30 days from that

Set your EndDate report Parameter to an expression with the value of now()
Set your StartDate report Parameter to an expression with the value of dateArithmetic(now(), -30, "day")

In your Tag Historian data source, bind the starting and ending dates to each respective parameter.

I did as you said, but I’m not getting any values in my preview

Can you post a screenshot of your Tag Historian Data Source?

Show me your table placed in the Design tab and the result in the Preview tab.

In the design tab, if you drag the Data Source key onto the page it will automatically create a table with the Data Key configured. Then you just need the name of your columns in the details columns, not the full key path. Just @motor speed@ should work.

If that doesn’t yield any results, you might want to verify that you’re actually storing data to the Historian.

I was able to drag the data source key onto the page. I’m still having an issue where I’m getting too many dates

I’d ideally want no repeat dates.

That’s due to how you’ve configured your Tag Historian Data Source. If what you’re wanting is the average speed for each day, then set the Aggregation Mode to Basic Average and the Sample Size to an Interval of 24 hours. Keep in mind this won’t be the daily average from 00:00:00 to 23:59:59, but from your StartDate to your EndDate in 24 hour increments.

If you want the former (average for each single day), you’ll need to change the expressions driving your StartDate and EndDate parameters.

1 Like

What if I specifically want the value from a certain time of day, say 9 am?

If you only want one value from 9 am use Aggregation Mode Closest Value with a Fixed Sample Size of 1.
If you want hourly average starting at 9am, set your StartDate expression to:
setTime(dateArithmetic(now(), -30, "day"), 9, 0, 0)

I’d recommend reviewing the Inductive University courses for the Tag Historian and the Reporting Module:

1 Like

I’m still having issues; I’ve reviewed the courses but I don’t think it’s getting me anywhere closer to the root of the problem. To reiterate: I’m trying to display the value for a tag at a certain time every day for the past 30 days. I have this right now:

These values are clearly wrong and for some reason, it shows that my motor speed is just continuously increasing gradually to what it is supposed to be. It does the same for other tags as well.
I’ve tried using aggregation mode closest value with a fixed sample size of 1, but that only gives me 1 date which is not what I’m looking for.

In the Advanced settings, make sure “Prevent Interpolation” is checked.

When I check off “Prevent Interpolation”, I only get one data point for 6/12/18. Is this because I turned on historical tags for that data on 6/12/18 or are my settings still off?

I tested those settings myself on a test tag using a much smaller interval and the results were what you’re expecting.

I know that checking off prevent interpolation will instruct the system to not include values in a window if the raw data does not provide any new values for that window, but what if I want to show repeated values?

I don’t have repeat values currently.

Consider using a script data source in your report to “reprocess” raw history data into the form you wish, with whatever aggregate/last value rules you need.