Tuesday, November 18, 2008

ColdFusion 8 - Creating a Simple Gantt Chart

I needed to create a basic Gantt Chart from a query. So once again I dipped into the webcharts3d utility, borrowing code from a great entry about charts on Raymond Camden's blog.

I made some small modifications. Using the webcharts utility, I selected the XMLStyle tab and replaced a few dynamic values with {placeholders} . Then saved only the style information to an xml file. That way I could read in the style information directly, without having to extract it from a .wcp file.



Next, I created two functions. One to convert my query into webchart's xml format. The other to encapsulate Raymond Camden's code to generate the chart html. Since I needed some flexibility with the formatting, the functions have a number of optional settings for things like date formats and locale. But even with the minimal settings it produces a nice and simple Gantt Chart.



Unfortunately, the one thing I could not figure out was how to localize the dates in the chart tool tips. The webcharts3d documentation shows how you can use patterns to format tool tip values. Patterns definitely come in handy. Especially date patterns.


<!--- example of using patterns in chart style xml file --->
<![CDATA[
$(prevValue;EEE MMMM d, yyyy)
$(nextValue;EEE MMMM d, yyyy)
]]>



The problem is the date patterns seem to be applied without regard to the current Locale. They are always formatted according to the default Locale of the jvm. For example, I created a chart using es_ES: Spanish (Standard). While the date labels are handled correctly, the tool tips are formatted using English (US), because that is my server's default.



Very odd. I am hoping I overlooked something. I cannot imagine the utility can format the date labels correctly, but *whoops* .. we forgot about tool tips. Drat. That small item mars an otherwise perfect chart. Oh, well I guess I will keep looking.

Updated: November 19 - Corrected copy / paste error
Updated: March 1, 2010 - Modified date/time formatting

Generate Gantt Chart
<!---
    CREATE QUERY WITH SAMPLE DATA
--->
<cfset rangeStart = createDate(2008,10,1)>
<cfset rangeEnd = createDate(2008,10,31)>
<cfset numOfDays = 31>
<cfset qTasks = QueryNew("TaskName,Type,StartDate,EndDate")>
<cfloop from="1" to="6" index="t">
    <cfloop list="Testing,Development" index="type">
        <cfset row = queryAddRow(qTasks, 1)>
        <cfset d1 = randRange(1, 31)>
        <cfset d2 = randRange(1, 31)>
        <cfset taskStart = createDate(2008, 10, min(d1, d2))>
        <cfset taskEnd = createDate(2008, 10, max(d1, d2))>

        <cfset querySetCell(qTasks, "TaskName", "Task "& t, row)>
        <cfset querySetCell(qTasks, "Type", type, row)>
        <cfset querySetCell(qTasks, "StartDate", taskStart, row)>
        <cfset querySetCell(qTasks, "EndDate", taskEnd, row)>
    </cfloop>
</cfloop>

<!---
    GENERATE CHART HTML
--->
<cfset chartProp = {    stylePath = ExpandPath("./simpleGanttStyle.xml"),
                        query = qTasks,
                        nameCol = "TaskName",
                        typeCol = "Type",
                        startCol = "StartDate",
                        endCol = "EndDate",
                        height = 400,
                        width = 800
    }>

<cfset chartHTML = generateGantt( argumentCollection = chartProp )>

<!--- display chart --->
<cfoutput>#chartHTML#</cfoutput>


simpleGanttStyle.xml (Chart Style)
<?xml version="1.0" encoding="UTF-8"?>
<gantt>
     <xAxis isMultiline="true">
         <dateTimeStyle majorUnit="Day" minorUnit="Hour" />
         <groupFormat style="DateTimePattern" pattern="{groupDatePattern}">
                <locale lang="{language}" country="{country}" variant=""/>
           </groupFormat>
             <labelFormat style="DateTimePattern" pattern="{labelDatePattern}">
                <locale lang="{language}" country="{country}" variant=""/>
           </labelFormat>
     </xAxis>

     <dataLabels style="Value"/>
     <popup background="#C8FFFFFF" foreground="#333333" isMultiline="true"/>
     <paint palette="Dawn" paint="Plain" max="100"/>
 <![CDATA[
$(rowLabel)
/ $(colLabel)
$(prevValue;{tipDatePattern})
$(nextValue;{tipDatePattern})
 ]]>
</gantt>


Functions
<cffunction name="generateGantt" returntype="string">
    <cfargument name="stylePath" type="string" required="true" hint="Absolute path the xml chart style">
    <cfargument name="query" type="query" required="true">
    <cfargument name="nameCol" type="string" required="true">
    <cfargument name="typeCol" type="string" required="true">
    <cfargument name="startCol" type="string" required="true">
    <cfargument name="endCol" type="string" required="true">
    <cfargument name="width" type="numeric" required="true">
    <cfargument name="height" type="numeric" required="true">
    
    <cfargument name="format" type="string" required="false" default="png">
    <cfargument name="language" type="string" required="false" default="">
    <cfargument name="country" type="string" required="false" default="">
    <cfargument name="groupDatePattern" type="string" required="false" default="MMM-yyyy">
    <cfargument name="labelDatePattern" type="string" required="false" default="E dd">
    <cfargument name="tipDatePattern" type="string" required="false" default="">

    <cfset var Local = {} >

    <cfscript>
        Local.Locale = createObject("java", "java.util.Locale");
        // Create a Locale using the given language / country
        if ( len( arguments.language ) and len( arguments.country ) ) {
            Local.dateLocale = Local.Locale.init( arguments.language, arguments.country );
        }
        // Use the current Locale
        else {
            Local.dateLocale = getPageContext().getResponse().getLocale();
            if ( not structKeyExists(Local, "dateLocale") ) {
                Local.dateLocale = Local.Locale.getDefault();
            }    
        }
    
        arguments.dateLocale = Local.dateLocale;
        arguments.language = Local.dateLocale.getLanguage();
        arguments.country = Local.dateLocale.getCountry();
        arguments.model = queryToGanttModel( argumentCollection= arguments );
        Local.chartHTML = generateGanttHTML( argumentCollection = arguments );
    </cfscript>

    <cfreturn Local.chartHTML>
</cffunction>


<cffunction name="generateGanttHTML" returntype="string">
    <cfargument name="stylePath" type="string" required="true">
    <cfargument name="model" type="string" required="true">
    <cfargument name="height" type="numeric" required="true">
    <cfargument name="width" type="numeric" required="true">
    <cfargument name="format" type="string" required="false" default="png">
    <cfargument name="language" type="string" required="true" default="en">
    <cfargument name="country" type="string" required="false" default="US">
    <cfargument name="groupDatePattern" type="string" required="false" default="MMM-yyyy">
    <cfargument name="labelDatePattern" type="string" required="false" default="EEE dd">
    <cfargument name="tipDatePattern" type="string" required="false" default="">
    
    <cfset var Local = {}>
    
    <cfscript>
        // Read in the xml styles and set the desired date patterns
        // Note, the date patterns _are_ case sensitive
        Local.style = FileRead( arguments.stylePath );
    
        // SOURCE: Raymond Camden blog entry
        // SOURCE: http://www.coldfusionjedi.com/index.cfm/2008/1/18/Coolest-CFCHART-Trick-Ever
        Local.style = replaceNoCase( Local.style, "{groupDatePattern}", arguments.groupDatePattern );
        Local.style = replaceNoCase( Local.style, "{labelDatePattern}", arguments.labelDatePattern );
        Local.style = replaceNoCase( Local.style, "{language}", arguments.language, "all" );
        Local.style = replaceNoCase( Local.style, "{country}", arguments.country, "all" );
        Local.style = replaceNoCase( Local.Style, ";{tipDatePattern}", ";"& arguments.tipDatePattern, "all" );
    
        Local.MxServerComponent = createObject( "java", "com.gp.api.jsp.MxServerComponent" );
        Local.context = getPageContext().getServletContext();
        Local.chartServer = Local.MxServerComponent.getDefaultInstance( Local.context );
    
        Local.chart = Local.chartServer.newImageSpec();
        Local.chart.width = arguments.width;
        Local.chart.height = arguments.height;
        Local.chart.type = arguments.format;
        Local.chart.style = Local.style;
        Local.chart.model = arguments.model;
    
        // Get host information
        if ( getPageContext().getRequest().isSecure() ) {
            Local.baseURL = "https://"& getPageContext().getRequest().getHeader('Host') &"/";
        }
        else {
            Local.baseURL = "http://"& getPageContext().getRequest().getHeader('Host') &"/";
        }
    </cfscript>
    
    <!--- Create html tag set --->
    <cfsavecontent variable="Local.chartHTML">
        <cfoutput>#Local.chartServer.getImageTag( Local.chart, Local.baseURL & "CFIDE/GraphData.cfm?graphCache=wc50&graphID=")#</cfoutput>
    </cfsavecontent>
    
    <!--- Good old Webcharts loves to add an extra /Images/ to the URL --->
    <cfset Local.chartHTML = replace( Local.chartHTML, Local.baseURL & "Images/", Local.baseURL , "all" )>
    
    <cfreturn Local.chartHTML>
</cffunction>


<cffunction name="queryToGanttModel" returntype="string">
    <cfargument name="query" type="query" required="true">
    <cfargument name="nameCol" type="string" required="true">
    <cfargument name="typeCol" type="string" required="true">
    <cfargument name="startCol" type="string" required="true">
    <cfargument name="endCol" type="string" required="true">
    <cfargument name="dateLocale" type="any" required="true">
    
    <cfset var Local = {}>

    <cfscript>
        // use formatter to convert date objects into strings
        // Note: This constructor may not support all locales    
        // Note: Date patterns _are_ case sensitive
        Local.datePattern = "yyyy-MM-dd HH:mm:ss"; 
        Local.SimpleDateFormat = createObject("java", "java.text.SimpleDateFormat");
        Local.formatter = Local.SimpleDateFormat.init( Local.datePattern, arguments.dateLocale );
        Local.formatter.setLenient( false );
            
        // prepare to generate the xml
        Local.newline = createObject("java", "java.lang.System").getProperty("line.separator");
        Local.sb = createObject("java", "java.lang.StringBuffer").init();
    
        //    generate the xml headers
        //  note, newlines are added for 'readability'
        Local.sb.append('<?xml version="1.0" encoding="UTF-8"?>');
        Local.sb.append( Local.newline );
        Local.xmlLocale = arguments.dateLocale.getLanguage() &"-"& arguments.dateLocale.getCountry();
        Local.sb.append('<xml pattern="'& Local.datePattern &'" locale="'& Local.xmlLocale &'">');
        Local.sb.append( Local.newline );
    
        // generate the xml chart content using the query data
        for ( Local.row = 1; Local.row <= arguments.query.recordCount; Local.row++) {
            // extract the time (ie number of milliseconds) from the date column values
            Local.startDate = arguments.query[arguments.startCol][Local.row].getTime();
            Local.endDate = arguments.query[arguments.endCol][Local.row].getTime();
    
            // construct an xml element for each row in the query
            Local.sb.append('<item name="'& arguments.query[arguments.nameCol][Local.row] &'"');
            Local.sb.append(' type="'& arguments.query[arguments.typeCol][Local.row] &'"');
            Local.sb.append(' from="'& Local.formatter.format( Local.startDate ) &'"');
            Local.sb.append(' to="'& Local.formatter.format( Local.endDate ) &'" />');
            Local.sb.append( Local.newline );
        }
    
        //     append closing tag
        Local.sb.append( '</xml>' );
    </cfscript>

    <cfreturn Local.sb.toString()>
</cffunction>

10 comments:

cfSearching February 26, 2010 at 10:47 AM  

This comment was mistakenly trashed, along with a batch of spam.

Posted by: (JC)
====================
This code works great .. except I've noticed that the bars are off by one day for me. For example, if a project has an end date of October 20th, the bar draws to the line between the 18th & 19th. Where as, I would expect it to draw between the 19th and 20th.

JC March 1, 2010 at 9:33 AM  

I believe you are also experiencing the same issue. In your image above, look at how the tool tip says that the dates for Task 1 go from 10/3/2008-10/11/2008. Then, the green bar for Task 1 draws from 10/2/2008-10/10/2008. I think this is a bug with webCharts3D. I've tried find a discrepancy with the data and it looks good. Any ideas?

cfSearching March 1, 2010 at 3:21 PM  

@JC,

Well, webcharts does have a few issues with tooltips/Locales. But mainly when you are using something other than the "default" Locale.

Regarding the image in this entry, I really do not remember the underlying cause :) Though I do recall trying all kinds of crazy o/s and jvm date/time settings during testing, which may have had something to do with it.

You may want to try tweaking the major/minor units of the xAxis dateTimeStyle explicitly.

Example:
<dateTimeStyle majorUnit="Day" minorUnit="Hour" />

Also, keep in mind the left side of each date header is beginning of that date (ie 12:00 AM). So if you are displaying dates (only) like in this entry:

Start October 18, 2008 12:00:00 AM
End October 20, 2008 12:00:00 AM

The bar actually does stretch to Oct 20th. But only the beginning of that day. So due to how the chart is formatted, it may seem short one day. But it is really not.

HTH
-Leigh

cfSearching March 1, 2010 at 6:00 PM  

@JC,

In thinking about it, I see one other change that should help. That is assuming your query dates have a time value other than 12:00 AM. In the queryToGanttModel function, change the date pattern to include the time:

ie:
Local.datePattern = "yyyy-MM-dd HH:mm:ss";

-Leigh

Unknown May 12, 2010 at 6:52 AM  

I'd like to add two features to my chart:
1. A title
2. A URL to each data plot to allow for "drill down" functionality.

Any thoughts?

Mark <><

cfSearching May 12, 2010 at 7:20 AM  

@Mark,

Modify the simpleGanttStyle.xml.


- For links, add an "action" attribute to the <gantt> tag. Use whatever pseudo variables you need $(value), etcetera...

<gantt isVGridVisible="true" action="someOtherPage.cfm?value=$(value)">

- For an overall title, add a <title> property, somewhere beneath the xAxis

<title>Some Title</title>

-Leigh

Unknown May 12, 2010 at 8:49 AM  

How do I set the value of $(value) in my code? For example, right now $(value) contains "Wed 14-Fri 16" for a date range of:

10-14-2009
10-16-2009

I need the Task and the date range in order to effectively "drill down".

Thanks for all the help.

Mark <><

cfSearching May 12, 2010 at 9:34 AM  

You cannot set the values of the psuedo variables. The values are determined by webcharts. But you can use different variables like $(prevValue), $(nextValue), $(rowLabel).

You can also use formatting, like in the xml above: ie $(prevValue; MM/dd/yyyy)


To get a full list of the variables avaiable, run the webcharts utility. Click the Help Tab and look under:
Contents > Designer > Data Labels > (Parameters Link)

-Leigh

Unknown May 12, 2010 at 11:55 AM  

First off, thank you very much for all your help on this.

Now, I'm very close to getting my graph to function exactly like I want with one exception. The value being displayed in the tool is not correct. Is it possible to email you an example of that is happening and see if you have a solution for me? It's a lot easier to see than to explain. You should have my email address associated with this post.

Mark <><

cfSearching May 12, 2010 at 1:07 PM  

@Mark,

Actually blogger does not show the email AFAIK. (Something it does right ;-) But you can email my yahoo address (ie cfsearching)

-Leigh

  © Blogger templates The Professional Template by Ourblogtemplates.com 2008

Header image adapted from atomicjeep