Chart Auto scaling using SQL data

[Former Member]

Does anybody know if autoscaling from a SQL server looks at the MinRange or MaxRange attributes of the Columns element in an llumdoc xml?

Basically what i'm trying to do is force autoscaling depending on what values I have from my query. I've tried various autoscale techniques from what the display template offers, but they don't suit my needs (i'm using a custom chart with Stacked Bar and Line pen types)

In my BLS, I'm finding the max value of my xml output, and I want to set the Y axis to this value. Note that the Y axis must be the same scale for all pens. (Thus autoscaling by pen doesn't work well) The documentation says Global Server Scaling only works with tag data sources.

[Former Member · Accepted solution]

I am using BLS to assign my min and max already, however when I read up on global server scaling this is what I found (this is 11.5 documentation)

This parameter determines whether the min/max ranges should be based on the values returned by the connector in its legend information (typically min/max engineering ranges).  Note that only Tag connectors (and not all Tag connectors) are capable of providing this information.

I figure I could give it a try, but since my data is SQL, not Tag. I figured it wouldn't work.

[agentry_src · Accepted solution]

Hi Doug,

I would also try simply turning off all the server scaling for global and pens and use the Global Ranges dynamically set by your BLS. That should actually work best for your purposes.

Mike

[jcgood25 · Accepted solution]

Doug,

If you send an SQLQuery right to the iChart, Server Scaling will not be of any use, because the MinRange and MaxRange attributes for the columns are 0 and 1 (Description is also not much use). These values would traditionally come from the historian tag database in a TagQuery, which is why you will find the iChart set for Global Scaling and in Line mode, because a History tag query is a match made in heaven for the iChart. But if you return 2 columns in your query results with constants that you want for the min and max Y scale you can map the column names into the Min Range Column and Max Range Column iChart properties (in 12.0 they are on the Server Scaling panel). Description will work the same way as a tag query if you map it from your query results in the Description Column iChart property (including ShowTagDescription and/or LegendShowDescription). The dataset will have 2 or 3 more columns than the original, but you will be able to accomplish what you are after.

If you use an XacuteQuery and send back the expected IllumDoc format as your OutputXML then you of course have the MinRange, MaxRange, and Description attributes in the Rowsets/Columns section and this will allow it to behave similar to a tag query as well. I've done this before with putting the LegendOnTop and having it show only the TagDescription instead of the TagName, and buried a nice message in the Description attribute for my IllumDoc column.

In both of these cases for SQL and Xacute query templates, you will be able to use GlobalServerScaling, which really ends up either following the Column attributes in the dataset, or the iChart mapped columns for the 3 respective fields.

Regards,

Jeremy

[Former Member]

Jeremy

Your solution worked, but works but I found a twist. Actually it works in two ways, i could ether map a column for Min / Max, or push MinRange / MaxRange in the XML document from BLS and use servers scaling, either way works.

Here's the twist. The chart takes the maxvalue and multiplies it by 5 for the top of the scale. For example if my maxvalue is 100, the chart will scale to 500. If maxvalue is 10, the chart scales to 50. So to fix this I simply divide maxvalue by 5 in the BLS and everything works out fine. Actually, so that I can get headroom in the chart, I add 5% to the maxvalue then divide by 5.

Thanks for the help.

[jcgood25]

Stacked Bar chart perhaps?

100 MaxRange x 5 Pens = 500 Y-Axis?

[Former Member]

Ah.. didn't think about that, makes sense now. I'm using the stacked bar chart as a way to color context my bars, Not really using it as a "traditional" stacked bar chart.

I didn't consider how stacked bar charts handle max value. Thanks again.

[agentry_src]

Doug,

Why don't you use Global Server scaling and assign the max and min ranges dynamically from your BLS? Auto scaling would ignore those values and determine ranges from your data.

Good luck,

Mike