Charting data with CanvasJS

[wuench]

CreateChart Macro
Version 1.1



Description
This macro will create interactive/animated HTML5 line charts from a MySQL database for data logged with the datalog DB driver and display them in the CQC web server. The default config allows for tooltips, zooming, and turning on/off series dynamically. The macro literals can be edited for many more options. To see what all is available see the canvasjs.com website for more info.

Installation

• Install Macro Pack
  
• Download CanvasJS and install the canvasjs-min.js file under /htmlroot/script
  
• Optionally, open the macro in the Macroworkshop and edit the default DSN, Username, and Password. This is so you don't have to pass those parameters on the URL.

Usage

Code:

http://cqcserver/cmlbin/user/CreateChart?Type=parm&dsn=CQCDB&series1=descriptor1&series2=descriptor2...

Parameters

• dsn - DSN of your MySQL DB
  
• user - Username of your MySQL DB
  
• pass - Password of your MySQL DB
  
• type - Graph Type (Min5,Min10,Min15,Min30,Hour,Day,Week,Month,Year)
  
• seriesX - Descriptor you would like to chart.
  
• width - Chart width in pixels or percent (i.e. 300px or 100%)
  
• height - Chart height in pixels or percent
  
• maxpoints - Maximum data points to display/scale graph to (default 50)
  
• title - Graph title
  

Examples

Code:

[b]Chart 2 Series, Period = 1 Day[/b]
http://cqcserver/cmlbin/user/CreateChart?dsn=CQCDB&type=Day&series1=IndoorTemp&series2=AtticTemp

[b]Chart 2 Series, 1 Day, Size 300x600, Title Temperatures, Datapoints 100[/b]
http://cqcserver/cmlbin/user/CreateChart?dsn=CQCDB&type=Day&series1=IndoorTemp&series2=AtticTemp&title=Temperatures&maxpoints=100&width=600px&height=300px

Versions

• Version 1.0 - Original Version (Did not function in IE or CQC Web Widget)
  
• Version 1.1 - Works in IE - Fixed JS Date function to be IE Compatible
  

[Dean Roddey]

We should probably move this to the 'how to' section?

[znelbok]

Awesome.

Does this work with the MS SQL offering that was originally proposed for the DB Datalogger driver or just MySQL only

[wuench]

i don't know if it will work for other DBs or not, I am not much of a SQL guy. You might need to adjust the SQL statement. There is only one, it is a literal in the top of the macro. This doesn't actually use the driver, it accesses the DB directly, but it does assumes that the data is in the structure used by the driver of Timestamp,Value.

[wuench]

Well, maybe jumped the gun. It isn't working correctly in IE and therefor doesn't work in a web widget either. Guess i'll have to do some tweaking....

EDIT: Ok, fixed with version 1.1. Looks like IE isn't capable of converting the raw date string in the format stored in the DB so I had to convert it.

[wuench]

We should probably move this to the 'how to' section?

Yeah, sorry. Feel free to move it...

[znelbok]

I tried this with MSSQL and got an error

Code:

Server: CQC Web Server
Connection: Close
Date: Mon, Aug 31 21:53:13 2015 +1000
Content-Type: text/html
Content-Length: 363

<h1>An error has occurred creating the graph...</h1>
01/01 00:00:00-Zeus01, CQCWebSrv, CQCWebSrvWorkerThread1
{
    CIDDBase, CIDDBase_DirectStatement.cpp.88, Failed/Cannot Do, Error: 6502/65535/195
    Direct statement execution failed for statement
    [Microsoft][ODBC SQL Server Driver][SQL Server]'TO_SECONDS' is not a recognized built-in function name.
}

Also, are spaces in the DSN and/or descriptor supported. I couldn't get the browser to return anything when I used a DSN with a space. I created a second DSN [no spaces] and it worked. The descriptor seems to work, but I get the above error.

[wuench]

I don't know, I don't have any in mine. You could try putting tick marks around the DSN in your SQL statement and see if that fixes it.

Oh sorry looks like the DSN is a part of the connect statement not in the SQL. So I guess that is a Dean question. I am not sure. Looking at your error, it looks like it doesn't like the TO_SECONDS. Basically what that statement is doing is converting everything to seconds then doing a little math, so it will go back last day, last month, etc in seconds. Maybe MSSQL needs a different function or even a different approach.

[dlmorgan999]

I'm pretty familiar with MS SQL, and I definitely want to give this a try. I'll report back on what I find, although I may not have time to play with it until the weekend.

[znelbok]

Totally forgot about this thread.

Was any progress made in getting it to work with MS SQL?

Mick