I guess I should have been more specific, as getting it into excel wasn't
the issue so much as formating the data in cf. But, I've figured out a
solution that involved creating a view ordered by county, then querying
against the view to get items and dates and looping through those to get
totals - works great as long as all counties have data for all years, which
works in this case:
Here's the whole thing:
<cfsetting enablecfoutputonly="yes">
<cfquery name="getcounty" password="#pword#" username="#uname#"
datasource="#dsn#">
SELECT distinct(name)
FROM flpweb.cfs_view_foodstamp
ORDER BY name
</cfquery>
<cfquery name="getfs" password="#pword#" username="#uname#"
datasource="#dsn#">
SELECT distinct item, datecollected
FROM flpweb.cfs_view_foodstamp
ORDER BY item, datecollected
</cfquery>
<CFHEADER NAME="Content-Disposition" VALUE="inline; filename=data.csv">
<CFCONTENT TYPE="application/excel">
<cfoutput>Item,Date,#valuelist(getcounty.name)#
</cfoutput>
<cfoutput query="getfs">
<cfquery name="gettotal" password="#pword#" username="#uname#"
datasource="#dsn#">
SELECT total FROM flpweb.cfs_view_foodstamp WHERE item =
'#getfs.item[currentrow]#' AND datecollected =
'#dateformat(getfs.datecollected[currentrow], "dd-mmm-yyyy")#' ORDER BY name
</cfquery>#getfs.item[currentrow]#,#getfs.datecollected[currentrow]#,#valuel
ist(gettotal.total)#
</cfoutput>
<cfsetting enablecfoutputonly="no">
Deanna Schneider
Interactive Media Developer
[EMAIL PROTECTED]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
