ColdFusion In Context: Group Profiles

Multiple events have patterns that managers hope to learn from. Are Mondays your busiest day for sales? What time of day are most problems entered into a tracking system? Questions like these can take multiple queries (or multi-part queries) to answer. Further, people are better at drawing conclusions from graphs than from raw numbers.

Here's a technique you can extend to extract patterns from your data and represent them using a bar graph. You'll ask the user for the report month and year, make a list of the items to plot, and for each item, count and plot its occurrences during the chosen time period. Along the way, you'll use a loop to build a list; determine the current month and year; take pieces of a date from a database table, state the numeric day of the week as the name of the day; count matching records; and stretch a picture to represent the record count. To test it, you can copy the whole thing into a page called profile.cfm, create a few records, and run the result. [I only have sample data for May 2001.]

Make Lists for Pull-Downs

You'll want a list of months and of recent years. Months are easy, but to get the last few years, you need to start with this year and work backward. You'll use the ListAppend function to make the list of years, one year at a time, that you want the user to pick from. 

<cfset monthList="1,2,3,4,5,6,7,8,9,10,11,12">
<cfset yearFrom=#year(now())#-10>
<cfset yearList="">
<cfloop index="iYear" from="#yearFrom#" to="#year(now())#">
<cfset yearList=ListAppend(#yearList#, #iYear#)>
</cfloop>

Get or Set Currently Selected Month and Year

If the user has activated the form, then the month and year displayed by the form (dMonth and dYear) need to be used. If not, the current date will be used as the selected month and year. The ColdFusion functions to parse the current date are straightforward and logically named. 

<cfif isDefined("form.dMonth")>
  <cfset sMonth=#form.dMonth#>
  <cfset sYear=#form.dYear#>
<cfelse>
  <cfset instant=now()>
  <cfset sMonth=#month(instant)#>
  <cfset sYear=#year(instant)#>
</cfif>

Ask for Report Month and Year

Users will select the month and year from pulldowns populated by the lists previously created. The currently selected month and year (sMonth and sYear) need to be marked as selected in the select controls that are used to determine the display month and year (dMonth and dYear). Because you need to populate non-ColdFusion controls with ColdFusion variables, the form is surrounded with cfoutput tags to let this happen. (If only a small portion of the form needed this treatment, the tags would be moved inside to surround just that portion.) Finally, notice that although the name of the month is displayed, it is the number of the month that does the work in other parts of this page. 

<cfoutput>
<cfform name="getMonth" action="Profile.cfm" method="post">

<cfselect name="dMonth" size="1">
<cfloop list="#monthList#" index="mon">
<cfif sMonth is mon>
  <option selected value="#mon#">
<cfelse>
  <option value="#mon#">
</cfif>
#monthAsString(mon)#
</cfloop>
</cfselect>

<cfselect name="dYear" size="1">
<cfloop list="#yearList#" index="yr">
<cfif sYear is yr>
  <option selected>
<cfelse>
  <option>
</cfif>
#yr#
</cfloop>
</cfselect>

Add a submit button, add a javascript exit button, and close the form and the cfoutput tag. If you don't want the code to fail, replace "index.htm" with a page that exists. 

<input type=submit name="doIt" value="Select Month and Year">

<input type="button" name="formaction" value="Exit" 
onClick="document.location='index.htm'; return false;">

</cfform>
</cfoutput>

Make a List of the Items to be Plotted

In this example, values are plotted for days of the week. The function to obtain them is datepart(dw, {date}) for Microsoft SQL (where "dw" means day of week) or weekday({date}) for Access. It returns a number for the day of the week where the week begins with Sunday as 1 and ends with Saturday as 7 (unless someone has adjusted the database's defaults). 

<cfquery name="getItem" datasource="myDB">
select distinct weekday(OpenedWhen) as Item
from WorkOrder
</cfquery>

Get and Plot the Value of each Item

In order to let the user copy the report, it's given a separate heading based on the form that determined the month and year to be used. 

<table border="0">
<tr><td colspan="3"><cfoutput><b>
Orders Opened by Day of Week in #monthAsString(val(sMonth))# #sYear#
</b></cfoutput></td></tr>

The html "tt" tag - think "teletype" - is used instead of the "pre" tag to avoid the closing carriage return that comes with the "pre" tag. The copied report looks better with a tight, non-proportional font than with the default proportional fonts chosen by common browsers. 

<tr><td width="15%"><tt>DAY</tt></td>
<td width="10%"><tt>ORDERS</tt></td><td></td></tr>

For each row in the previous query (getItem), the report will generate a new query (getValue) to count the number of rows in the table in which the item appears during the selected month and year. It will then display the day of the week, the count for that day, and a stretched picture (bar graph element) representing the count. The aptly named "DayOfWeekAsString" function in ColdFusion converts 1 to Sunday, 2 to Monday, and so forth. If using something other than Microsoft Access, replace weekday({date}) with datepart(dw, {date}), month({date}) with datepart(mm, {date}), and year({date}) with datepart(yyyy, {date}). To force a variable to equal the record count multiplied by a constant, the evaluate function is used. (Otherwise, ColdFusion doesn't know what to do with the constant and throws an error.) Because Netscape defaults to about 20 pixels if you try to set an image dimension to zero - add one pixel to the result. 

<cfoutput query="getItem">
<cfquery name="getValue" datasource="myDB">
select weekday(OpenedWhen)
from WorkOrder
where weekday(OpenedWhen) = #getItem.Item#
and month(OpenedWhen) = #sMonth#
and year(OpenedWhen) = #sYear#
</cfquery>
<tr><td><tt>#dayOfWeekAsString(getItem.Item)#</tt></td>
<td><tt>#getValue.recordcount#</tt></td>
<td><img src="b.gif" width="#evaluate(9*getValue.recordcount+1)#"
height="15"></td></tr>
</cfoutput>
</table>

Extend the Technique

Now you have an example of one method of extracting a pattern from data and representing it with a bar graph. You've asked the user for a report month and year, made a list of items to plot, and for each item, counted and plotted its occurrences during the chosen time period.

When extending this code for the detail rows in the report, be careful to distinguish between variables associated with the query named in the opening cfoutput tag (getItem) and the query generated between the opening and closing tags (getValue). Refining this technique, you might drop items for which the count is zero. You might report on items which have nothing to do with a date. The count might represent the number of E-mail problems for the period or the number of Canadian orders. The limit to this technique is your imagination. =Marty=

[To avoid frustrating you, this demonstration adds a note to the screen telling which month has data and increases the pull-down range to ten years. The adjustment for Netscape browsers was added after publication.]