25 Jul
Using a database to populate an Open Flash Chart graph
Just got Archie Smuts comment asking how to populate a Open Flash Chart graph using results from a database.
Just for reference, the code that follows represents this graph:
Examples for version 2 are here.
Using the new version of Open Flash Chart, here is an example for you to follow:
- Start a rails app and install the plugin per these instructions.
- The results table that I am working with has these rows:
1 2 3 4 5 6
Result.create(:student_name => "Jack", :subject => "History", :test_score => 97 Result.create(:student_name => "Jack", :subject => "Science", :test_score => 85) Result.create(:student_name => "Jill", :subject => "History", :test_score => 92) Result.create(:student_name => "Jill", :subject => "Science", :test_score => 57)
- Create a controller, mine is test_it:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
class TestItController < ApplicationController def index @graph = open_flash_chart_object(600,300,"/test_it/graph_code") end def graph_code # we will have bars for each student subject combo bars = [] # random colors to chose from colours = ["#459a89", "#9a89f9"] # the results results = Result.find(:all) # group by subject and use subject as the key results.group_by(&:subject).each do |subject, result| # 3d bar graph, could be any bar graph though bar = Bar3d.new bar.set_key(subject, 3) bar.colour = colours[bars.size] bar.values = result.map(&:test_score) bars << bar end # some title title = Title.new("Test Results") # labels along the x axis, just hard code for now, but you would want to dynamically do this x_axis = XAxis.new x_axis.labels = ["Jack", "Jill"] # go to 100% since we are dealing with test results y_axis = YAxis.new y_axis.set_range(0, 100, 10) # setup the graph graph = OpenFlashChart.new graph.bg_colour = '#ffffcc' graph.title = title graph.x_axis = x_axis graph.y_axis = y_axis graph.elements = bars render :text => graph.to_s end end
- Create a view, mine is index.html.erb for the test_it controller:
1 2 3 4
<script type="text/javascript" src="/javascripts/swfobject.js"></script> <%= @graph %>
Hope that helps.
25 Responses
to “Using a database to populate an Open Flash Chart graph”
Sorry, comments for this entry are closed at this time.
Hello, the first example worked. But in this example with database, I don’t know where it goes or where I create the first part: result.create(…) The others, know: view and controller, but this I don’t know
@Cecci – good question 🙂 When I did it, I opened up script/console and ran each line one at a time. You could also place it in a migration if you wish.
Hi, It worked! Thank you… Now I am trying to do the graph of pie, but I do not know that receives |open_flash_chart_object()| I do not know where from are those folders. Might you make clear to me that it receives?
Sorry, but I don’t speak english very well
@Cecci – Sorry but I am not exactly clear what you are after, but this example of a pie chart may help – http://pullmonkey.com/2008/7/29/open-flash-chart-ii-pie-chart
Also, you may want to know what parameters to pass to open_flash_chart_object(). It takes these parameters:
open_flash_chart_object(width, height, url, use_swfobject, base)
o width is the px width of the graph
o height is the px height of the graph
o url is the path to your graph_code action
o use_swfobject is always true, it is legacy and used to determine if you were going to use the javascript swfobject.js or not
o base is the path to your open-flash-chart.swf file if it is not right under your RAILS_ROOT/public directory
The file that contains the code is under:
RAILS_ROOT/vendor/plugins/open_flash_chart/lib/open_flash_chart_object.rb
Hey… So I have a collection of payments with two different months (Oct 07 and Aug 08). I’m trying to get a collection for each month graphed. Everything is working great except the fact that I have to bars for each month. Both have the right values but I just need on bar for each month. Please help.
<filter:code attributes=lang="ruby">
def graph_code
# we will have bars for each student subject combo
bars = []
months = []
monthly_totals =[]
# random colors to chose from
colours = ["#459a89", "#9a89f9"]
# the results
@payments = Payment.find(:all, :conditions => {
:account_id => current_account.id,
:created_at => (11.months.ago.beginning_of_month..Time.now)
})
# group by subject and use subject as the key
@payments.group_by(&:month).each do |month, payments|
month_total = 0
for payment in payments
month_total += payment.amount
end
monthly_totals << month_total
# 3d bar graph, could be any bar graph though
bar = Bar3d.new
# bar.set_key(month, 3)
bar.colour = "#459a89"
bar.values = monthly_totals
bars << bar
months << month
end
@max = monthly_totals.max.to_i + 100 – (monthly_totals.max.to_i % 100)
# some title
title = Title.new("Payments")
# labels along the x axis, just hard code for now, but you would want to dynamically do this
x_axis = XAxis.new
x_axis.labels = months
# go to 100% since we are dealing with test results
y_axis = YAxis.new
y_axis.set_range(0, @max, @max/10)
# setup the graph
graph = OpenFlashChart.new
graph.bg_colour = ‘#ffffff’
graph.title = title
graph.x_axis = x_axis
graph.y_axis = y_axis
graph.elements = bars
render :text => graph.to_s
end
< /filter:code>
Oops…here it is:
<filter:code attributes=lang="ruby">
def graph_code
# we will have bars for each student subject combo
bars = []
months = []
monthly_totals =[]
# random colors to chose from
colours = ["#459a89", "#9a89f9"]
# the results
@payments = Payment.find(:all, :conditions => {
:account_id => current_account.id,
:created_at => (11.months.ago.beginning_of_month..Time.now)
})
# group by subject and use subject as the key
@payments.group_by(&:month).each do |month, payments|
month_total = 0
for payment in payments
month_total += payment.amount
end
monthly_totals << month_total
# 3d bar graph, could be any bar graph though
bar = Bar3d.new
# bar.set_key(month, 3)
bar.colour = "#459a89"
bar.values = monthly_totals
bars << bar
months << month
end
@max = monthly_totals.max.to_i + 100 – (monthly_totals.max.to_i % 100)
# some title
title = Title.new("Payments")
# labels along the x axis, just hard code for now, but you would want to dynamically do this
x_axis = XAxis.new
x_axis.labels = months
# go to 100% since we are dealing with test results
y_axis = YAxis.new
y_axis.set_range(0, @max, @max/10)
# setup the graph
graph = OpenFlashChart.new
graph.bg_colour = ‘#ffffff’
graph.title = title
graph.x_axis = x_axis
graph.y_axis = y_axis
graph.elements = bars
render :text => graph.to_s
end
</filter:code>
Actually… I just solved it. I guess I only need to make one bar instance. I was assuming I needed to create a bar for each month. So I pulled out the bar from the loop. Now it looks like this:
<filter:code attributes=lang="ruby">
# group by subject and use subject as the key
@payments.group_by(&:month).each do |month, payments|
month_total = 0
for payment in payments
month_total += payment.amount
end
monthly_totals << month_total
months << month
end
# 3d bar graph, could be any bar graph though
bar = Bar3d.new
# bar.set_key(month, 3)
bar.colour = "#459a89"
bar.values = monthly_totals
bars << bar
</filter:code>
{"y_axis": {"steps": 10, "max": 100, "min": 0}, "title": {"text": "Test Results"}, "elements": [{"text"
: "Anti-Money Laundering (WestLB)", "type": "bar_3d", "colour": "#459a89", "values": "10", "font-size"
: "4"}, {"text": "Conflicts of Interest", "type": "bar_3d", "colour": "#9a89f9", "values": "12", "font-size"
: "4"}, {"text": "Anti-Money Laundering Training", "type": "bar_3d", "colour": "#9a89f9", "values": "51"
, "font-size": "4"}, {"text": "Compliance Essentials", "type": "bar_3d", "colour": "#9a89f9", "values"
: "50", "font-size": "4"}], "bg_colour": "#ffffcc"}
I am trying to use this plugin, but the chart is not loading. I did everything i found in these places but nothing.
downloaded plugin in vendor/plugins
i copied the flash file to public and the javascript in public/javascripts/…
i put <% javascript_include_tag :all %> in the applications layout.
****Routes*****
map.with_options :controller => ‘stats’ do |t|
t.monthly_graph ‘/:id/monthly_graph’, :action => ‘monthly_graph’
t.yearly_graph ‘/:id/yearly_graph’, :action => ‘yearly_graph’
t.statistics ‘/statistics/:id’, :action => :show
end
*** stats controller ***
class StatsController < ApplicationController
def show
@post = Post.find(params[:id])
session[:time] ||= Time.now
@m_graph = open_flash_chart_object(550, 400, monthly_graph_path(@post.id))
end
def monthly_graph
@post = Post.find(params[:id])
session[:time] ||= Time.now
title = Title.new("Κόστος καταχώρησης ανά μήνα")
media_costs=[]
media_titles =[]
media_counts=[]
Media.find :all, :order => "title" do |media|
costs=[]
counts=[]
(1..days_in_month(session[:time].year, session[:time].month)).each do |day|
costs << media.trades.sum(:cost, :conditions => "created_at between ‘#{DateTime.new(session[:time].year, session[:time].month, day).to_formatted_s(:db)}’ AND ‘#{(DateTime.new(session[:time].year, session[:time].month, day) + 1.day).to_formatted_s(:db)}’")
counts << media.trades.count(:conditions => "created_at between ‘#{DateTime.new(session[:time].year, session[:time].month, day).to_formatted_s(:db)}’ AND ‘#{(DateTime.new(session[:time].year, session[:time].month, day) + 1.day).to_formatted_s(:db)}’")
end
media_costs << costs
media_counts << counts
media_title << media.title
end
lines =[]
media_costs.each_with_index do |x, index|
lines[index] = LineDot.new
lines[index].text = media_titles[index]
lines[index].width = 4
lines[index].colour = ‘#6363AC’
lines[index].values = x
end
y = YAxis.new
y.set_range(0,10000,5)
x_legend = XLegend.new("Ημέρες")
x_legend.set_style(‘{font-size: 20px; color: #778877}’)
y_legend = YLegend.new("Κόστος")
y_legend.set_style(‘{font-size: 20px; color: #770077}’)
tmp = []
(0..days_in_month(session[:time].year, session[:time].month)).to_a.each do |x|
tmp << x
end
chart = OpenFlashChart.new
chart.set_title(title)
chart.set_x_legend(x_legend)
chart.set_y_legend(y_legend)
chart.y_axis = y
lines.each {|x| chart.add_element(x)}
render :text => chart.to_s
end
*** VIEW show.html.erb ****
<%= @m_graph %>
I am using rails 2.2.2 but i tried older version but nothing the flash doesn’t render
Can anynone help?
@Pedros – Your implementation looks good. Any javascript errors. You need the <%= for your javascript_include_tag
<%= javascript_include_tag :all %>
Hi,
I tried this code but the chart doesn’t show up, I just have a "white" screen (no error message).
Here is my chart.to_s :
<filter:code attributes=lang="json">
{"y_axis": {"steps": 10, "max": 100, "min": 0}, "title": {"text": "Test Results"}, "elements": [{"text": "history", "type": "bar_3d", "colour": "#459a89", "font-size": 3, "values": [85, 78]}], "x_axis": {"labels": {"labels": ["Jack"]}}, "bg_colour": "#ffffcc"}
< /filter:code>
Is my chart.to_s valid ?
I also tried with what Ganesh said above => same result…
Does anyone has any idea?
For some strange reason the values of my y-axis labels doesn’t display. I don’t specifically set any labels for Y-axis like I do for X-axis (x_labels = XAxisLabels.new). Is this needed for Y-axis or should it display the values set by y.set_range(min, max, step)?
Thank You.
<filter:code attributes=lang="ruby">
def progression
# person id is
if params[:person_id]
person_id = params[:person_id].to_i
else
person_id = session[:loginuser].id
end
person = Person.find(person_id)
# data values (points)
quarter = Point.find(:all, :conditions => {:userlogin_id => person_id, :year => ’09’})
points = quarter.map{ |r| r.points }
# build line
title = Title.new("Your Progression")
line = Line.new
line.set_tooltip("<br>qty = #val#")
line.width = 2
line.colour = "#597AFF"
line.dot_size = 5
line.values = points
x_labels = XAxisLabels.new
x_labels.set_vertical()
labels = []
labels << XAxisLabel.new("08", ‘#BBBBE3’, 16, ‘horizontal’)
labels << XAxisLabel.new("Q1", ‘#BBBBE3’, 16, ‘horizontal’)
labels << XAxisLabel.new("Q2", ‘#BBBBE3’, 16, ‘horizontal’)
labels << XAxisLabel.new("Q3", ‘#BBBBE3’, 16, ‘horizontal’)
labels << XAxisLabel.new("Q4", ‘#BBBBE3’, 16, ‘horizontal’)
x_labels.labels = labels
x = XAxis.new
x.set_labels(x_labels)
x.colour = "#E8E8E8"
x.grid_colour = "#E8E8E8"
y = YAxis.new
y.colour = "#E8E8E8"
y.grid_colour = "#E8E8E8"
# This sets min, max and step for y-axis
min = data.min { |a, b| a <=> b }
max = data.max { |a, b| a <=> b }
step = (max-min)/10
min -= step unless min == 0
max += step
if max == min
max = 100
min =0
step = 20
end
y.set_range(min, max, step)
x_legend = XLegend.new("Quarters")
x_legend.set_style(‘{font-size: 22px; color: #E3BBD8}’)
y_legend = YLegend.new("Points")
y_legend.set_style(‘{font-size: 22px; color:#E3BBD8}’)
chart = OpenFlashChart.new
chart.bg_colour = ‘#FFFFFF’
chart.title = title
chart.set_x_legend(x_legend)
chart.set_y_legend(y_legend)
chart.x_axis = x
chart.y_axis = y
chart.add_element(line)
render :text => chart.to_s
end
</filter:code>
@Dalf – just for comparison, this is my chart.to_s:
<filter:code>
{"y_axis": {"steps": 10, "max": 100, "min": 0}, "title": {"text": "Test Results"}, "elements": [{"text": "History", "type": "bar_3d", "colour": "#459a89", "values": [97, 92], "font-size": 3}, {"text": "Science", "type": "bar_3d", "colour": "#9a89f9", "values": [85, 57], "font-size": 3}], "x_axis": {"labels": {"labels": ["Jack", "Jill"]}}, "bg_colour": "#ffffcc"}
</filter:code>
Other than your missing label and bar, they look the same to me. Do you have javascript enabled? Do you have the swfobject.js loaded? What is the generated HTML for the page? Did you render :text => chart.to_s? If you think of anything let me know.
@WIehann – Maybe your color choice is too faint compared to your background color? What swf file are you using? The bundled one or the new one. Everything looks fine from here. What is the JSON output (chart.to_s)? What is the generated HTML?
@Pedros I had a similar problem when i used a rails helper for the url.
Assume that you have something like this in your controller action:
<filter:code attributes=lang="ruby">
# results_bar_chart_path(@result) returns /result/1/bar_chart
@chart = ofc2(600,300,bar_chart_path(@result))
< /filter:code>
The problem is that ofc2 assumes by default to be "/" the base directoy and appends the url to it, which will result in //result/1/bar_chart
To fix this, just pass an empty string as fourth param like this:
<filter:code attributes=lang="ruby">
# in your controller action
# results_bar_chart_path(@result) returns /result/1/bar_chart
@chart = ofc2(600,300,bar_chart_path(@result),"")
< /filter:code>
You may have already fixed the problem, but I hope that it will help anyone who runs into this problem.
I’ve got to excuse me because i’ve mistaken the plugin that is discussed here with this one:
http://openflashchart2.sentor.pl/
Sorry for that.
Hi, thanks for the great charts.
I’m having trouble with the database example. I’ve inserted the results into a Mysql database and have a results migration.
At http://localhost:3000/test_it/ I get the following;
Open Flash Chart
IO ERROR
Loading test data
Error #2032
This is the URL that I tried to open:/test_it/graph_code
And at http://localhost:3000/test_it/graph_code I get;
uninitialized constant TestItController::Result
RAILS_ROOT: /home/andrew/Projects/ofc2_test_app
Any help is greatly appreciated. I’ve been working through it for a few days now.
Andrew
I am having trouble displaying data from a database. I am trying to loop through my "Asset_Types table and display the count of the nmber of assets that belong to each asset_type.
Can someone please point me in the right direction?
<filter:code attributes=lang="ruby">
def graphcode
# we will have bars for each student subject combo
bars = []
# random colors to chose from
colours = ["#459a89", "#9a89f9"]
# the results
results = AssetType.all :order => "name"
results.each do |at|
bar = Bar3d.new
bar.set_key(at.name, 2) #the number is the fone size
bar.values = at.assets.count
bars << bar
end
# some title
title = Title.new("Assets by Type")
# labels along the x axis, just hard code for now, but you would want to dynamically do this
x_axis = XAxis.new
x_axis.labels = ["Jack", "Jill"]
# go to 100% since we are dealing with test results
y_axis = YAxis.new
y_axis.set_range(0, 500, 10)
# setup the graph
graph = OpenFlashChart.new
graph.bg_colour = ‘#ffffcc’
graph.title = title
graph.x_axis = x_axis
graph.y_axis = y_axis
graph.elements = bars
render :text => graph.to_s
end
< /filter:code>
The relevant json that is being generated is:
{"y_axis": {"steps": 10, "max": 500, "min": 0}, "title": {"text": "Assets by Type"}, "elements": [{"text": "Adviser Central", "type": "bar_3d", "values": 2, "font-size": 2}, {"text": "BES Server", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Backup", "type": "bar_3d", "values": 1, "font-size": 2}, {"text": "Backup Tape Drive", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Blackberry", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Camera", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "DHCP", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Database Server", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Desktop", "type": "bar_3d", "values": 136, "font-size": 2}, {"text": "Domain Name Hosting", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Domain Name Registration", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Email Server", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Email Server", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Fax", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Firewall", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Internal Domain", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Internet Service Provider", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Label Printer", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Laptop", "type": "bar_3d", "values": 14, "font-size": 2}, {"text": "Leap", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Linux", "type": "bar_3d", "values": 6, "font-size": 2}, {"text": "MDF", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "ManagePoint", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Other", "type": "bar_3d", "values": 7, "font-size": 2}, {"text": "Other", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Printer", "type": "bar_3d", "values": 24, "font-size": 2}, {"text": "Router/Modem", "type": "bar_3d", "values": 16, "font-size": 2}, {"text": "Scanner", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "StorageCraft", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Switch", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Trendmicro Antivirus", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Trixbox", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "UPS", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Web Hosting", "type": "bar_3d", "values": 0, "font-size": 2}, {"text": "Website", "type": "bar_3d", "values": 6, "font-size": 2}, {"text": "Windows", "type": "bar_3d", "values": 29, "font-size": 2}, {"text": "iPhone", "type": "bar_3d", "values": 0, "font-size": 2}], "x_axis": {"labels": {"labels": ["Jack", "Jill"]}}, "bg_colour": "#ffffcc"}
Hi. I wan wondering how I could send some parameters to the graph_code method. For example, if I wanted to send a parameter called ‘year’, I could send it this way:
@graph = open_flash_chart_object(600, 300,”/test_it/graph_code?year=2008″)
But is there a better way to do this?
Works locally, but failed on http://strongsummer.heroku.com/test_it
===================================
This app looks good!
I implemented 3 graphs ( first graph, pie chart and test result) on 1 page.
Runs fine locally on rails. But test result gave an error deployed on heroku (The other 2 graph looks fine.) here :- http://strongsummer.heroku.com/test_it
Error message :
Open Flash Chart
IO ERROR
Loading test data
Error #2032
This is the URL that I tried to open:/test_it/graph_three
I’d suspect this may be a flash link to or database error. I changed a few parameters in database.yml but to no avail. (Now i am just sticking to what i know is the best working config here from previous.) :-
development:
adapter: sqlite3
database: db/development.sqlite3
pool: 5
timeout: 5000
test:
adapter: sqlite3
database: db/test.sqlite3
pool: 5
timeout: 5000
production:
adapter: postgresql
database: db/development.sqlite3
pool: 5
timeout: 5000
Basically, i did a
> rake db:setup
> rake db:migrate
> rake db:seed
and the git and heroku commands.
Any advice would be good!
Thanks!
These changes have the same result.
development:
adapter: sqlite3
database: db/allresults.db
pool: 5
timeout: 5000
production:
adapter: postgresql
database: db/allresults.db
pool: 5
timeout: 5000
What’s the error in the log?
Thanks for asking charlie!
The problem is solved in 2 steps :-
> gem install taps
> heroku db:push (As advised by heroku staff)
http://strongsummer.heroku.com/test_it is working fine now 🙂
Hi,
how are you setting the tooltip like that (with the xlabel and value)? Is it automatic? I’m just making a simple bar chart and #x_label# is not working, so I was wondering if there is a workaround. Thanks
Learnnig a ton from these neat articles.