SVG, XSLT and JSP/JSTL: Generating Digital Gauge or Speedometer Chart based on dynamic data

4

SVG in combination with XML/XSLT is a tremendously powerful architecture to produce dynamic charts based on XML data – that can easily be generated from databases such as Oracle or MySQL. Using SVG, we can create many different types of chart-types. This article discusses specifically the so called speed-o-meter or digital gauge. Earlier I reported on the creation of Line Charts using a generic SVG generating XSLT-stylesheet. This post is a sequel to two previous posts: Generating SVG Graphics in JSPs using JSTL & XSL(T) – from MySQL to Bar Chart and Pie Chart and XSL Transforming XML generated from SQL results using JSTL tags in JSP pages – putting JSTL to the test. In these posts, we talk in detail about the technology and architecture for extracting data from relational databases using JSP/JSTL, turning it into XML documents and transforming these documents using XSLT through the JSTL XML-tags. These articles also describes how to enable SVG in your browser.
Example of a Digital Gauge (or Speedometer)

Approach to generating SVG based Graphs

First I create SVG documents for the type of graph I want to generate. This SVG document is completely hardcoded, it is just a way to draw one instance of the type of graph I am working on. I have used different resources found on the internet for constructing this SVG image. One very helpful resource is Examples of SVG based Dial Gauges (Speedometers); many more SVG examples: Many Sample Charts (barchart, barley, birdlog, cellular, fuelcalc, piecharts, polar, min-max, 3D charts and many more! (© Copyright Causeway Graphical Systems Ltd 2004).

In a second step I create an XSLT stylesheet that will produce the SVG document that I crafted in the first step. This XSLT initially is nothing more than one template matching any XML Document’s root element and transforming it into one large chunk of SVG:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:xlink="http://www.w3.org/2000/xlink/namespace/"
                version="1.0">
  <xsl:output method="image/svg+xml" omit-xml-declaration="yes"/>
  <xsl:template match="/">
    <svg width="1200" height="1200">
       ... rest of the SVG document
    </svg>
  </xsl:template>
</xsl:stylesheet>

Along with this rudimentary XSLT sheet, I also setup a JSP that – using JSTL XML tags – will import an XML document with the data to eventually display in the graph and transform it to the SVG Document. I decide to adopt the following procedure for producing SVG from data in a database:
From database to XML ; import into JSP that uses two XSLT stylesheets to transform from the XML data into SVG
This means that the ‘clean’ XML as extracted from the database (described in a previous post XSL Transforming XML generated from SQL results using JSTL tags in JSP pages – putting JSTL to the test) is imported into a JSP that in turn transforms it twice. First a specific XSLT stylesheet is used to transform the data-XML to a generic format required by the second stylesheet. This second stylesheet is a generic stylesheet that will transform a source document in proper format to a linegraph or piechart or barchart – for each type of graph we will need such a generic stylesheet. With this line up, we can easily create many graphs: we only need to create XML documents containing data, transforming them into a generic data document and can then apply this generic graph-type stylesheet.

The JSP that drives all of this and produces an SVG document – DeptSalSpeedometerSVG.jsp – is the following:

<%@ page contentType="image/svg+xml" %>
<%@ taglib uri="/WEB-INF/x.tld" prefix="x" %>
<%@ taglib uri="/WEB-INF/c.tld" prefix="c" %>
<%@ taglib uri="/WEB-INF/sql.tld" prefix="sql" %>
<c:import url="DeptSalsXML.jsp" varReader="empdoc" >
  <x:parse var="empxmldoc" xml="${empdoc}" scope="page" />
</c:import>
  <c:import var="xslt2" url="PrepareEmpXMLforSpeedometerChart.xsl" />
  <x:transform xml="${empxmldoc}" xslt="${xslt2}" var="empxmldoc2" scope="page"/>
<c:import var="xslt" url="buildSVGSpeedometer.xsl" scope="page"/>
<x:transform xml="${empxmldoc2}" xslt="${xslt}" />

Extract Data from Database into XML Document

DeptSalsXML.jsp is the JSP that produces an XML document with the data extracted from the database.

<%@ page contentType="text/xml" %>
<%@ taglib uri="/WEB-INF/c.tld" prefix="c" %>
<%@ taglib uri="/WEB-INF/sql.tld" prefix="sql" %>
    <sql:query var="deptdata" sql="select dname, sum(sal) salsum  from emp , dept where emp.deptno = dept.deptno group by dname">
    </sql:query>
<depts>
  <c:forEach var="row" items="${deptdata.rowsByIndex}">
<dept><dname><c:out value="${row[0]}"/></dname><salsum><c:out value="${row[1]}"/></salsum></dept>
</c:forEach>
</depts>

The resulting XML Document can be downloaded:here

Transform data into generic format fit for Graph-Generation

The first XSLT stylesheet to apply – PrepareEmpXMLforSpeedometerChart.xsl – transforms this data into the format required by the second stylesheet – buildSVGSpeedometer.xsl -, the generic stylesheet that produces Digital Gauge Charts Note that PrepareEmpXMLforSpeedometerChart.xsl is tuned exactly to the structure of the XML document that contains the data from the database on the one hand, the format required by the second stylesheet and the specific requirements for the graph to produce (titles, colors, markers, annotations etc.).

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml"/>
  <xsl:template match="/depts">
    <xsl:element name="graphData">
      <xsl:element name="sets">
      <xsl:for-each select="dept"> <!- for every department we will create a set element; this set element is transformed into a single dial  ->
        <xsl:element name="set">
          <xsl:attribute name="title">Department <xsl:value-of select="dname"/></xsl:attribute>
          <xsl:attribute name="dcolor">red</xsl:attribute>
          <xsl:element name="measure">
            <xsl:element name="xvalue"> <!- the value for the dial is included as the xvalue element  ->
              <xsl:value-of select="salsum"/>
            </xsl:element>
            <xsl:element name="label">
              <xsl:value-of select="concat('Salary sum for department ',dname,' is ',salsum)"/>
            </xsl:element>
          </xsl:element>
        </xsl:element>
        </xsl:for-each>
      </xsl:element>
      <!-- sets -->
    <xsl:element name="markerarea"> <!-- markerareas are zones in the speedometer that can be assigned colors and labels ->
        <xsl:element name="startvalue">7500</xsl:element>
        <xsl:element name="endvalue">9000</xsl:element>
        <xsl:element name="color">green</xsl:element>
        <xsl:element name="title">In this zone we find the cheap departments</xsl:element>
    </xsl:element>
    <xsl:element name="markerarea">
        <xsl:element name="startvalue">9000</xsl:element>
        <xsl:element name="endvalue">11000</xsl:element>
        <xsl:element name="color">yellow</xsl:element>
        <xsl:element name="title">This zones harbours the average salary-spenders</xsl:element>
    </xsl:element>
    <xsl:element name="markerarea">
        <xsl:element name="startvalue">11000</xsl:element>
        <xsl:element name="endvalue">12500</xsl:element>
        <xsl:element name="color">red</xsl:element>
        <xsl:element name="title">These are the expensive ones</xsl:element>
    </xsl:element>
    <xsl:element name="yvalues"><!- markers along the speedometer 'axis'; in this case between 8000 and 12000 there will be 4 markers (spaced a $1000 apart), between every two markers is one submarker  ->
      <xsl:element name="ymarkers">
        <xsl:element name="minvalue">8000</xsl:element>
        <xsl:element name="maxvalue">12000</xsl:element>
        <xsl:element name="steps">4</xsl:element>
        <xsl:element name="gridline">true</xsl:element>
        <xsl:element name="submarkers">1</xsl:element>
        <xsl:element name="showsubmarkervalue">no</xsl:element>
      </xsl:element>
      <xsl:element name="yvalue">
        <xsl:element name="value">7500</xsl:element>
        <xsl:element name="label">7500</xsl:element>
        <xsl:element name="gridline">true</xsl:element>
      </xsl:element>
      <xsl:element name="yvalue">
        <xsl:element name="value">12500</xsl:element>
        <xsl:element name="label">12500</xsl:element>
        <xsl:element name="gridline">true</xsl:element>
      </xsl:element>
    </xsl:element> <!-- yvalues -->
      <xsl:element name="degrees">220</xsl:element> <!- which angle should the speedometer span (360 would indicate a full circle, 180 a semi-circle)  ->
      <xsl:element name="miny">7500</xsl:element>
      <xsl:element name="maxy">12500</xsl:element>
      <xsl:element name="title">Department Salary Sums</xsl:element>
      <xsl:element name="subtitle">Salary in US Dollars</xsl:element>
      <xsl:element name="xtitle">Time</xsl:element>
      <xsl:element name="ytitle">Salary</xsl:element>
     <!- -the counter - ->
      <xsl:element name="counter"><xsl:value-of select="sum(dept/salsum)"/></xsl:element>
      <xsl:element name="counterlabel">Salarysum for all departments</xsl:element>
      <xsl:element name="counterunits">US$</xsl:element>
     <!- -the day-counter - ->
      <xsl:element name="daycounter">0001</xsl:element>
      <xsl:element name="daycounterunits">$/month</xsl:element>
      <xsl:element name="daycounterlabel">Day Counter - no clear meaning in this example</xsl:element>
    </xsl:element>
    <!- - end of graphData - ->
  </xsl:template>
</xsl:stylesheet>

The output from this stylesheet on the source document can be seen here:Department Salary Data Transformed in Sets and Measures for Speedometer Graph

1 2 3
Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

4 Comments

  1. I can understand the report in PDF,XML,HTML.
    I want to do the report in XL.How I can proceed please give a idea.

  2. all information resides here are good.i need some information regardingg migration.i.e, can i get sorce code & front end design for migrating data from excel to oracle.reply soon…plz…

  3. I have drawn up a list of to do’s: new features I would like to add to the Speedometer chart:
    – animation (show the movement of the dials over time)
    – support for multiple axes
    – better markersupport: set markercolor, markerdash and markerlength; gridlines
    – display images (icons) as (part of) markervalues
    – display a legend indicating the meaning of each dial
    – implement a clock using the speedometer stylesheet as demonstration

    I hope to add these features in the new couple of weeks and update the post accordingly.

  4. Pingback: » Enable SVG with Firefox