• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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.
0
RussoMA
Asked:
RussoMA
  • 8
  • 7
1 Solution
 
mosphatCommented:
There's a variable cold queryname.columnlist you can use.
It would be something like this:

<table border="1">
<cfloop from="1" to="#queryname.recordcount#" index="i">
  <tr>
    <cfloop list="#queryname.columnlist#" index="column">
      <td>#queryname[column][i]#</td>
    </cfloop>
  </tr>
</cfloop>
</table>
0
 
RussoMAAuthor Commented:
And column headings to match the query?
0
 
RussoMAAuthor Commented:
also i want to be able to have the office column always be the leftmost column, there will always be data in that column...
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mosphatCommented:
You mean something like this?

<table border="1">
  <tr>
    <th>Office</th>
    <cfloop from="1" to="#listLen(queryname.columnlist)#" index="i">
      <th>Cat#i#</th>
    </cfloop>
  </tr>
<cfloop from="1" to="#queryname.recordcount#" index="i">
  <tr>
    <td>Loc#i#</td>
    <cfloop list="#queryname.columnlist#" index="column">
      <td>#queryname[column][i]#</td>
    </cfloop>
  </tr>
</cfloop>
</table>
0
 
RussoMAAuthor Commented:
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.
0
 
mosphatCommented:
Replace this:
<cfloop from="1" to="#listLen(queryname.columnlist)#" index="i">
      <th>Cat#i#</th>
    </cfloop>

with this:
<cfloop list="#queryname.columnlist#" index="column">
      <th>#column#</th>
    </cfloop>
0
 
RussoMAAuthor Commented:
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...
0
 
mosphatCommented:
Ow, now I understand what you mean. I think...

<cfset officeIndex = listFindNoCase(queryname.columnlist, "office")>
<table border="1">
  <tr>
    <th>Office</th>
    <cfloop list="#queryname.columnlist#" index="column">
      <cfif i neq officeIndex>      
        <th>#column#</th>
      </cfif>
    </cfloop>
  </tr>
<cfloop from="1" to="#queryname.recordcount#" index="i">
  <tr>
    <td>#queryname[column][officeIndex]#</td>
    <cfloop list="#queryname.columnlist#" index="column">
       <cfif i neq officeIndex>
      <td>#queryname[column][i]#</td>
      </cfif>
    </cfloop>
  </tr>
</cfloop>
</table>
0
 
RussoMAAuthor Commented:
i think that will do it, i will check it first thing tomorrow, already off that for the time being.
0
 
mosphatCommented:
This is a little more efficient.
It makes sure the 'office' column is first in line.

<cfset officeIndex = listFindNoCase(queryname.columnlist, "office")>
<cfset list = listAppend(listGetAt(queryname.columnlist, officeIndex), listDeleteAt(queryname.columnlist, officeIndex))>
<table border="1">
  <tr>
    <cfloop list="#list#" index="column">
      <th>#column#</th>
    </cfloop>
  </tr>
<cfloop from="1" to="#queryname.recordcount#" index="i">
  <tr>
    <cfloop list="#list#" index="column">
      <td>#queryname[column][i]#</td>
    </cfloop>
  </tr>
</cfloop>
</table>
0
 
RussoMAAuthor Commented:
last one seems to do the trick... BUT it does it twice?:

OFFICE                 BILLINGERROR
Summerside          4
Dryford                 300

OFFICE                 BILLINGERROR
Summerside          4
Dryford                 300
0
 
mosphatCommented:
It's not my code that is duplicating.
What else did you code around it?
0
 
RussoMAAuthor Commented:
verbatim:

<cfoutput query="showresults">

<cfset officeIndex = listFindNoCase(showresults.columnlist, "office")>
<cfset list = listAppend(listGetAt(showresults.columnlist, officeIndex), listDeleteAt(showresults.columnlist, officeIndex))>
<table border="1">
  <tr>
    <cfloop list="#list#" index="column">
      <th>#column#</th>
    </cfloop>
  </tr>
<cfloop from="1" to="#showresults.recordcount#" index="i">
  <tr>
    <cfloop list="#list#" index="column">
      <td>#showresults[column][i]#</td>
    </cfloop>
  </tr>
</cfloop>
</table>

</cfoutput>
0
 
mosphatCommented:
Get rid of the query="showresults" in <cfoutput query="showresults">. The query is already outputted in this part: <cfloop from="1" to="#showresults.recordcount#" index="i">
0
 
RussoMAAuthor Commented:
thanks, that was it.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now