# Report Tables query **URL:** \ **Category:** Ignition\ **Tags:** scripting\ **Created:** [June 12, 2018, 5:12pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427 "2018-06-12T17:12:53Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![harryzhou4498](https://avatars.discourse-cdn.com/v4/letter/h/71c47a/32.png) [@harryzhou4498](https://forum.inductiveautomation.com/u/harryzhou4498)\ **Post date:** [June 12, 2018, 5:12pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/1 "2018-06-12T17:12:53Z")
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? ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/a/a9c0e5bffabea0c053030217f9eec183e86f6e6d.PNG) This is what I’m trying so far for one of the tags, but I don’t think I’m on the right track. ---
**Author:** ![bfuson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfuson/32/4597_2.png) [@bfuson](https://forum.inductiveautomation.com/u/bfuson)\ **Post date:** [June 12, 2018, 5:54pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/2 "2018-06-12T17:54:22Z")
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. ---
**Author:** ![harryzhou4498](https://avatars.discourse-cdn.com/v4/letter/h/71c47a/32.png) [@harryzhou4498](https://forum.inductiveautomation.com/u/harryzhou4498)\ **Post date:** [June 12, 2018, 5:56pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/3 "2018-06-12T17:56:27Z")
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 ---
**Author:** ![bfuson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfuson/32/4597_2.png) [@bfuson](https://forum.inductiveautomation.com/u/bfuson)\ **Post date:** [June 12, 2018, 6:00pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/4 "2018-06-12T18:00:23Z")
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. ---
**Author:** ![harryzhou4498](https://avatars.discourse-cdn.com/v4/letter/h/71c47a/32.png) [@harryzhou4498](https://forum.inductiveautomation.com/u/harryzhou4498)\ **Post date:** [June 12, 2018, 6:05pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/5 "2018-06-12T18:05:19Z")
I did as you said, but I’m not getting any values in my preview ---
**Author:** ![bfuson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfuson/32/4597_2.png) [@bfuson](https://forum.inductiveautomation.com/u/bfuson)\ **Post date:** [June 12, 2018, 6:06pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/6 "2018-06-12T18:06:32Z")
Can you post a screenshot of your Tag Historian Data Source? ---
**Author:** ![harryzhou4498](https://avatars.discourse-cdn.com/v4/letter/h/71c47a/32.png) [@harryzhou4498](https://forum.inductiveautomation.com/u/harryzhou4498)\ **Post date:** [June 12, 2018, 6:08pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/7 "2018-06-12T18:08:42Z")
![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/5/5fcc57cd4b19cd374ad9695db3ed2155c7290c29.PNG) ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/6/6449e922270a83a831233c9cd242789d56f07929.PNG) ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/c/caa2c65dc74f1da8a3082bef108f10efe4dabec5.PNG) ---
**Author:** ![bfuson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfuson/32/4597_2.png) [@bfuson](https://forum.inductiveautomation.com/u/bfuson)\ **Post date:** [June 12, 2018, 6:13pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/8 "2018-06-12T18:13:33Z")
Show me your table placed in the Design tab and the result in the Preview tab. ---
**Author:** ![harryzhou4498](https://avatars.discourse-cdn.com/v4/letter/h/71c47a/32.png) [@harryzhou4498](https://forum.inductiveautomation.com/u/harryzhou4498)\ **Post date:** [June 12, 2018, 6:17pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/9 "2018-06-12T18:17:24Z")
![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/47896d6326ce53f0d262894731439ec96887b506.PNG) ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/9/9905a43dcbb12d91fa3b0002707e2b4e2ed1c1d2.PNG) ---
**Author:** ![bfuson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfuson/32/4597_2.png) [@bfuson](https://forum.inductiveautomation.com/u/bfuson)\ **Post date:** [June 12, 2018, 6:32pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/10 "2018-06-12T18:32:38Z")
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. ---
**Author:** ![harryzhou4498](https://avatars.discourse-cdn.com/v4/letter/h/71c47a/32.png) [@harryzhou4498](https://forum.inductiveautomation.com/u/harryzhou4498)\ **Post date:** [June 12, 2018, 6:45pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/11 "2018-06-12T18:45:41Z")
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 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/2c92c986f8b292c6569287885d2cec78e3e90fce.PNG) I’d ideally want no repeat dates. ---
**Author:** ![bfuson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfuson/32/4597_2.png) [@bfuson](https://forum.inductiveautomation.com/u/bfuson)\ **Post date:** [June 12, 2018, 6:52pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/12 "2018-06-12T18:52:31Z")
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. ---
**Author:** ![harryzhou4498](https://avatars.discourse-cdn.com/v4/letter/h/71c47a/32.png) [@harryzhou4498](https://forum.inductiveautomation.com/u/harryzhou4498)\ **Post date:** [June 12, 2018, 7:03pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/13 "2018-06-12T19:03:21Z")
What if I specifically want the value from a certain time of day, say 9 am? ---
**Author:** ![bfuson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfuson/32/4597_2.png) [@bfuson](https://forum.inductiveautomation.com/u/bfuson)\ **Post date:** [June 12, 2018, 7:16pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/14 "2018-06-12T19:16:51Z")
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: - [https://inductiveuniversity.com/course/tag-historian-in-ignition](https://inductiveuniversity.com/course/tag-historian-in-ignition) - [https://inductiveuniversity.com/video/tag-historian-query?r=/course/reporting-in-ignition](https://inductiveuniversity.com/video/tag-historian-query?r=/course/reporting-in-ignition) ---
**Author:** ![harryzhou4498](https://avatars.discourse-cdn.com/v4/letter/h/71c47a/32.png) [@harryzhou4498](https://forum.inductiveautomation.com/u/harryzhou4498)\ **Post date:** [June 12, 2018, 7:58pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/15 "2018-06-12T19:58:58Z")
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: ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/2/2a514fde51670cf136710c4e503794b0bd62f3db.PNG) ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/8/89e73facdacea06d1f188ae9ab05a7b6bc91509c.PNG) 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. ---
**Author:** ![bfuson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfuson/32/4597_2.png) [@bfuson](https://forum.inductiveautomation.com/u/bfuson)\ **Post date:** [June 12, 2018, 8:38pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/16 "2018-06-12T20:38:44Z")
In the Advanced settings, make sure “Prevent Interpolation” is checked. ---
**Author:** ![harryzhou4498](https://avatars.discourse-cdn.com/v4/letter/h/71c47a/32.png) [@harryzhou4498](https://forum.inductiveautomation.com/u/harryzhou4498)\ **Post date:** [June 13, 2018, 12:36pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/17 "2018-06-13T12:36:36Z")
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? ---
**Author:** ![bfuson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfuson/32/4597_2.png) [@bfuson](https://forum.inductiveautomation.com/u/bfuson)\ **Post date:** [June 13, 2018, 1:05pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/18 "2018-06-13T13:05:03Z")
I tested those settings myself on a test tag using a much smaller interval and the results were what you’re expecting. ---
**Author:** ![harryzhou4498](https://avatars.discourse-cdn.com/v4/letter/h/71c47a/32.png) [@harryzhou4498](https://forum.inductiveautomation.com/u/harryzhou4498)\ **Post date:** [June 13, 2018, 2:10pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/19 "2018-06-13T14:10:11Z")
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? ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/4/4e3939a951541a066809415c2f44bc658589c9fb.PNG) I don’t have repeat values currently. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [June 13, 2018, 2:14pm UTC](https://forum.inductiveautomation.com/t/report-tables-query/18427/20 "2018-06-13T14:14:45Z")
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. [Next page](https://forum.inductiveautomation.com/t/report-tables-query/18427.md?page=2)