How can you set pens in the EasyChart to columns that are in different tables? When selecting the pens at design time, I can browse the database (Microsoft SQL Server) and see my multiple tables but when I select columns from more than one table the “Choose time column” dropdown list is no longer available, which is understandable.
A workaround I tried was creating a view using the “full outer join” clause to combine multiple tables and then selecting the pens from that view. Even in that scenario, you have to combine the time stamp columns into one column. To do that I used this expression in my view:
ISNULL(Table1.t_stamp, Table2.t_stamp) AS CombinedTS
Of course it would get more complicated if combining more than two columns.
I might be missing something here, but just add pens from different tables directly. You can’t do it all in one step, but you can use the “Browse for Pens” button for each table separately. I mean, add your pens from table A, and then add your pens from table B. Don’t join the tables together - very inefficient!
You weren’t missing anything. I’m just not experienced with the software and didn’t realize that what I wanted to do could be accomplished that easily.
To take this scenario a bit further, in a post on “Tue Sep 04, 2007 10:53 am” you indicated how to programmatically manipulate the pens in a chart and you listed the set of attributes that make up a pen. What attributes of a pen would have to be set in my scenario where I would have pens from more than one table and would want each pen to be associated with the time stamp column from the appropriate table?
I see that the pen has a “TABLE_NAME” attribute that will need to be set appropriately but what else needs to be done? I’m wondering if I’ll need to reference a structure that contains the chart’s “tables”, add the appropriate number of “tables”, and set the attribute values of those “tables”, including the time stamp column. Will you please explain how that can be done? I don’t know Jython, so some sample code would be nice.
Lets take a step back here. What are you trying to accomplish? Why are you looking into modifying pen values via scripting - is something changing on the fly?
I’d like to be able to present the application user with a dropdown list of all available tags (there will probably be two drop downs - one for a plant area which will filter the second one to display only the tags in the selected process area) and let them select which tags they want to display in the chart.
Larry,
I sent Travis an email requesting that he contact you to show you how this works. Selecting pens at runtime isn’t immediately intuitive for our new users, but it’s relatively simple and very flexible once you get the hang of it! If you find yourself using any scripting or overly complex queries, there’s probably an easier way. As an aside, creating a tutorial is on my “to do” list.
The key to Selecting pens at runtime is storing your pen configuration in a table in your SQL database. Choose the pen options that interest you from the available list below. Each will be a column in your pens table with the same name. In your case, you will also want to add an area column.
Add a dropdown list to the window with the Easy Chart. Bind its Data property to the following query:
SELECT DISTINCT area FROM pens
Bind the Easy Chart’s Where Clause property to an expression that generates “WHERE area = selected_area”. It should look something like this:
"area='{Root Container.Dropdown.selectedLabel}'"
Viola, you should be done. The Easy Chart will automatically let the user choose relevant pens for that area. A few ideas to get fancier:
[ul]
[li]Store the areaid and area description separately - your JOINs could be useful there. [/li]
[li]See the demo project for how to add a dropdown that allows the user to change the graph mode between historical/realtime.[/li][/ul]
Here are the (optional) pen columns:
[ul]
[li]NAME[/li]
[li]COL_NAME[/li]
[li]TABLE_NAME[/li]
[li]XVAL_COL_NAME[/li]
[li]AXIS[/li]
[li]SUBPLOT[/li]
[li]ENABLED[/li]
[li]DATASOURCE[/li]
[li]COLOR[/li]
[li]DASH_PATTERN[/li]
[li]RENDER_STYLE[/li]
[li]LINE_WEIGHT[/li]
[li]SHAPE[/li]
[li]FILL_SHAPE[/li]
[li]GROUP_NAME[/li]
[li]WHERE_CLAUSE[/li]
[li]DIGITAL[/li]
[li]OVERRIDE_AUTOCOLOR[/li][/ul]
Technical Note (once you get the first part): In this case your graph options were defined at design time. You knew which pens belonged to which area and didn’t want to change that. The more general technique builds pen configuration tables using the same principals as described above. Users choose pens to be stored in their profile, which are stored in a database table. The charts pens property is bound to a query that reads this configuration. You will often use objects on the screen (like the list box) to allow users to change to change the WHERE clause of pens query, like to select an area or profile.
Let me know if I’m going too quickly over the deep end. It’s really not bad once you get comfortable with how databinding works. You are dealing with the 2nd most complex task that I can think of in FactoryPMI behind some of the more custom scripting applications.
Your first recommendation won’t quite work because we want the ability to display pens from different areas/tables on the same chart and we don’t necessarily want to display all pens from a selected area.
Your technical note is interesting and I think we’ll want to implement some of the concepts you mentioned there.
I think I have enough information to make this work now. I looked more closely at your list of pen attributes and the “XVAL_COL_NAME” is the attribute I didn’t notice before. That’s the one that would typically be set to the name of the time stamp column, right? Not noticing that attribute earlier is what got me off track earlier where I mentioned the ability to reference a structure that contains the chart’s “tables”.
Yes, the XVAL_COL_NAME is the timestamp column name.
One observation: How many tags are you dealing with in total? Why not add them all to the chart - the user can turn them on and off via the checkboxes that the easy chart generates for you. The check boxes will scroll - there is no limit. You can even group them by area. Assuming a manageable number of tags, this would of course be the easiest solution.
Beyond that, you certainly could have two dropdowns and an “Add this tag” button like you described. The only decision to make is where to store the user’s tag selection. Options are:
in a database table (possibly keyed off the username or client session ID. Most difficult, but most powerful, could save certain tag selections as named graphs)
…or in a property on the window (easy, but will clear out the tag selection every time they leave the window)
…or in SQLTags client tag memory (probably the best idea)
Feel free to call us if you get stuck putting this together,
We have hundreds of tags. We will want to give users the ability to save named sets of pens. You mentioned three options to store tag selections and I had planned to use the first option (database table). But, I’m interested in knowing a little more about the third option (SQLTags). Will you tell me a little more about how that would work?
Sure, the third option I was talking about was storing the user’s selection in a database-type Client tag. This would let the selection be saved while the user had the application open, but wouldn’t offer any retention between sessions.
You should go with option 1 (storing the tags in the database). If you need help with the table and/or query design, let us know.