troubleshooting Question

Fusioncharts xml from Coldfusion query

Avatar of joedfuse
joedfuseFlag for United States of America asked on
Web DevelopmentXMLColdFusion Language
13 Comments1 Solution1674 ViewsLast Modified:
Hello experts, What i need to do is create an xml page from a sql query in Coldfusion. I have it working for a basic cfloop using set label and value. But i am running into problems using datasets. Below i will copy the working cf code and output xml and i will include the xml structure i need to have.
Im using fusioncharts to graph out the following queries and folloowed there tutorials as best i could but they dont have anything on the combo chart  so i ave been trying for hours to figure out how to loop through the DB.... and can easily do it the forst way but fail the second.

Thanks in advance for any help
<!--- WORKING CODE --->
<!--- check to see if someone has selected to view past transactions, if not load current month --->
<cfif IsDefined("form.month")>
<cfset reportMonth = "#form.month#">
<cfset reportYear = "#form.year#">
<cfelse>
<cfset reportMonth  = #month(now())#>
<cfset reportYear  = #year(now())#>
</cfif>

<!--- main query to pull activity and link forecast best etc--->
<cfquery name="getActivityRelate">
SELECT  
  dbo.company.companyName, 
  dbo.products.prodName, 
  dbo.products.prodID, 
  dbo.users.fName, 
  dbo.users.lName, 
  SUM(dbo.activityTransactions.activityCnt) AS activityCnt, 
  MAX(dbo.activityTransactions.updateDate) AS updateDate, 
  dbo.best.best, 
  dbo.forecast.forecast 
FROM 
 dbo.products 
 INNER JOIN dbo.company ON (dbo.products.compID=dbo.company.compID) 
 INNER JOIN dbo.users ON (dbo.users.compID=dbo.company.compID) 
  AND (dbo.products.userID=dbo.users.userID) 
 INNER JOIN dbo.activityTransactions ON (dbo.company.compID=dbo.activityTransactions.compID) 
  AND (dbo.users.userID=dbo.activityTransactions.userID) 
  AND (dbo.products.prodID=dbo.activityTransactions.prodID) 
  AND (dbo.users.compID=dbo.activityTransactions.compID) 
  AND (dbo.products.userID=dbo.activityTransactions.userID) 
  AND (dbo.products.compID=dbo.activityTransactions.compID) 
 INNER JOIN dbo.forecast ON (dbo.forecast.prodID=dbo.activityTransactions.prodID) 
  AND (dbo.forecast.userID=dbo.activityTransactions.userID) 
  AND (dbo.forecast.compID=dbo.activityTransactions.compID) 
  AND (dbo.forecast.compID=dbo.company.compID) 
  AND (dbo.forecast.prodID=dbo.products.prodID) 
  AND (dbo.forecast.userID=dbo.users.userID) 
 INNER JOIN dbo.best ON (dbo.best.userID=dbo.activityTransactions.userID) 
  AND (dbo.best.prodID=dbo.activityTransactions.prodID) 
WHERE 
  users.userID = #session.userID# AND 
  (dbo.activityTransactions.activeMM = #reportMonth#) AND  
  (dbo.activityTransactions.activeYYYY = #reportYear#) 
GROUP BY dbo.company.companyName, dbo.products.prodName, dbo.products.prodID, dbo.users.fName, dbo.users.lName, dbo.best.best, dbo.forecast.forecast 
</cfquery>


<!---start graph functins--->

<!--- dashboard.cfm has passed us a property animate. We request that. --->
<cfparam name="URL.animate" default="1">
<cfset animateChart = URL.animate>

     <cfset currentDate = #DateFormat(Now(), "mm/dd/yyyy")#>
<!--- Generate the chart element --->
<cfset strXML = "<chart caption='#getActivityRelate.fname# #getActivityRelate.lname#' subCaption='Current Activity as of #currentDate#' formatNumberScale='0' numberSuffix=' sales' animation=' " & animateChart & "'>">

<!--- Iterate through each product --->

<cfloop query="getActivityRelate">
   <cfset prodName = getActivityRelate.ProdName>
   <cfset activityCount = getActivityRelate.activityCnt>
   <!--- Generate <set label='..' value='..'/> --->
   <cfset strXML = strXML & "<set label='#prodName#' value='#activityCount#' />">
</cfloop>

<!--- Finally, close <chart> element --->
<cfset strXML = strXML & "</chart>">
<cfoutput>#renderChart("Charts/Column3D.swf", "", strXML, "FactorySum", 600, 300, false, false)#</cfoutput>

<!--- The above code returns thsi xml --->
<chart caption='fname lname' subCaption='Current Activity as of 12/18/2009 formatNumberScale='0' numberSuffix=' sales' animation='1'>
  <set label="Product1" value="462" /> 
  <set label="Product2" value="857" /> 
  <set label="Product3" value="671" /> 
  <set label="Product4" value="494" /> 
  <set label="Product5" value="761" /> 
</chart>

<!--- I need to output the xml something like this example---->
<chart palette='1' caption='Sales' shownames='1' showvalues='0' numberPrefix='$' sYAxisValuesDecimals='2' connectNullData='0' PYAxisName='Revenue' SYAxisName='Quantity'  numDivLines='4' formatNumberScale='0'>
<categories>
<category label='currentMonth' />
<category label='lastmonth' />
</categories>
<dataset seriesName='ProdName' color='AFD8F8' showValues='0'>
<set value='232' />
</dataset>
<dataset seriesName='Best' color='F6BD0F' showValues='0' >
<set value=240' /> 
</dataset>
<dataset seriesName='ForeCast' color='8BBA00' showValues='0' parentYAxis='S' >
<set value='45000' />
</dataset>
</chart>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 13 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros