Using a database to populate an Open Flash Chart graph

July 25th, 2008 by charlie

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:


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:


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:

1234	<script type="text/javascript" src="/javascripts/swfobject.js"></script><%= @graph %>

Posted by charlie Filed in development, Open Flash Chart Graphs, projects, rails, ruby, tutorials

17 Responses to “Using a database to populate an Open Flash Chart graph”

Cecci Says:
July 28th, 2008 at 09:38 AM 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
charlie Says:
July 28th, 2008 at 10:17 AM
@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.
Cecci Says:
July 30th, 2008 at 07:41 AM 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
charlie Says:
July 30th, 2008 at 10:05 AM
@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
Nathan Leavitt Says:
August 28th, 2008 at 05:01 PM 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>
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>

Nathan Leavitt Says:
August 28th, 2008 at 05:03 PM Oops...here it is:


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

Nathan Leavitt Says:
August 28th, 2008 at 06:34 PM 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:


# 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

Ganesh Says:
December 1st, 2008 at 01:11 AM {"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"}
Petros Says:
December 2nd, 2008 at 06:31 AM 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?
charlie Says:
December 3rd, 2008 at 09:37 PM
@Pedros - Your implementation looks good. Any javascript errors. You need the <%= for your javascript_include_tag

<%= javascript_include_tag :all %>
Dalf Says:
February 17th, 2009 at 10:43 PM 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>
{"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?

WIehann Says:
April 14th, 2009 at 02:00 AM 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.


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

charlie Says:
April 15th, 2009 at 09:25 AM
@Dalf - just for comparison, this is my chart.to_s:


{"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"}

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.

charlie Says:
April 15th, 2009 at 09:28 AM
@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?
Enno Says:
May 4th, 2009 at 06:59 AM @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>
# 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>
# 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.
Enno Says:
May 5th, 2009 at 04:49 AM 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.
Andrew Says:
June 17th, 2009 at 09:09 PM 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