7 Replies - 538 Views - Last Post: 26 March 2014 - 01:21 PM Rate Topic: -----

#1 braddorl  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 15
  • Joined: 06-March 14

Need assistance in creating Chart

Posted 21 March 2014 - 12:15 PM

I am trying to create a chart that looks like the attached image using the CFCHART function. As my previous posts have enlightened, I am completely new to programming and development. In Excel this chart was generated by creating the first series issue date and then each subsequent series was stacked by an integer value and then controlling the color of the resulting bar. I have tried to recreate this effect in CF but because the labelformat is date it will not accept an integer to add to the first series. If I try to pass the date it adds the first date and then plots the next series as added to the first series as though 01/14/2013 + 03/26/2013 and ends up somewhere in Jul 2058.

Is this possible? Or am I will I need to learn how to write Java like some examples I have seen?

TIA,
Randy

Attached image(s)

  • Attached Image


Is This A Good Question/Topic? 0
  • +

Replies To: Need assistance in creating Chart

#2 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1924
  • View blog
  • Posts: 3,462
  • Joined: 13-January 08

Re: Need assistance in creating Chart

Posted 22 March 2014 - 08:20 AM

So, what you're looking to build is called a gannt chart. While CF's native charting engine can produce all kinds of different charts, gannt charts aren't one of them. In this event, you have two alternatives:

1./ Build your own gannt chart. The upside of this option is that by building it yourself, you should have control of all aspects of how it renders and looks. The downside is that the skill needed is well beyond a beginner's skill level. That said, this is a handy tutorial for how to make a basic gannt chart.

2./ Or, you could Google around and consider acquiring and using a third party charting option. I've had one project that required a gannt chart and I chose to simply use a third party charting solution because I didn't need the granular control over the chart that building it myself might have provided and the charting solution provided me with other charts that I was able to use in other projects. I chose to use Fusioncharts but this was way back when their charts were a Flash object driven by an XML data feed. I believe they've wholly moved to HTML5 and I'm told that the data pass through is much less quirky and problematic than when I tried it.

I suspect there likely is a native Java charting library you could download and install but I don't write Java or like to mess with the CF engine so that would be up to you. Otherwise, these are pretty much your options. Good luck!
Was This Post Helpful? 1
  • +
  • -

#3 braddorl  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 15
  • Joined: 06-March 14

Re: Need assistance in creating Chart

Posted 25 March 2014 - 11:31 AM

Craig,
I took your advice and I am using the Multi-Line Gantt from ChartDirector ver 5.1. Thank you for the kick in the right direction. I can get the charting to work as long as my data query contains no empty strings. When the data, in this case response date contains an [empty string] as reported via cfdump of the query. I get the following error:

Tried to call "addBoxLayer(double[], double[], int, java.lang.String)", but cannot convert argument 2 to the desired type.

I have tried to manipulate the returned [empty string] by creating a new query that adds a column (set to date) via the following code:

<!---Build Intermediate Query for Graph --->
<cfset qStackedBarCARS = queryNew("CARNUMBER, ISSUEDATE, RESPONSEDATE, CLOSINGDATE, CORRECTIVEACTIONDUEDATE, STATUS,", 
									"VARChar, Date, Date, Date, Date, VARCHAR" )>
	
	<cfoutput query="CARAccessforGraph">
			<cfset queryAddRow(qStackedBarCARS, 1)>
			
			<!---Populate query with Data from Query=CARAccessforGraph --->
			<cfset querySetCell(qStackedBarCARS, "CARNUMBER", #NumberFormat(CARAccessforGraph.CAR_YEAR, "0000")#&"-"&#NumberFormat(CARAccessforGraph.CAR_NO,"000")#)>
			<cfset querySetCell(qStackedBarCARS, "ISSUEDATE", #CARAccessforGraph.Issue_Date#)>
			<cfset querySetCell(qStackedBarCARS, "RESPONSEDATE", #CARAccessforGraph.Response_Date#)>
			<cfset querysetcell(qStackedBarCARS, "CLOSINGDATE", #CARAccessforGraph.Closing_Date#)>
			<cfset querysetcell(qStackedBarCARS, "CORRECTIVEACTIONDUEDATE", #CARAccessforGraph.Due_Date#)>
			
			<!---Determine status of each record --->
				<cfif trim(CARAccessforGraph.Due_Date) EQ "">
					<cfset querySetCell(qStackedBarCARS, "STATUS", "No Corrective Action Assigned")>
				<cfelseif trim(CARAccessforGraph.Closing_Date) EQ "">
					<cfset querySetCell(qStackedBarCARS, "STATUS", #CARAccessforGraph.Due_Date# - dateformat(Now(), "mm/dd/yyyy"))>
				<cfelseif CARAccessforGraph.Closing_Date GT 0>
					<cfset querySetCell(qStackedBarCARS, "STATUS", "Complete")>
				</cfif>	
	</cfoutput>

<!---Loops through query to analyze EmptyStrings and build list and array to add column of data to "ChartRESPONSEDATE" --->
	<cfset temp ="">	
	<cfset mylist = "">	
	
	<cfoutput query="qStackedBarCARS">
			<!---Determine ChartRESPONSEDATE Variable --->
				<cfif qStackedBarCARS.RESPONSEDATE EQ "">
					<cfset temp = #CREATEODBCDATETIME(DATEFORMAT(DATEADD("d", 30, Now()),"yyyy-mm-dd"))#>
				<cfelse>
					<cfset temp = #qStackedBarCARS.RESPONSEDATE#>
				</cfif>
				<cfset mylist = ListAppend(mylist, temp)>
	</cfoutput>
			
				<cfset ArrayUpdate = ListtoArray(mylist)>
				<cfset queryAddColumn(qStackedBarCARS, "ChartRESPONSEDATE", "Date", ArrayUpdate)>







I have dumped the results of this query and it shows a {ts '2014-04-24 00:00:00'} value in the new field where the original field had an [empty string]. So I know I am getting a date variable in there but unless I go back to the original data and force a date (which I can only do for testing) I get the error from above. Any suggestions?
Was This Post Helpful? 0
  • +
  • -

#4 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1924
  • View blog
  • Posts: 3,462
  • Joined: 13-January 08

Re: Need assistance in creating Chart

Posted 25 March 2014 - 02:28 PM

Hm. The problem I'm having is following your business logic here.

You said you were getting the error from the charting solution when the response date from your original query was empty. However, you still add that date to the qStackedBarCARS query anyway and then apply some logic to populate a list that ends up as a new column (ChartRESPONSEDATE) that you add at the end...but apparently your error persists, right?

Without being able to see it error it's tough to tell what it is that that second error is tripping over. One thing though: you could modify the initial query you're using (CARAccessforGraph) to use the logic you're applying via the looping over that query to build the other query. All the things you're doing there can be done within the original SQL so you wouldn't need CF to do that. If you pass the contents of the original query into the charting engine and it throws an error for a missing date, your query can be amended to ensure that it doesn't return a blank but that it returns a date 30 days from the present day (like you have in your code).

I know that's not a lot of help but without being able to monkey with the charting engine itself (and forcing a date like you've mentioned) it's tough to be able to help you further than that. For instance, what do you do with qStackedBarCARS to pass it to the charting engine? Send it as a query? Convert it to an array?
Was This Post Helpful? 2
  • +
  • -

#5 braddorl  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 15
  • Joined: 06-March 14

Re: Need assistance in creating Chart

Posted 25 March 2014 - 03:34 PM

Craig,
There is not a lot of logic in my writing the code. The first query CARSACCESSFORGRAPH is a basic select, from, inner join, ordered by SQL statement from an ACCESS db. The intermediate query qStackedBarsChart populates from the first so that I could loop over it with the cfif statements and use a query add column to manipulate the data because I knew how to do that. That's the logic. Any pointers appreciated!

So the problem I believe is that when the database has a null value for response_date, the code that converts the query .toarray() does not pass as a double which is what the chart engine expects. But when I put a "dummy date" in the database for response_date, the code for the .toarray passes a double and everything works. I looked at the metadata for the query under both cases and it states that it is a date type. So my deduction is that when a null is in the source data it is somehow affecting the .toarray, but why and how to correct I don't know. I'm googling the cast() function now but haven’t figured out how to use it to convert the column response_date .toarray() as a forced double so that the code for the chart engine works.

I wish I could explain better, but I am learning this as I go. Google may or may not be my friend. From your responses, I assume I am going about this wrong, but learning by trial and a lot of errors.

Randy
Was This Post Helpful? 0
  • +
  • -

#6 braddorl  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 15
  • Joined: 06-March 14

Re: Need assistance in creating Chart

Posted 26 March 2014 - 06:28 AM

So after some Plan, Do, Check, Act I have determined that I am not passing the array of the column as a DateObject which is what ChartDirector is expecting. I determined this experimentation and reading in depth the ChartDirector help file. So my latest question is why am I not passing a CF Date Object? The ChartDirector Help File states:

"Many functions in the ChartDirector API accept dates/times as parameters. ChartDirector supports using ColdFusion date objects to represent dates. Note that ChartDirector uses real ColdFusion date objects, not "numeric dates" (ColdFusion dates convert to a number). If ChartDirector encounters a numeric date, it will just treat it as an ordinary number.

Internally, ChartDirector represents dates/times as the seconds elapsed since 1-1-0001 00:00:00. When ChartDirector encounters a ColdFusion date, it will automatically convert it to the internal format for processing."

So if I do any math on the dates in CF, the Date Object is lost, or so it appears. Because if I just pass in a column from the original SQL Query (Issue_Date) or a Back-end manipulated column of dates with no [empty strings] every thing works great.

My latest iteration of code follows: Yes Craig I know it is extremely ugly.
<cfset twelvemonthsAgo = DateAdd("m",-12,Now())> 

<cfquery datasource="QA" dbtype="ODBC" name="CARAccessforGraph">
	SELECT CAR_LOG.CAR_YEAR, CAR_LOG.CAR_NO, CAR_LOG.Issue_Date, CAR_LOG.Response_Date, CAR_LOG.Due_Date, CAR_LOG.Closing_Date, CAR_LOG.Cat
	FROM CAR_LOG
	WHERE CAR_LOG.Closing_Date IS NULL OR CAR_LOG.Issue_Date >= #twelvemonthsago# 
	GROUP BY CAR_LOG.CAR_YEAR, CAR_LOG.CAR_NO, CAR_LOG.Issue_Date, CAR_LOG.Response_Date, CAR_LOG.Due_Date, CAR_LOG.Closing_Date, CAR_LOG.Cat
	ORDER BY Issue_Date;
</cfquery>

<!---Get the minimum date from the query CARAccessforGraph for use in the charting function --->
<cfquery dbtype="query" name="minCARDate">
	SELECT min(Issue_Date) as BEGINCHARTDATE from CARAccessforGraph
</cfquery>

<!---Get the maximum date from the query CARAccessforGraph for use in the charting function --->
<cfquery dbtype="query" name="maxCARDATE">
	SELECT max(Due_Date) as ENDCHARTDATE from CARAccessforGraph
</cfquery>

<!---Loops through query to Concatenate CAR_Year and CAR_NO and add column of data to "CARNUMBER" --->
	<cfset temp ="">	
	<cfset mylist = "">	
	
	<cfoutput query="CARAccessforGraph">
			<!---Determine CARNUMBER Variable --->
				<cfset temp = #NumberFormat(CARAccessforGraph.CAR_YEAR, "0000")#&"-"&#NumberFormat(CARAccessforGraph.CAR_NO,"000")#>
				<cfset mylist = ListAppend(mylist, temp)>
	</cfoutput>
			
				<cfset ArrayUpdate = ListtoArray(mylist,",",TRUE)>
				<cfset queryAddColumn(CARAccessforGraph, "CARNUMBER", "varChar", ArrayUpdate)>

<!---Loops through query to analyze EmptyStrings and build list and array to add column of data to "RequiredResponseDate" --->
	<cfset temp ="">	
	<cfset mylist = "">	
	
	<cfoutput query="CARAccessforGraph">
			<!---Determine RequiredResponseDate Variable --->		<!---Current Code Represents latest attempt to force Date Object --->
				<cfif CARAccessforGraph.Cat EQ 1 >
					<cfset funyr = #DateFormat(DATEADD("w", 3, CARAccessforGraph.Issue_Date), "yyyy")#>
					<cfset funm = #DateFormat(DATEADD("w", 3, CARAccessforGraph.Issue_Date), "mm")#>
					<cfset fund = #DateFormat(DATEADD("w", 3, CARAccessforGraph.Issue_Date), "dd")#>
					<cfset temp = CreateDate(#funyr#, #funm#, #fund#)>
				<cfelseif CARAccessforGraph.Cat EQ 2 >
					<cfset funyr = #DateFormat(DATEADD("w", 3, CARAccessforGraph.Issue_Date), "yyyy")#>
					<cfset funm = #DateFormat(DATEADD("w", 3, CARAccessforGraph.Issue_Date), "mm")#>
					<cfset fund = #DateFormat(DATEADD("w", 3, CARAccessforGraph.Issue_Date), "dd")#>
					<cfset temp = CreateDate(#funyr#, #funm#, #fund#)>
				<cfelse>
					<cfset funyr = #DateFormat(DATEADD("w", 5, CARAccessforGraph.Issue_Date), "yyyy")#>
					<cfset funm = #DateFormat(DATEADD("w", 5, CARAccessforGraph.Issue_Date), "mm")#>
					<cfset fund = #DateFormat(DATEADD("w", 5, CARAccessforGraph.Issue_Date), "dd")#>
					<cfset temp = CreateDate(#funyr#, #funm#, #fund#)>
				</cfif>
				<cfset mylist = ListAppend(mylist, temp)>
	</cfoutput>
	
				<cfset ArrayUpdate = ListtoArray(mylist,",",TRUE)>
				<cfset queryAddColumn(CARAccessforGraph, "RequiredResponseDate", "Date", ArrayUpdate)>

<cfdump var="#CARAccessforGraph#">									<!---Once I am able to get the RequiredResponseDate to pass correctly will need to code other Dates for Charting --->

<!---The following sets up variables for use by ChartDirector --->

<cfset vlabels = CARAccessforGraph["CARNUMBER"].toArray()>
<cfset vIssueDate = CARAccessforGraph["ISSUE_DATE"].toArray()>
<cfset vRequiredResponseDate = CARAccessforGraph["RequiredResponseDate"].toArray()> <!---This passess an array that is not compatiable with the data conversion of ChartDirector, which expects a Date Object --->

<!---Below is the code from ChartDirector Ver 5.1 that requires modification to work with CAR data --->
<cfscript>

chartingStartdate = minCARDate.BeginChartDate;
chartingEnddate = DateAdd("d", 30, maxCARDate.EndChartDate);

// ChartDirector for ColdFusion API Access Point
cd = CreateObject("java", "ChartDirector.CFChart");

// A utility to allow us to create arrays with data in one line of code
function Array() {
    var result = ArrayNew(1);
    var i = 0;
    for (i = 1; i LTE ArrayLen(arguments); i = i + 1)
        result[i] = arguments[i];
    return result;
}

// the names of the tasks
labels = vlabels; 


// the planned start dates and end dates for the tasks
startDate = vIssueDate;
endDate = vRequiredResponseDate;

// the actual start dates and end dates for the tasks up to now
actualStartDate = Array(CreateDate(2004, 8, 16), CreateDate(2004, 8, 27), CreateDate(
    2004, 9, 9), CreateDate(2004, 9, 18), CreateDate(2004, 9, 22));
actualEndDate = Array(CreateDate(2004, 8, 27), CreateDate(2004, 9, 9), CreateDate(
    2004, 9, 27), CreateDate(2004, 10, 2), CreateDate(2004, 10, 8));

// Create a XYChart object of size 1000 x 1000 pixels. Set background color to light
// green (ccffcc) with 1 pixel 3D border effect.
c = cd.XYChart(1000, 1000, "0xccffcc", "0x000000", 1);

// Add a title to the chart using 15 points Times Bold Itatic font, with white
// (ffffff) text on a dark green (0x6000) background
c.addTitle("Open CARs and 12 month History", "Times New Roman Bold Italic", 15,
    "0xffffff").setBackground("0x006000");

// Set the plotarea at (140, 55) and of size 840 x 920 pixels. Use alternative
// white/grey background. Enable both horizontal and vertical grids by setting their
// colors to grey (c0c0c0). Set vertical major grid (represents month boundaries) 2
// pixels in width
c.setPlotArea(140, 55, 840, 920, "0xffffff", "0xeeeeee", cd.LineColor, "0xc0c0c0",
    "0xc0c0c0").setGridWidth(2, 1, 1, 1);

// swap the x and y axes to create a horziontal box-whisker chart
c.swapXY();

// Set the y-axis scale to be date scale from Aug 16, 2004 to Nov 22, 2004, with
// ticks every 7 days (1 week)
// Original Code: c.yAxis().setDateScale(CreateDate(2004, 8, 16), CreateDate(2004, 11, 22), 86400 * 7);
/* Insert of my code to establish chart start and end dates*/
c.yAxis().setDateScale(ChartingStartDate, ChartingEndDate, 86400 * 7);


// Add a red (ff0000) dash line to represent the current day
c.yAxis().addMark(Now(), c.dashLineColor("0xff0000", cd.DashLine));

// Set multi-style axis label formatting. Month labels are in Arial Bold font in "mmm
// d" format. Weekly labels just show the day of month and use minor tick (by using
// '-' as first character of format string).
c.yAxis().setMultiFormat(cd.StartOfMonthFilter(), "<*font=Arial Bold*>{value|mmm d}",
    cd.StartOfDayFilter(), "-{value|d}");

// Set the y-axis to shown on the top (right + swapXY = top)
c.setYAxisOnRight();

// Set the labels on the x axis
c.xAxis().setLabels(labels);

// Reverse the x-axis scale so that it points downwards.
c.xAxis().setReverse();

// Set the horizontal ticks and grid lines to be between the bars
c.xAxis().setTickOffset(0.5);

// Use blue (0000aa) as the color for the planned schedule
plannedColor = "0x0000aa";

// Use a red hash pattern as the color for the actual dates. The pattern is created
// as a 4 x 4 bitmap defined in memory as an array of colors.
actualColor = c.patternColor(Array("0xffffff", "0xffffff", "0xffffff", "0xff0000",
    "0xffffff", "0xffffff", "0xff0000", "0xffffff", "0xffffff", "0xff0000",
    "0xffffff", "0xffffff", "0xff0000", "0xffffff", "0xffffff", "0xffffff"), 4);

// Add a box whisker layer to represent the actual dates. We add the actual dates
// layer first, so it will be the top layer.
actualLayer = c.addBoxLayer(actualStartDate, actualEndDate, actualColor, "Actual");

// Set the bar height to 8 pixels so they will not block the bottom bar
actualLayer.setDataWidth(8);

// Add a box-whisker layer to represent the Required Response date frame
c.addBoxLayer(startDate, endDate, plannedColor, "Required Response").setBorderColor(
    cd.SameAsMainColor);

// Add a legend box on the top right corner (595, 60) of the plot area with 8 pt
// Arial Bold font. Use a semi-transparent grey (80808080) background.
b = c.addLegend(595, 60, False, "Arial Bold", 8);
b.setAlignment(cd.TopRight);
b.setBackground("0x80808080", -1, 2);

// Output the chart
chart1URL = c.makeSession(GetPageContext(), "chart1");

// Include tool tip for the chart
imageMap1 = c.getHTMLImageMap("", "",
    "title='{xLabel} ({dataSetName}): {top|mmm dd, yyyy} to {bottom|mmm dd, yyyy}'");

</cfscript>
<html>
<body style="margin:5px 0px 0px 5px">
<div style="font-size:18pt; font-family:verdana; font-weight:bold">
    Multi-Layer Gantt Chart
</div>
<hr style="border:solid 1px #000080" />
<cfoutput>

<img src="getchart.cfm?#chart1URL#" usemap="##map1" border="0" />
<map name="map1">#imageMap1#</map>
</cfoutput>
</body>
</html>


						


Was This Post Helpful? 0
  • +
  • -

#7 braddorl  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 15
  • Joined: 06-March 14

Re: Need assistance in creating Chart

Posted 26 March 2014 - 07:01 AM

I added this code

for(i = 1; i LE ArrayLen(endDate); i = i + 1)
     endDate[i] = LSParseNumber(endDate[i]);



And now I get this error:

{ts'2013-01-0900:00:00'} must be interpretable as a valid number in the current locale.
Was This Post Helpful? 0
  • +
  • -

#8 braddorl  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 15
  • Joined: 06-March 14

Re: Need assistance in creating Chart

Posted 26 March 2014 - 01:21 PM

View Postbraddorl, on 26 March 2014 - 10:01 AM, said:

I added this code

for(i = 1; i LE ArrayLen(endDate); i = i + 1)
     endDate[i] = LSParseNumber(endDate[i]);



And now I get this error:

{ts'2013-01-0900:00:00'} must be interpretable as a valid number in the current locale.


DUH! LSparsedatetime not LSPARSENUMBER! STUPID STUPID STUPID!
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1