Reg: Line chart for Sql Query

[former_member329434]

Hi Friends,

I have requirement to present live data from SQL database into MII server in the form of Line Chart . I could able to present live data in Bar, Grid and other chart types.

( OPC is connected to SQL database, Using IDBC connector, established connection to MII server)

Somehow, I am unable to present live data in the form of Line chart. with same parameter settings, I could acheive using tag query and present a line chart.

with Line chart using SQL query, I am getting only grid screen with out any trends.

I want to know whether it is possible to present Line chart for a SQL query . Please let me know if i need to do some workaround.

As I am very new to MII environment, I also request forum, to show some links on SPC chart types ( blogs or small examples from your workplace).

Thanks,

Suman

[Former Member]

Hi Suman,

First, answer to your question if we can plot a line chart with a SQL qry is Yes, we can plot any type of chart with either a SQL,TAG or Xacute qry.

Before suggesting any workaround to your issue, can you please tell if you are plotting the data against some Date Time value?

Warm Regards,

Anuj

[former_member329434]

Hi Anuj,

I have a data field called 'datetimestamp' in my query . My input query has ( 'Datetimestamp', val1, val2,val3,,val4, freq, and total) fields.

Please let me know if you need any other details.

Appreciate your early response.

Thanks,

Suman

[Former Member]

Hi Suman,

Wow thats cool 🙂

I had also faced this issue way back in which I was able to present data in a bar chart but wasnt able to present the same data in a line chart.

If I remember correctly, the way out to this issue was something related to DateTime format. I would suggest you to first try with your 'Datetimestamp' column in the XML date format (yyyy-MM-ddTHH:mm:ss) and then play around with other non XML formats because I am not able to recall the exact solution. And make sure that the DateTime format you are mapping to your chart template matches the one defined in the X-Axis tab of template.

Hope this will get you something!!

Warm Regards,

Anuj

[former_member329434]

Hi Anuj / Friends,

I am trying with all possibilities and would explore  Timestamp parameter also. If anyone gets any idea, please share .

MII : 12.2.4

Thank you Anuj.

Thanks,

Suman

[sidnooradarsh]

Suman,

Not the best solution but try this,

Convert the datetime to string (use convert in case of SQL Server or to_char in case of Oracle)

Now go into X-Axis Section of chart display template and Add this column to X-Axis Label Columns.

Regards,

Adarsh

[former_member329434]

Hi Adarsh,

Thank you.

I have changed data type to varchar2 and tested. Still, I didn't get desired result.  I don't see any single field plotting on the chart.

any other thoughts....

Thanks,

Suman

[Former Member]

Hi Suman,

Can you attach the screenshot of the output of of your SQL query or the xacute, whichever query is the final query mapped to your chart. All the columns must be visible and try to capture few rows.

Warm Regards,

Anuj

[sidnooradarsh]

Hello Suman,

When in trouble always start with something very simple and basic, this is how I would try to debug,

1) First take a simple Query with few records say 5 or 10 even better if you can start with only two columns like Datetime stamp and Value columns

2) Take a brand new iChart from Display template ( do not copy from any existing template because some items might be set that chart doesn't like)

3) Then on Data Mapping tab, Add your Value Column/s to Value Columns section, having Value column as numeric is preferred not string

4) Convert your Datetime column to varchar2

5) Then go to X-Axis tab, Add the datetimestamp Column to X-Axis Label Columns section

6) Once the basic setup is working then start playing or experimenting with it by changing item by item

Try something like above this should help you to understand and debug.

Good Luck!

Regards,

Adarsh

[jcgood25]

In order for an iChart in Line mode to show a trend line like a basic Simulator tag query in History mode you need to make the SQLQuery 'time based''.  This is done by mapping a times tamp column in query mode or using the [SD] and [ED] tokens in your fixed query.  The chart and the time refresh controls don't know which field is your date time column, nor can it reforms late the new query requests when you scroll forward or backward in time.  This happens behind the scenes in Tag queries, but for non-tag query templates you need to enable this.  Consider a table with 2 columns called MyDateTime and MyValue with a real date time and not a string, and a numerical value.  Then consider a simple query like this:  SELECT * FROM MyTable WHERE MyDateTime BETWEEN [SD] AND [ED] ORDER BY MyDateTime ASC  Map your ValueColumns to MyValue and set auto scaling to true (not server scaling).  The chart in line mode should take care of the rest.  At runtime when you pick the date buttons in the time refresh control bar or the left/right navigational controls, the query will be reformulated and the date tokens will send the new dates to the DB query.  Don't forget that the default date criteria in your query will default to the last 60 minutes, so set these fields accordingly.

[former_member329434]

Dear All,

Thank you very much for your support.

I think line type will not work for current mode type for any connector( not sure..). I have done some testing using SQL query and also using Simulator server.( tag query with only history mode works).

My problem got fixed after changing the current generation table( instant values from shop floor, only one record at a time ) with another history table( 2 mins delay history table with n number of records).

Want to know from the forum, instead of single record(live date) does line type chart works for only bunch of records ?

Is there any way , I can present live data( single record) in the form of Line Chart?

Thanks,

Suman

[Former Member]

Hi Suman,

I dont think that plotting a line chart for a single record will be a good idea.

Anyways, to answer your question, number of records has nothing to do with the chart type. But if you have a single record, you wont be able to see any lines plotted in the chart area, rather you will see a single marker because atleast two data points are required to see a line (basic geometry 🙂 ) in the chart and I think you must be getting this single marker in your chart area for that single record.

Best Regards,

Anuj