In this section, we'll show you how to use FusionCharts and Ruby to plot charts from data contained in a database. We'll create a pie chart to show "Factory Output Quantity by Factory" using dataXML method.

For the sake of ease, we'll use the MySQL Database. You can, however, use any database with FusionCharts including SQLLite, MS SQL, Oracle, Access etc. Database configuration will be available here Download Package >> RoR >> config >> database.yml. In the production version, we have used database named as factorydb.

Before you go further with this page, we recommend you to please see the previous section "Basic Examples" as we start off from concepts explained in that page.

All code discussed here is present in
Controller : Download Package > Code > RoR > app > controllers > fusioncharts > db_example_controller.rb.
Rhtml : Download Package > Code > RoR > app > views > fusioncharts > db_example folder.

Database Structure

Let's quickly have a look at the database structure.

The database contains just 2 tables:

  1. factory_masters: To store the name and id of each factory
  2. factory_output_quantities: To store the number of units produced by each factory for a given date.

Note that the name of the table is pluralized, as per Ruby conventions. For demonstration, we've fed some sample data in the database.

Database Configuration

  1. All the database configuration is present in the file app > config > database.yml. Here we need to specify the database name, user name and password to access the database. We have used the MySQL database for our examples. We assume that you have created the database with the name factorydb, either by using the rake db:create utility or by using MySQL create database command.
  2. Once this is done, we need to create the required tables. The required sql script "create_tables.sql" is present in the Download Package > Code > RoR >db folder. You could run this script in your mysql and create the tables and insert the sample data. The other way of creating these tables, is to run rake db:migrate for the migration scripts "001_create_factory_masters.rb" and "002_create_factory_output_quantities.rb" present in the folder Download Package > Code > RoR >db > migrate. Note that these scripts will not create foreign key relationships. You would have to manually alter the table to create these relationships, if you think neccessary. To insert the sample data please run the sql script "insert_sample_data.sql" present in the Download Package > Code > RoR >db folder.

Let's now shift our attention to the code that will interact with the database, fetch data and then render a chart.

Database Example Using dataXML method

Controller: Fusioncharts::DbExampleController
Action: basic_dbexample
#There are two examples in this controller.
#*Pie-chart for total ouput quantities of each factory by getting data from database and using dataXML method
#*Pie-chart for total ouput quantities of each factory and a link to another chart
#which gives detailed information for selected factory
#All the views related to this controller will use the "common" layout.
#As per Ruby On Rails conventions, we have the corresponding views
#with the same name as the function name in the controller.

class Fusioncharts::DbExampleController < ApplicationController
#This is the layout which all functions in this controller make use of.
layout "common"

#This action retrieves the values from the database and constructs an array
#to hold, factory name and corresponding total output quantity.
#The view for this action basic_dbexample will use the array values to construct the
#xml for this chart. To build the xml, the view takes help from the builder file #(basic_factories_quantity.builder)

def basic_dbexample
    headers["content-type"]="text/html";
    @factory_data = []
    #Get data from factory masters table
    factory_masters = FactoryMaster.find(:all)
    factory_masters.each do |factory_master|
        total = 0.0
        factory_id = factory_master.id
        factory_name = factory_master.name
        factory_master.factory_output_quantities.each do |factory_output|
            total = total + factory_output.quantity
        end
        # Push the hash of values into the array
        @factory_data<<{:factory_name=>factory_name,:factory_output=>total}
    end
end

View:
<% @page_title="FusionCharts - Database Example" %>
<% @page_heading="FusionCharts Database Example Using dataXML method" %>
<% @page_subheading="Click on any pie slice to slice it out. Or, right click to enable rotation mode." %>
<%
#In this example, we show how to connect FusionCharts to a database.
#For the sake of ease, we've used a database which contains two tables, which are linked to each
#other.

# The xml is obtained as a string from builder template.
str_xml = render :file=>"fusioncharts/db_example/basic_factories_quantity",:locals=>{:factory_data => @factory_data}

#Create the chart - Pie 3D Chart with data from strXML
render_chart '/FusionCharts/Pie3D.swf', '', str_xml, 'FactorySum', 600, 300, false, false do-%> <% end-%>

Now, here we need to understand some lines of code.

  1. The basic_dbexample action of the controller, first preforms a find on FactoryMaster model, getting all the values.
  2. For each factory in the record set obtained in the previous step, we sum up the total output quantity for that factory. This is done by iterating through the factory_output_quantities of each factory_master. Note that FactoryMaster and FactoryOutputQuantity are related to each other as shown:

    #Model class to store data of factory id and name
    #As per Ruby On Rails conventions, we have the corresponding table
    #factory_masters in the database

    class Fusioncharts::FactoryMaster < ActiveRecord::Base
        has_many :factory_output_quantities,
                 :order => 'date_pro asc'
    end

    #Model class to store output data of factories
    #As per Ruby On Rails conventions, we have the corresponding table
    #factory_output_quantities in the database

    class Fusioncharts::FactoryOutputQuantity < ActiveRecord::Base
        belongs_to :factory_master
    end

    Based on this relation, Ruby On Rails is able to get the data for FactoryOutputQuantity when a find is performed on FactoryMaster.

  3. An array @factory_data is used to store the values. Each element of this array contains a hash with factory_name and factory_output. The total obtained for this factory in the previous step is used as value for the factory_output element.
  4. The view basic_dbexample.html.erb passes the @factory_data array created in the previous step to the builder file "basic_factories_quantity", as parameter. The xml obtained is assigned to str_xml variable.
  5. Finally, render_chart function is called by setting the str_xml as xml parameter.

Let us now take a look at the builder file.

Builder - basic_factories_quantity.builder
#Creates xml with values for Factory Output
#along with their names.
#The values required for building the xml is obtained as parameter factory_data
#It expects an array in which each element as
#a hash with values for "factory_name" and "factory_output"

xml = Builder::XmlMarkup.new
xml.chart(:caption=>'Factory Output report', :subCaption=>'By Quantity', :pieSliceDepth=>'30', :showBorder=>'1', :formatNumberScale=>'0', :numberSuffix=>'Units') do
    for item in factory_data
        xml.set(:label=>item[:factory_name],:value=>item[:factory_output])
    end
end

The builder builds chart element with several attributes, then iterates through the factory_data array (received as parameter from the view) to create the <set> element with value for label as factory name and value for value attribute as total factory output.


When you now run the code, you'll see a beautiful pie chart as under: