OT: Google Doodle

As far as google doodles go .. pretty sharp. "Now that's what I'm talking about".

http://www.google.com/logos/halloween08.gif

...Read More

Silly cfchart tweaks

Whenever I run the webcharts3d to search for one thing, I invariably end up finding other small tweaks that might come in handy later. So with that in mind, I am writing up these small tweaks for future use.


SkipLabels
For when the Y-Axis labels get too crowded, the skipLabels attribute can be used to reduce the number of labels displayed, without changing the number of gridlines. For example, the chart on the right displays every other label by using skipLabels="1".



Formatting Y-Axis Dates
ColdFusion seems to display Y-Axis dates in MM/d/yy format. I have yet to figure out a way to change the format except using styles. Here are a few attributes that can be useful when working with dates on the Y-Axis.



LabelFormat:
Used to change the display format of Y-Axis dates. Warning, the patterns are case sensitive.
<labelFormat style="DateTimePattern" pattern="MM.dd.yyyy"/>

IsReversed:
Used to reverse the direction of the Y-Axis values. Can also be applied to other types, not just dates.
<yAxis type="DateTime" isReversed="true">

ScaleMin/ScaleMax:
Can be used to force the chart to display a "fixed" date range. The parseFormat pattern tells webcharts the format of the scaleMin/scaleMax dates. Again, the date patterns are case sensitive.
<yAxis type="DateTime" scaleMin="10/01/2008" scaleMax="10/31/2008"> <parseFormat style="DateTimePattern" pattern="MM/dd/yyyy"/> </yAxis>


MajorUnit/MajorStep:
Similar to SkipLabels, but more suitable for date/time ranges
<dateTimeStyle majorUnit="Day" majorStep="1"/>

Choice LabelFormat:
It turns out there is an interesting label format called "Choice". You can use it to display text instead of a numeric value. For example, display "YES" instead of 1. Just supply the number to text translation separated by a "#". Use a "|" to separate each set. Choice seems to require a little more tweaking than other types. But still an interesting option.

<labelFormat style="Choice" pattern="1#YES|0#NO"/>



Orientation/isMultilevel
A combination of orientation and isMultilevel can also be used to create more readable labels. The image on the left shows how the IsMultilevel setting displays the labels on different lines.

<labelstyle ismultilevel="true" orientation="Parallel">




Examples:
The base file was created by copying the default style (ie C:\ColdFusion8\charting\styles\default.xml ) and modifying only the <yAxis> section. For brevity, only the <yAxis> section of the xml is posted below. The {placeholders} to represent the dynamic values, which are inserted when the charts are generated.

SkipLabels

<!---
FILE: skipLabelsStyle.xml
--->
<yAxis scaleMin="0">
<titleStyle font="Arial-12-bold"/>
<dateTimeStyle majorUnit="Year" minorUnit="Month"/>
<labelFormat style="Pattern" pattern="#,##0.########"/>
<groupStyle skipLabels="{skipLabels}"/>
</yAxis>

<!---
SKIP LABELS EXAMPLE
--->
<cfset chartStyle = FileRead( ExpandPath("./skipLabelsStyle.xml") )>
<cfset chartStyle = replaceNoCase(chartStyle, "{skipLabels}", "1")>
<cfchart format="png" style="#chartStyle#">
<cfchartseries type="bar">
<cfchartdata item="A" value="26">
<cfchartdata item="B" value="15">
<cfchartdata item="C" value="75">
<cfchartdata item="D" value="49">
<cfchartdata item="E" value="90">
<cfchartdata item="F" value="25">
<cfchartdata item="G" value="95">
</cfchartseries>
</cfchart>

Y-Axis Dates

<!---
SAMPLE VALUES for line charts
--->
<cfset q1 = queryNew("ChartItem,ChartValue", "varchar,date")>
<cfset q2 = queryNew("ChartItem,ChartValue", "varchar,date")>
<cfloop from="1" to="8" index="x">
<cfloop from="85" to="90" index="y">
   <cfset row = queryAddRow(q1, 1)>
   <cfset q1["ChartItem"][row] = chr(y)>
   <cfset q1["ChartValue"][row] = createDate(2008, 10, randRange(1,25))>

   <cfset row = queryAddRow(q2, 1)>
   <cfset q2["ChartItem"][row] = chr(y)>
   <cfset q2["ChartValue"][row] = createDate(2008, 10, randRange(1,25))>
</cfloop>
</cfloop>

<!---
FILE 1: yAxisDateStyle.xml
--->  
<yAxis type="DateTime">
<titleStyle font="Arial-12-bold"/>
<labelFormat style="DateTimePattern" pattern="{labelDateFormat}"/>
</yAxis>



<!---
EXAMPLE 1: FORMAT Y-AXIS DATES  (MM.dd.yyy)
--->
<cfset chartStyle = FileRead( ExpandPath("./yAxisDateStyle.xml") )>
<!--- date patterns _are_ case sensitive --->
<cfset chartStyle = ReplaceNoCase( chartStyle, "{labelDateFormat}", "MM.dd.yyyy")>
<cfchart format="png" style="#chartStyle#">
<cfchartseries type="line">
   <cfloop query="q1">
     <cfchartdata item="#ChartItem#" value="#q1.ChartValue[currentRow].getTime()#">
   </cfloop>
</cfchartseries>
<cfchartseries type="line">
   <cfloop query="q2">
     <cfchartdata item="#ChartItem#" value="#q2.ChartValue[currentRow].getTime()#">
   </cfloop>
</cfchartseries>
</cfchart>

<!---
FILE 2: yAxisReverseDateStyle.xml
--->  
<yAxis type="DateTime" isReversed="true">
<titleStyle font="Arial-12-bold"/>
<labelFormat style="DateTimePattern" pattern="{labelDateFormat}"/>
</yAxis>


<!---
EXAMPLE 2: REVERSE AND FORMAT Y-AXIS DATES
--->
<cfset chartStyle = FileRead( ExpandPath("./yAxisReverseDateStyle.xml") )>
<!--- date patterns _are_ case sensitive --->
<cfset chartStyle = ReplaceNoCase( chartStyle, "{labelDateFormat}", "MM.dd.yyyy")>
<cfchart format="png" style="#chartStyle#">
<cfchartseries type="line">
   <cfloop query="q1">
     <cfchartdata item="#ChartItem#" value="#q1.ChartValue[currentRow].getTime()#">
   </cfloop>
</cfchartseries>
<cfchartseries type="line">
   <cfloop query="q2">
     <cfchartdata item="#ChartItem#" value="#q2.ChartValue[currentRow].getTime()#">
   </cfloop>
</cfchartseries>
</cfchart>

<!---
FILE 3: yAxisReverseDateStyle.xml
--->
<yAxis type="DateTime"  scaleMin="{dateMin}" scaleMax="{dateMax}">
<titleStyle font="Arial-12-bold"/>
<labelFormat style="DateTimePattern" pattern="{labelDateFormat}"/>
<parseFormat style="DateTimePattern" pattern="{inputDateFormat}"/>
<dateTimeStyle majorUnit="{dateUnit}" majorStep="{dateStep}"/>
</yAxis>


<!---
EXAMPLE 3: SET SCALE W/SKIP LABELS
--->
<cfset chartStyle = FileRead( ExpandPath("./yAxisDateRangeStyle2.xml") )>
<!--- date patterns _are_ case sensitive --->
<!--- set the input format of min/max dates --->
<cfset chartStyle = ReplaceNoCase( chartStyle, "{inputDateFormat}", "MM/dd/yyyy")>
<cfset chartStyle = ReplaceNoCase( chartStyle, "{dateMin}", "10/01/2008")>
<cfset chartStyle = ReplaceNoCase( chartStyle, "{dateMax}", "10/31/2008")>
<!--- set the display format of Y-Axis dates --->
<cfset chartStyle = ReplaceNoCase( chartStyle, "{labelDateFormat}", "MM.dd.yyyy")>
<!--- display a date label every three (3) Days --->
<cfset chartStyle = ReplaceNocase( chartStyle, "{dateUnit}", "Day" ) >
<cfset chartStyle = ReplaceNocase( chartStyle, "{dateStep}", "3" ) >

<cfchart format="png" style="#chartStyle#">
<cfchartseries type="line">
   <cfloop query="q1">
     <cfchartdata item="#ChartItem#" value="#q1.ChartValue[currentRow].getTime()#">
   </cfloop>
</cfchartseries>
<cfchartseries type="line">
   <cfloop query="q2">
     <cfchartdata item="#ChartItem#" value="#q2.ChartValue[currentRow].getTime()#">
   </cfloop>
</cfchartseries>
</cfchart>

Choice

<!---
FILE: choiceStyle.xml
--->
<yAxis scaleMin="0">
<titleStyle font="Arial-12-bold"/>
<dateTimeStyle majorUnit="Year" minorUnit="Month"/>
<labelFormat style="Choice" pattern="{choicePattern}"/>
</yAxis>


<!---
CHOICE EXAMPLE
--->

<cfset chartStyle = FileRead( ExpandPath("./choiceStyle.xml") )>
<cfset choiceText = "0##|2##Bored to Tears | 4##Jaded | 6##Intrigued">
<cfset chartStyle = ReplaceNoCase( chartStyle, "{choicePattern}", choiceText)>

<cfchart format="png" style="#chartStyle#" scalefrom="0" scaleto="6">
<cfchartseries type="bar">
<cfchartdata item="Group A" value="2">
<cfchartdata item="Group B" value="2">
<cfchartdata item="Group C" value="4">
<cfchartdata item="Group D" value="6">
<cfchartdata item="Group E" value="2">
</cfchartseries>
</cfchart>

...Read More

Believe half of what you see, and none of what Studio Express grid view tells you about whitespace

So I am cleaning up some data from an old MS SQL database today. I needed to split a column on what appeared to be a space character. But obviously not because SELECT CHARINDEX( char(32), MyColumn ) returned zero.

No problem, I thought. It is probably a tab character or something. So I decided to check the ascii values. In a less than brilliant moment, I decided to copy and paste one of the values from grid view and use it for testing. Imagine my surprise when SELECT ASCII( SUBSTRING('some value', 5, 1) ) told me the character in question really was a space. Oookaay... then why does SELECT CHARINDEX( char(32), MyColumn ) return zero?

It took me a few minutes before I realized the problem. The data actually contained char(13) + char(10), but Studio Express grid view helpfully translated the new lines into spaces for my viewing pleasure. That is what I get for not checking the underlying data with sql.

Really I know better. When it comes to whitespace, never trust the graphical tools. They all lie.

...Read More