RussoMA
asked on
Access Crosstab -> Excel via CF table.
I have an Access Crosstab query that I need to output into a ColdFusion table directly exported into Excel via cfheader/cfcontent.
I need to output a table with dynamic column headers because the crosstab query sometimes will only have 1 column, and sometimes it will have ~10 columns depending on the data.
ie)
Office | Cat1 | Cat2 | Cat3
-------------------------- --------
Loc1 | 744 | 7664 | 273
-------------------------- --------
Loc2 | 346 | 2323 | 2322
-------------------------- --------
etc, with Cat1, Cat2, Cat3... being dynamic based on the query.
I need to output a table with dynamic column headers because the crosstab query sometimes will only have 1 column, and sometimes it will have ~10 columns depending on the data.
ie)
Office | Cat1 | Cat2 | Cat3
--------------------------
Loc1 | 744 | 7664 | 273
--------------------------
Loc2 | 346 | 2323 | 2322
--------------------------
etc, with Cat1, Cat2, Cat3... being dynamic based on the query.
ASKER
And column headings to match the query?
ASKER
also i want to be able to have the office column always be the leftmost column, there will always be data in that column...
You mean something like this?
<table border="1">
<tr>
<th>Office</th>
<cfloop from="1" to="#listLen(queryname.col umnlist)#" index="i">
<th>Cat#i#</th>
</cfloop>
</tr>
<cfloop from="1" to="#queryname.recordcount #" index="i">
<tr>
<td>Loc#i#</td>
<cfloop list="#queryname.columnlis t#" index="column">
<td>#queryname[column][i]# </td>
</cfloop>
</tr>
</cfloop>
</table>
<table border="1">
<tr>
<th>Office</th>
<cfloop from="1" to="#listLen(queryname.col
<th>Cat#i#</th>
</cfloop>
</tr>
<cfloop from="1" to="#queryname.recordcount
<tr>
<td>Loc#i#</td>
<cfloop list="#queryname.columnlis
<td>#queryname[column][i]#
</cfloop>
</tr>
</cfloop>
</table>
ASKER
actually i want to be able to pull in the column headings from the query itself..
they are things like:
InventoryError, WasteError, BillingError, etc...
i want to have this be as little formatting as possible after exporting the table.
they are things like:
InventoryError, WasteError, BillingError, etc...
i want to have this be as little formatting as possible after exporting the table.
Replace this:
<cfloop from="1" to="#listLen(queryname.col umnlist)#" index="i">
<th>Cat#i#</th>
</cfloop>
with this:
<cfloop list="#queryname.columnlis t#" index="column">
<th>#column#</th>
</cfloop>
<cfloop from="1" to="#listLen(queryname.col
<th>Cat#i#</th>
</cfloop>
with this:
<cfloop list="#queryname.columnlis
<th>#column#</th>
</cfloop>
ASKER
so how do i make the office column always be the leftmost column. everything else good so far. office is the leftmost column in the crosstab query...
Ow, now I understand what you mean. I think...
<cfset officeIndex = listFindNoCase(queryname.c olumnlist, "office")>
<table border="1">
<tr>
<th>Office</th>
<cfloop list="#queryname.columnlis t#" index="column">
<cfif i neq officeIndex>
<th>#column#</th>
</cfif>
</cfloop>
</tr>
<cfloop from="1" to="#queryname.recordcount #" index="i">
<tr>
<td>#queryname[column][off iceIndex]# </td>
<cfloop list="#queryname.columnlis t#" index="column">
<cfif i neq officeIndex>
<td>#queryname[column][i]# </td>
</cfif>
</cfloop>
</tr>
</cfloop>
</table>
<cfset officeIndex = listFindNoCase(queryname.c
<table border="1">
<tr>
<th>Office</th>
<cfloop list="#queryname.columnlis
<cfif i neq officeIndex>
<th>#column#</th>
</cfif>
</cfloop>
</tr>
<cfloop from="1" to="#queryname.recordcount
<tr>
<td>#queryname[column][off
<cfloop list="#queryname.columnlis
<cfif i neq officeIndex>
<td>#queryname[column][i]#
</cfif>
</cfloop>
</tr>
</cfloop>
</table>
ASKER
i think that will do it, i will check it first thing tomorrow, already off that for the time being.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
last one seems to do the trick... BUT it does it twice?:
OFFICE BILLINGERROR
Summerside 4
Dryford 300
OFFICE BILLINGERROR
Summerside 4
Dryford 300
OFFICE BILLINGERROR
Summerside 4
Dryford 300
OFFICE BILLINGERROR
Summerside 4
Dryford 300
It's not my code that is duplicating.
What else did you code around it?
What else did you code around it?
ASKER
verbatim:
<cfoutput query="showresults">
<cfset officeIndex = listFindNoCase(showresults .columnlis t, "office")>
<cfset list = listAppend(listGetAt(showr esults.col umnlist, officeIndex), listDeleteAt(showresults.c olumnlist, officeIndex))>
<table border="1">
<tr>
<cfloop list="#list#" index="column">
<th>#column#</th>
</cfloop>
</tr>
<cfloop from="1" to="#showresults.recordcou nt#" index="i">
<tr>
<cfloop list="#list#" index="column">
<td>#showresults[column][i ]#</td>
</cfloop>
</tr>
</cfloop>
</table>
</cfoutput>
<cfoutput query="showresults">
<cfset officeIndex = listFindNoCase(showresults
<cfset list = listAppend(listGetAt(showr
<table border="1">
<tr>
<cfloop list="#list#" index="column">
<th>#column#</th>
</cfloop>
</tr>
<cfloop from="1" to="#showresults.recordcou
<tr>
<cfloop list="#list#" index="column">
<td>#showresults[column][i
</cfloop>
</tr>
</cfloop>
</table>
</cfoutput>
Get rid of the query="showresults" in <cfoutput query="showresults">. The query is already outputted in this part: <cfloop from="1" to="#showresults.recordcou nt#" index="i">
ASKER
thanks, that was it.
It would be something like this:
<table border="1">
<cfloop from="1" to="#queryname.recordcount
<tr>
<cfloop list="#queryname.columnlis
<td>#queryname[column][i]#
</cfloop>
</tr>
</cfloop>
</table>