In our previous example, we had used FusionCharts to plot a chart using data stored in database. We'll now extend that example itself to create a drill-down chart which can show more information.
Before you go further with this page, we recommend you to please see the previous sections like "Basic Examples", Creating Data from Array" as we start off from concepts explained in those pages.
If you recall from previous example, we were showing the sum of factory output in a pie chart as under:
In this example, we'll extend the previous example, so that when a user clicks on a pie slice for a factory, he can drill down to see date wise production for that factory.
Setting up the pie chart for Link
To set up the pie chart to enable links for drill-down involves just minor tweaking of our previous example. We basically need to add the link attribute for each <set> element.
The code examples contained in this page are contained in
Controller : Download Package > Code > RoR > SampleApp > app > controllers > fusioncharts > db_drilldown_controller.rb.
Views : Download Package > Code > RoR > SampleApp > app > views > fusioncharts > db_drilldown folder.
Controller: Fusioncharts::DbDrilldownController
Action: default
#In this function, we obtain total output of quantities and name of each factory from the database and plot them on a pie-chart.
#It stores URL to the "detailed" function in a variable passing FactoryId as parameter to the function which
#returns quantity produced and date of production of the factory that are obtained from database and which are plotted in a chart.
#This action retrieves the values from the database and constructs an array
#to hold, factory name, corresponding total output quantity and URL to the action which will generate the detailed chart.
#The view for this action default.html.erb will use the array values to construct the
#xml for this chart. To build the xml, the view takes help from the builder file (default_factories_quantity.builder)
def default
headers["content-type"]="text/html";
str_data_url = '';
#Get data from factory masters table
factory_masters = FactoryMaster.find(:all)
@factory_data = []
#Iterate through each record
factory_masters.each do |factory_master|
total = 0.0
#Get factoryid and factoryname
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
# Escape the URL
str_data_url = CGI.escape("/Fusioncharts/db_drilldown/detailed?FactoryId="+factory_id.to_s)
# Put the hash of values in the array
@factory_data<<{:str_data_url=>str_data_url, :factory_name=>factory_name,:factory_output=>total}
end
end
View: default.html.erb
<% @page_title="FusionCharts Free - Database and Drill-Down Example " %>
<% @page_heading="Database and Drill-Down Example" %>
<% @page_subheading="Click on any pie slice to see detailed data." %>
<%
# The xml is obtained as a string from builder template.
str_xml = render "fusioncharts/db_drilldown/default_factories_quantity",{:factory_data => @factory_data}
#Create the chart - Pie 3D Chart with data from strXML
render_chart '/FusionCharts/FCF_Pie3D.swf','', str_xml, 'FactorySum', 650, 450, false, false do-%>
<% end-%>
The factory data is obtained and stored in a variable @factory_data.When compared to the previous example, the default action in this example, creates a link to the detailed action with FactoryId as parameter. This link is added as the third element of the hash for each factory. The URL that is assigned to str_data_url is escaped using the CGI.escape function.
The view is similar to the basic_dbexample.html.erb seen in the previous page. Here the builder template used is default_factories_quantity.builder and we pass the factory_data array parameter to the builder. Finally, the chart is rendered by calling the render_chart function.
The builder template used is as follows:
#Creates xml with values for Factory Output
#along with their names and a link to detailed action for each factory.
#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", "factory_output" and "str_data_url"
xml = Builder::XmlMarkup.new
xml.graph(:caption=>'Factory Output report', :subCaption=>'By Quantity', :pieSliceDepth=>'30',:decimalPrecision=>'0', :showNames=>'1', :formatNumberScale=>'0', :numberSuffix=>' Units') do
for item in factory_data
xml.set(:name=>item[:factory_name],:value=>item[:factory_output],:link=>item[:str_data_url])
end
end
Iterate through the array factory_data and use the values present in the hash. We add an attribute called link to the <set> tag, with value as the hash element str_data_url.
On clicking a pie slice, what happens? It goes to the detailed action of the Controller. Let us now generate the code for the chart that will be shown on clicking a pie slice.
Creating the detailed data chart page
Controller: Fusioncharts::DbExampleController
Action: detailed
#This action retrieves the quantity and date of production of
#the factory identified by the request parameter expected "FactoryId"
#The view for this action is detailed.html.erb and it uses the builder file
#factory_details.builder to build the xml for the column chart.
def detailed
headers["content-type"]="text/html";
@factory_id = params[:FactoryId]
@factory_data = []
factory_master = FactoryMaster. find(@factory_id)
factory_master.factory_output_quantities.each do |factory_output|
date_of_production = factory_output.date_pro
# Formats the date to dd/mm
#formatted_date = date_of_production.strftime('%d/%m')
# Formats the date to dd/mm without leading zeroes
formatted_date = format_date_remove_zeroes(date_of_production)
quantity_number = factory_output.quantity
@factory_data<<{:date_of_production=>formatted_date,:quantity_number=>quantity_number}
end
end
View: detailed.html.erb
<% @page_title=" FusionCharts Free- Database and Drill-Down Example " %>
<% @page_heading="Database and Drill-Down Example" %>
<% @page_subheading="Detailed report for the factory" %>
<%
#This page is invoked from default action in controller. When the user clicks on a pie
#slice rendered by default.html.erb, the factoryId is passed as a parameter to detailed function
#in the controller. We need to get that factory id, get the information from database and then show
#a detailed chart.
# The xml is obtained as a string from builder template.
str_xml = render "fusioncharts/db_drilldown/factory_details", {:factory_data=>@factory_data,:factory_id=>@factory_id}
#Create the chart - Column 2D Chart with data from strXML
render_chart '/FusionCharts/FCF_Column2D.swf', '', str_xml, 'FactoryDetailed', 600, 300, false, false do -%>
<% end-%>
<BR>
<a href='default'>Back to Summary</a>
The detailed action does the following:
- Gets the FactoryId from the request and stores ina variable @factory_id.
- Performs a find with the Model FactoryMaster for the id obtained from the previous step
- Iterates through the factory_output_quantities of this factory (Remember the hasMany relationship defined in the Model) and creates a hash with date of production (date_pro) and quantity_number (quantity_number ) as elements of the hash. The date_of_production is formatted to %d/%m format before putting in the hash. For this the function format_date_remove_zeroes from the application.rb is used (explained below)
- This hash is put into the array @factory_data.
This is the code for formatting date to dd/mm and removing the leading zeroes.
application.rb
# Formats the date to dd/mm without leading zeroes
def format_date_remove_zeroes(date_to_format)
date_num= date_to_format.strftime('%d').to_i
month_num = date_to_format.strftime('%m').to_i
formatted_date=date_num.to_s +"/"+month_num.to_s
end
The view detailed.html.erb calls the render function with the path to the builder factory_details, array factory_data and factory id as parameter. The resultant xml is assigned to the variable str_xml. Finally, it calls the render_chart function to chart a Column2D chart and passes the xml to it as dataXML parameter. What does the builder template factory_details do? Here is the code:
#Creates xml with values for date of production and quantity for a particular factory
#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 "date_of_production" and "quantity_number"
xml = Builder::XmlMarkup.new
xml.graph(:palette=>'2', :caption=>'Factory' + factory_id.to_s + ' Output ', :subcaption=>'(In Units)', :xAxisName=>'Date', :showValues=>'1', :decimalPrecision=>'0') do
for item in factory_data
xml.set(:name=>item[:date_of_production],:value=>item[:quantity_number],:color=>''+get_FC_color)
end
end
This is a simple xml with the outer <graph> element and <set> elements within it. The <set> element has label, value and color attributes. Date of production is set as the label and quantity is set as the value. These values are obtained from the array of hashes factory_data received as parameter.
Now, when you click on a pie slice, the page opens the following chart with details of the selected factory:
