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

10 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 %>