10+ Years ago, we released sqlDashboards. At the time we used what was the best and the most reliable charting library jfreecharts. It served us well for which we say a big thanks.
Now it’s time to launch a new HTML5 based SQL dashboards.
Pulse allows you to create and share real-time interactive dashboards with your team.
It currently includes almost all the charts you know and like from sqlDashboards plus a few extra. We will be reaching out to existing sqlDashboard customers to offer free trials as a thanks for your previous purchase.
We are excited to work on this new project for the next 10 years!
Our standard time-series graph interpolates between points. When the data you are displaying is price points, it’s not really valid to always interpolate. If the price was 0.40 at 2pm then 0.46 at 3pm, that does not mean it could be interpreted as 0.43 at 2.30pm. Amazingly till now, sqlDashboards had no sensible way to show taht data. Now we do:
For comparison here is the same data as a time-series graph:
The step-plot is usable for time-series and numerical XY data series. The format is detailed on the usual chart format pages.
sqlDashboards has now added support for stacked bar charts. e.g.:
The chart format is: The first string columns are used as category labels. Whatever numeric columns appear after the strings represents a separate series in the chart. Row in the table is shown stacked above the other, in the order as they appear.
sqlDashboards is specialised for real-time data visualization. Often this includes monitoring the state of orders, whether that’s baskets of goods being ordered online or financial transactions, we want to help you see what state those items are in. To support visualizing this data we’ve integrated the most powerful open source graphing: DOT and graphviz. If you install graphviz and make it available in your path, you can automatically get sqlDashboards to generate graphs from tabular data, like so:
From
To
label
cnt
PendingCancel
Calculated
Rejected
50
PendingReplace
Calculated
Rejected
10
PendingReplace
Calculated
Replaced
40
Calculated
PendingReplace
PendingReplace
50
Calculated
Filled
Trade
9400
Calculated
Calculated
Trade
5239
PendingCancel
Removed
Cancelled
150
Calculated
PendingCancel
PendingCancel
200
New
Calculated
Calculated
9660
New
Removed
Rejected
140
Created
Removed
Rejected
300
Created
New
New
9800
Notice to get the labels and styling you use a column called label or style respectively. The full format is detailed on the sqlDashboards example page.
Tow small changes in the latest release:
– Hide editor when first opening. Upon adding a chart the editor will show again.
– Bugfix: Mac “Save As” dialog was hiding the filename prompt. Fixed.
As always, it’s available to download now.
1.42 is now release and available to download. The biggest change is that saved files will no longer have usernames/pasaswords stored in them. This is to allow sharing amongst a team. Now sqlDashboards stores one default login as part of the software and attempts to use that with any opened files. If the login does not work it prompts the user. This can be changed under:
Full Change List:
Allow saving .das without username/password to allow sharing. Prompt user on file open if cant connect to server.
Bugfix: Allow resizing of windows within sqlDashboards even when “No table returned” or query contains error.
Allow setting File->title and use file name if file is Untitled.
If query is wrong and missing arg or something. Report why, report the reason.
KDB Database Only: Stop wrapping JDBC queries as we dont want kdb to use the standard SQL handler. We want to use the q) handler.
This is a quick video tutorial showing how to add highlighted rows to a dashboard as a few people had emailed asking this:
How to Highlight Rows in an sqlDashboard
The video content presented here requires JavaScript to be enabled and the latest version of the Adobe Flash Player. If you are using a browser with JavaScript disabled please enable it now. Otherwise, please update your version of the free Adobe Flash Player by downloading here.
To highlight a row we use specially named sqlDashboards columns SD_ that affect how the result is displayed but are not shown in the table
Add a column called SD_BGCOLOR specifying a color to use for the background of that row e.g. ‘green’ or using HTML hex triplet notation e.g. #224466
Add a column called SD_FGCOLOR specifying the color to use for the foreground of that row
Hex triplet is a six-digit, three-byte hexadecimal number used in HTML, CSS, SVG, and other computing applications to represent colors. The bytes represent the red, green and blue components of the color. One byte represents a number in the range 00 to FF (in hexadecimal notation), or 0 to 255 in decimal notation. This represents the least (0) to the most (255) intensity of each of the color components.
Using Case When
Commonly you will probably want to use SQL’s CASE-WHEN to alternate colors depending on an existing column value. e.g.
sqlDashboards 1.41 has been released and is available to download.
Changes include:
Support custom JDBC drivers and Authentication Services
Fix refresh rate display bug when widget is selected
Fix sqlchart.bat to allow running from any current path
The custom JDBC/security has been asked for a couple of times but it’s inclusion at this stage is experimental. We do have a few customers using it with qStudio and documentation is available here. If this interests you get in touch.
The following steps explain how to send an email with chart image attachments from the command line. First we must install the necessary email tools in linux:
Install Email Sending on Linux
apt-get install mutt
vi ~/.muttrc
That last line, opens the configuration for editing. For gmail you will have details like this:
account-hook imap://gmail/ “set
set imap_user = 'user@domain.com'
set imap_pass = 'PASSWORD'
set smtp_url = 'smtp://user@domain.com@smtp.gmail.com:587/'
set smtp_pass = 'PASSWORD'
set from = 'user@domain.com'
set realname = 'John'
set folder = 'imaps://imap.gmail.com:993'
set mbox= '+Inbox'
set spoolfile= '+Inbox'
set postponed= '+[Gmail]/Drafts'
set trash = '+[Google Mail]/Trash'
set header_cache=~/.mutt/cache/headers
set message_cachedir=~/.mutt/cache/bodies
set certificate_file=~/.mutt/certificates
set move = no
set timeout=15
auto_view text/html
To test this, first run “mutt”, it should display your gmail inbox. Then send an email using the command: mutt -s "test" receiver@domain.com <<< "test message"
Create an alias so we can run it with a shorter name
Run command to generate chart
wget http://www.sqldashboards.com/files/sqldashboards.jar
mv sqldashboards.jar /usr/local/bin/
alias sqlchart="java -cp /usr/local/bin/sqldashboards.jar com.timestored.sqldash.SqlChart"
sqlchart --servertype mysql --chart piechart --user USERNAME --password PASSWORD --database DATABASENAME--out pie.png --execute "select DATE(time),count(*) from qstudioreg group by DATE(time) ORDER BY DATE(time) DESC limit 9"
Voila we have our chart:
Send email with attachments
Use mutt to send the email, with -a to specify attachments like so: mutt -s "sql pie chart reports" ryan@timestored.com -a pie.png <<< "Here is your database report"
Done. So we successfully configured mutt for sending email, then generated our pie chart from mysql using sqlchart then we emailed it as an attachment. If you have any problems please let me know and I'll try to help. Here's the email in my inbox:
