Link to home
Start Free TrialLog in
Avatar of RussoMA
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.
Avatar of mosphat
mosphat

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>
Avatar of RussoMA

ASKER

And column headings to match the query?
Avatar of RussoMA

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.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>
Avatar of RussoMA

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.
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>
Avatar of RussoMA

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.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>
Avatar of RussoMA

ASKER

i think that will do it, i will check it first thing tomorrow, already off that for the time being.
ASKER CERTIFIED SOLUTION
Avatar of mosphat
mosphat

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RussoMA

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
It's not my code that is duplicating.
What else did you code around it?
Avatar of RussoMA

ASKER

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>
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">
Avatar of RussoMA

ASKER

thanks, that was it.