Hi, This is my first try at displaying crosstab results. I'm almost done. I have the columns and rows properly labeled and displayed. Now I just need to fill the cells with the data.
The last part with the ## near the end is where I will display the value. It sould be a count of SpecPK, but I can't get CF to allow it.
Here is what I have so far:
<cfquery Name="CrossSpec" Datasource="JVBB">
TRANSFORM Count(SpecPK) AS CountOfSpecPK
SELECT ReasonforDiscard
FROM UnacceptableSpecimens
GROUP BY ReasonforDiscard
PIVOT Format([SDate], 'yyyy')
</cfquery>
<body bgcolor="#FFFFFF" text="#000000">
<cfset list_services = ListDeleteAt(CrossSpec.ColumnList, ListFindNoCase(CrossSpec.ColumnList, "ReasonforDiscard"
) >
<table border="1" cellspacing="0">
<!---- output column headers ----->
<tr>
<td><b><font size="3" color="#990000">Reason for Discard</font></b></td>
<cfloop list="#list_services#" index="Col">
<td><b><font size="3" color="#990000"><cfoutput>#Col#</cfoutput></font></b></td>
</cfloop>
</tr>
<cfoutput query="CrossSpec">
<tr>
<td>#ReasonforDiscard#</td>
<cfloop list="#list_services#" index="Col">
<td>##</td>
</cfloop>
</tr>
</cfoutput>
</table>
_____________
Cliff
The last part with the ## near the end is where I will display the value. It sould be a count of SpecPK, but I can't get CF to allow it.
Here is what I have so far:
<cfquery Name="CrossSpec" Datasource="JVBB">
TRANSFORM Count(SpecPK) AS CountOfSpecPK
SELECT ReasonforDiscard
FROM UnacceptableSpecimens
GROUP BY ReasonforDiscard
PIVOT Format([SDate], 'yyyy')
</cfquery>
<body bgcolor="#FFFFFF" text="#000000">
<cfset list_services = ListDeleteAt(CrossSpec.ColumnList, ListFindNoCase(CrossSpec.ColumnList, "ReasonforDiscard"
) ><table border="1" cellspacing="0">
<!---- output column headers ----->
<tr>
<td><b><font size="3" color="#990000">Reason for Discard</font></b></td>
<cfloop list="#list_services#" index="Col">
<td><b><font size="3" color="#990000"><cfoutput>#Col#</cfoutput></font></b></td>
</cfloop>
</tr>
<cfoutput query="CrossSpec">
<tr>
<td>#ReasonforDiscard#</td>
<cfloop list="#list_services#" index="Col">
<td>##</td>
</cfloop>
</tr>
</cfoutput>
</table>
_____________
Cliff