I have a graph that shows the last 7 days of data for a process. I would like to add a scroll bar or buttons or whatever to allow the user to scroll back and forth to show data from different periods. Alternatively they could type in a date range and use that, if it is easier to achieve. TIA D. Lewis
Affecting date ranges on graphs is a very common task, and there are many different ways to implement this.
Before I go over some of them, you may want to check out the Graph Template Window goodies available at http://www.inductiveautomation.com/products/factorypmi/goodies/. These windows use an approach very different from the ones described below, that is designed to aid in rapid graph window development. Be sure to read the goodie instructions!
Okay, now onto the more ‘standard’ methods of affecting date ranges.
The simplest way to do this is to add two popup calendar components to the graph screen, one for the start date/time, and one for the end date/time. Then simply bind the selected dates on these calendars into your SQL query, which would look something like this:
SELECT t_stamp, temperature, pressure FROM history WHERE t_stamp >= '{Root Container.StartDate.date}' AND t_stamp <= '{Root Container.EndDate.date}' ORDER BY t_stamp
Whenever the user changes either date, the query runs. (Any bound query will run wheverver the query itself changes - even if the query is set to ‘Polling Off’)
The pro of this method is: Simple to make. The con is: users can inadventently select very large date ranges, which can cause undue network traffic and database strain.
There are many variations on the method above. It is usually a better design to have one date, and then have a [number] of [units], where [number] is a numeric text field and [units] is a dropdown. It is slightly more complex to create queries for this variation, as it involves your database’s date arithmetic functions.
Now, if you want button at the bottom for scrolling weeks, this would be neat, and very intuitive for the end user. Here is how you could implement it:
Add an integer property to your root container called “days”. This will represent the number of days back that we are viewing. Set this to -7 initially.
Bind your graph queries to ones like this one (Note - this syntax is specific to Microsoft SQL Server, which if my memory serves me correctly, is what you’re using)
SELECT t_stamp, temperature, pressure FROM history WHERE t_stamp >= DATEADD(DD, {Root Container.days}, GETDATE()) AND t_stamp <= DATEADD(DD, {Root Container.days}+7, GETDATE()) ORDER BY t_stamp
This will select all rows where the date is between seven days before now, and 0 days before now.
Now set up your back and next buttons to subtract 7 and add 7 to the “days” property, respectively. Each time the “days” property changes, the query will be run, looking at a different week.
Let me know which method you think fits your application best, and I can help you with more detail about that method, if necessary.
Hi Carl: Thanks for the methods. Good descriptions on basic techniques for modifying queries based on screen control values. I will also download the goodies on your website and look through them. Thanks again. David