What are the options to generate graphs from SQL Database?

Question

I have a SQL Server 2012 database from which I need to build a daily automated report with graphs. What would be the best solution to create graph?

I was thinking about automating everything with C# and Excel, but I am hoping there would be an better/easier solution.

Thanks

score 3 · Answer 1 · answered Apr 18 '13 at 22:14

I'd humbly recommend a web and data framework my company writes (it's free, open source, and public domain) called PowerShell Pipeworks. This lets you graph with code like this:

Select-Sql -TableName DBTable | Out-Html -AsBarGraph

It also has a cmdlet, Start-At, to run things normally, so your script would be something like

Start-At -Time "9:00 AM" -Everyday -ScriptBlock { 
    Select-Sql -TableName DBTable -ConnectionStringOrSetting SqlConnectionSecretSetting | Select-Object @{Name='DisplayedProperty';Expression={$_.CalculatedValue} | Out-Html -AsBarGraph |New-WebPage -UseJQueryUI | Set-Content $pathToHtmlFile
}

definitely recommend this company - I use their showui free tool. — Jimbo, Apr 19 '13 at 19:54

score 1 · Answer 2 · answered Mar 26 '16 at 22:31

You can chart any sql query using sqlchart from the command line. e.g. This generates a .png file 730x250:

sqlchart -D stockdb -P 1433 -servertype mssql -host localhost -e "select * from ohlc where sym='AMZN' AND (date > CURDATE()-60)" -o amazon-candlestick-dark.png --chart candlestick --height 250 --width 730 --theme dark

Then you can attach that to an email using your standard scripting language. Instructions for emailing on linux are here

score 1 · Answer 3 · answered Apr 25 '19 at 09:01

1

Try Grafana. You can link it to an SQL database as well. https://grafana.com/

answered Apr 25 '19 at 09:01

abhi002

71
2
10

score 0 · Answer 4 · edited May 23 '17 at 10:28

0

You can try to get data from SQL using C# and then generate graph using library in Create Excel (.XLS and .XLSX) file from C# (I myself used EPPlus for the graph before). Both steps are very easy and standard.

edited May 23 '17 at 10:28

Community

1
1

answered Apr 18 '13 at 17:48

Tianyun Ling

1,045
1
9
24

score 0 · Answer 5 · answered Apr 18 '13 at 17:54

0

Install and design reports in SSRS - email/store them on a schedule.

Powershell is also an option, let me know if you need more details on powershell.

answered Apr 18 '13 at 17:54

Jimbo

2,529
19
22

What about Powershell? SSRS is way too complex. :( – Martin Apr 18 '13 at 19:37

score 0 · Answer 6 · answered Apr 19 '13 at 15:23

0

If you want a "batteries included" solution you can try DBxtra, It can create reports from SQL Server, and other databases, and export or send them by email automatically with the Schedule Server option.

answered Apr 19 '13 at 15:23

Miguel Garcia

806
5
9

score 0 · Answer 7 · answered Apr 19 '13 at 19:52

Here are details on the powershell solution:

You will need to learn a little powershell ( you will never regret the time spent )
Install Powershell v3.0
Install module Invokesqlquery (this allows easy commuication to sql, can be downloaded from http://powershell4sql.codeplex.com/ )
Check out http://blogs.technet.com/b/richard_macdonald/archive/2009/04/28/3231887.aspx for charting

What are the options to generate graphs from SQL Database?

7 Answers7