Using FusionCharts with JSP > Creating Drill-down charts
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.
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 this 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 BasicDBExample.jsp. We basically need to add the link attribute for each <set> element. We create a new page Default.jsp from the previous page in DBExample folder with the following code changes:
<%@ include file="../Includes/DBConn.jsp"%>
<%
/*We've imported FusionChartsHelper, which contains function
to help us encode the URL.*/
%>
<%@ page import="com.infosoftglobal.fusioncharts.FusionChartsHelper"%>
<%@ page import="java.sql.Statement"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="java.sql.Date"%>
<HTML>
<HEAD>
<TITLE>FusionCharts Free - Database and Drill-Down Example</TITLE>
<%
/*You need to include the following JS file, if you intend to embed the chart using JavaScript.
Embedding using JavaScripts avoids the "Click to Activate..." issue in Internet Explorer
When you make your own charts, make sure that the path to this JS file is correct. Else, you would get JavaScript errors.
*/
%>
<SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>
<style type="text/css">
<!--
body {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.text{
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
-->
</style>
</HEAD>
<BODY>
<CENTER>
<h2><a href="http://www.fusioncharts.com" target="_blank">FusionCharts Free</a> Database and Drill-Down Example</h2>
<h4>Click on any pie slice to see detailed data.</h4>
<%
/*
In this example, we show how to connect FusionCharts to a database.
For the sake of ease, we've used MySQL Database.
It just contains two tables, which are linked to each
other.
*/
//Database Objects - Initialization
Statement st1=null,st2=null;
ResultSet rs1=null,rs2=null;
String strQuery="";
//strXML will be used to store the entire XML document generated
String strXML="";
//Generate the chart element
strXML = "<graph caption='Factory Output report' subCaption='By Quantity' decimalPrecision='0' showNames='1' numberSuffix=' Units'
pieSliceDepth='30' formatNumberScale='0'>";
//Query to retrieve data
strQuery = "select * from Factory_Master";
st1=oConn.createStatement();
rs1=st1.executeQuery(strQuery);
String factoryId=null;
String factoryName=null;
String totalOutput="";
String strDataURL="";
while(rs1.next()) {
factoryId=rs1.getString("FactoryId");
factoryName=rs1.getString("FactoryName");
strQuery = "select sum(Quantity) as TotOutput from Factory_Output where FactoryId=" + factoryId;
st2=oConn.createStatement();
rs2 = st2.executeQuery(strQuery);
if(rs2.next()){
totalOutput=rs2.getString("TotOutput");
}
FusionChartsHelper chartsHelper = new FusionChartsHelper();
// Encoding the URL since it has a parameter
strDataURL = chartsHelper.encodeDataURL("Detailed.jsp?FactoryId="+factoryId,"false",response);
//Generate <set name='..' value='..'/>
strXML += "<set name='" + factoryName + "' value='" +totalOutput+ "' link='"+strDataURL+"' />";
//Close recordset
rs2=null;
st2=null;
}
//Finally, close <graph> element
strXML += "</graph>";
//close resultset,statement,connection
try {
if(null!=rs1){
rs1.close();
rs1=null;
}
}catch(java.sql.SQLException e){
System.out.println("Could not close the resultset");
}
try {
if(null!=st1) {
st1.close();
st1=null;
}
}catch(java.sql.SQLException e){
System.out.println("Could not close the statement");
}
try {
if(null!=oConn) {
oConn.close();
oConn=null;
}
}catch(java.sql.SQLException e){
System.out.println("Could not close the connection");
}
//Create the chart - Pie 3D Chart with data from strXML
%>
<jsp:include page="../Includes/FusionChartsRenderer.jsp" flush="true">
<jsp:param name="chartSWF" value="../../FusionCharts/FCF_Pie3D.swf" />
<jsp:param name="strURL" value="" />
<jsp:param name="strXML" value="<%=strXML %>" />
<jsp:param name="chartId" value="FactorySum" />
<jsp:param name="chartWidth" value="650" />
<jsp:param name="chartHeight" value="450" />
<jsp:param name="debugMode" value="false" />
<jsp:param name="registerWithJS" value="false" />
</jsp:include>
<BR>
<BR>
<a href='../NoChart.html' target="_blank">Unable to see the chart above?</a><BR>
<H5><a href='../default.htm'>« Back to list of examples</a></h5>
</CENTER>
</BODY>
</HTML>
As you can see in the code above, we're doing the following:
- Include FusionCharts.js JavaScript class to enable easy embedding of FusionCharts.
- We then include DBConn.jsp, which gives the connection to our database.
- Thereafter, we generate the XML data document by iterating through the resultset. We store the XML data in strXML variable. To each <set> element, we add the link attribute, which points to Detailed.jsp - the page that contains the chart to show details. We pass the factory id of the respective factory by appending it to the link.
- Finally, we render the chart by including FusionChartsRenderer and passing strXML as parameter to it.
Let's now shift our attention to Detailed.jsp page.
Creating the detailed data chart page
The page Detailed.jsp contains the following code:
<%@ include file="../Includes/DBConn.jsp"%>
<%@ page import="com.infosoftglobal.fusioncharts.FusionChartsHelper"%>
<%@ page import="java.sql.Statement"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="java.text.SimpleDateFormat"%>
<HTML>
<HEAD>
<TITLE>FusionCharts Free - Database and Drill-Down Example</TITLE>
<%
/*You need to include the following JS file, if you intend to embed the chart using JavaScript.
Embedding using JavaScripts avoids the "Click to Activate..." issue in Internet Explorer
When you make your own charts, make sure that the path to this JS file is correct. Else, you would get JavaScript errors.
*/
%>
<SCRIPT LANGUAGE="Javascript" SRC="../../FusionCharts/FusionCharts.js"></SCRIPT>
<style type="text/css">
<!--
body {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.text{
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
-->
</style>
</HEAD>
<BODY>
<CENTER>
<h2><a href="http://www.fusioncharts.com" target="_blank">FusionCharts Free</a> Database and Drill-Down Example</h2>
<h4>Detailed report for the factory</h4>
<%
/*This page is invoked from Default.jsp. When the user clicks on a pie
slice in Default.jsp, the factory Id is passed to this page. We need
to get that factory id, get the information from database and then show
a detailed chart.
*/
//First, get the factory Id
String factoryId=null;
//Request the factory Id from parameters
factoryId = request.getParameter("FactoryId");
String chartCode="";
if(null!=factoryId){
// we will use the FusionChartsHelper class for colors
FusionChartsHelper colorHelper= new FusionChartsHelper();
ResultSet rs=null;
String strQuery;
Statement st=null;
java.sql.Date date=null;
java.util.Date uDate=null;
String uDateStr="";
String quantity="";
String strXML="";
//Generate the chart element string
strXML = "<graph caption='Factory " +factoryId +" Output ' subcaption='(In Units)' xAxisName='Date' showValues='1'
decimalPrecision='0'>";
//Now, we get the data for that factory
strQuery = "select * from Factory_Output where FactoryId=" +factoryId;
st=oConn.createStatement();
rs = st.executeQuery(strQuery);
while(rs.next()){
date=rs.getDate("DatePro");
quantity=rs.getString("Quantity");
if(date!=null) {
uDate=new java.util.Date(date.getTime());
// Format the date so that the displayed date is easy to read
SimpleDateFormat sdf=new SimpleDateFormat("d/M");
uDateStr=sdf.format(uDate);
}
strXML += "<set name='" +uDateStr+"' value='" +quantity+"' color='" + colorHelper.getFCColor() + "'/>";
}
//Close <graph> element
strXML +="</graph>";
//close resultset,statement,connection
try {
if(null!=rs){
rs.close();
rs=null;
}
}catch(java.sql.SQLException e){
//do something
System.out.println("Could not close the resultset");
}
try {
if(null!=st) {
st.close();
st=null;
}
}catch(java.sql.SQLException e){
//do something
System.out.println("Could not close the statement");
}
try {
if(null!=oConn) {
oConn.close();
oConn=null;
}
}catch(java.sql.SQLException e){
//do something
System.out.println("Could not close the connection");
}
//Create the chart - Column 2D Chart with data from strXML
%>
<jsp:include page="../Includes/FusionChartsRenderer.jsp" flush="true">
<jsp:param name="chartSWF" value="../../FusionCharts/FCF_Column2D.swf" />
<jsp:param name="strURL" value="" />
<jsp:param name="strXML" value="<%=strXML%>" />
<jsp:param name="chartId" value="FactoryDetailed" />
<jsp:param name="chartWidth" value="600" />
<jsp:param name="chartHeight" value="300" />
<jsp:param name="debugMode" value="false" />
<jsp:param name="registerWithJS" value="false" />
</jsp:include>
<% }
%>
<BR>
<a href='Default.jsp?animate=0'>Back to Summary</a> <BR>
<BR>
<a href='../NoChart.html' target="_blank">Unable to see the chart above?</a><BR>
<H5><a href='../default.htm'>« Back to list of examples</a></h5>
</CENTER>
</BODY>
</HTML>
In this page, we're:
- Including FusionCharts.js JavaScript class, to enable easy embedding of FusionCharts.
- Requesting the factory id for which we've to show detailed data. This data was sent to us as querystring, as a part of pie chart link.
- We get the requisite data for this factory from database and then convert it into XML using string concatenation.
- Finally, we render a Column 2D chart to show detailed data, by including FusionChartsRenderer.jsp.
When you now run the app, you'll see the detailed page as under:
