cfloop query display in tables

[olmos]

I am having trouble with displaying the data in a report the way that I need it.

I want it to display like this:
Show all the categories on the first row. Then show each count under the correct category and in the correct Date row. For example, in 2001 - February there is 1 event in category 2. (The date format is yy-mm for now)

Is this possible?

<TR>
<TD> DATE</TD> </TD><TD>EVENT CATEGORY 1</TD> <TD> EVENT CATEGORY 2</TD> <TD>EVENT CATEGORY 3</TD> ...
</TR>
(yy-mm)
<TR><TD>01 02</TD> <TD>1</TD>
<TR><TD>02 02</td> <TD>1</TD>

This is my current display Results is :

EVENT CATEGORY 1
EVENT CATEGORY 1 02 01 2
EVENT CATEGORY 1 02 02 14
EVENT CATEGORY 1 02 03 5
EVENT CATEGORY 1 02 04 1

EVENT CATEGORY 2
EVENT CATEGORY 2 01 02 1
EVENT CATEGORY 2 02 02 1


This is my query:


<CFQUERY name=&quot;categories&quot; datasource=&quot;#PrimaryDataSource#&quot;>

SELECT eventcategory_id

FROM incidentcategory

</CFQUERY>

<table>

<CFOUTPUT>
<CFLOOP query=&quot;categories&quot;>


<CFQUERY name=&quot;category_by_month_test&quot; datasource=&quot;#PrimaryDataSource#&quot;>

SELECT count(e.event_id) as &quot;event_cat_total&quot;, count(to_char(e.notification_date, 'YY MM')) as &quot;cat_counts&quot;,
to_char(e.notification_date, 'YY MM') as &quot;eachMonth&quot;, e.eventcategory_id
FROM event e, eventcategory c
WHERE e.eventcategory_id = c.eventcategory_id
AND e.eventcategory_id = '#eventcategory_id#'
GROUP BY to_char(notification_date, 'YY MM'), e.eventcategory_id
ORDER BY &quot;eachMonth&quot;, e.eventcategory_id

</CFQUERY>

<tr>
<td><b>EVENT CATEGORY #eventcategory_id#</b></td>
</tr>

<CFLOOP QUERY = &quot;category_by_month_test&quot;>
<tr>
<td><b>event CATEGORY #eventcategory_id#</b></td>
<td>#eachMonth# </td>
<td>#cat_counts#</td>

</CFLOOP>
</tr>

</CFLOOP>
</CFOUTPUT>

</table>

</CFLOOP>
</CFOUTPUT>

<CFOUTPUT>

Thanks in advance,
olmos.

 

[CFDude]

It seems as though you could make this a lot easier on yourself and the server, by creating one query that grabs all of the data and use the &quot;group&quot; attribute of the cfoutput tag to do the same thing:

e.g.
<CFQUERY name=&quot;category_by_month_test&quot; datasource=&quot;#PrimaryDataSource#&quot;>

SELECT count(e.event_id) as &quot;event_cat_total&quot;, count(to_char(e.notification_date, 'YY MM')) as &quot;cat_counts&quot;,
to_char(e.notification_date, 'YY MM') as &quot;eachMonth&quot;, e.eventcategory_id
FROM event e, eventcategory c
WHERE e.eventcategory_id = c.eventcategory_id
GROUP BY to_char(notification_date, 'YY MM'), e.eventcategory_id
ORDER BY &quot;eachMonth&quot;, e.eventcategory_id

</CFQUERY>


<cfoutput query=&quot;category_by_month_test&quot; group=&quot;eventcategory_id&quot;>
<tr>
<td colspan=&quot;3&quot;><b>EVENT CATEGORY #eventcategory_id#</b></td>
</tr>
<!--- Note the nested cfoutputs --- >
<cfoutput>
<tr>
<td><b>event CATEGORY #eventcategory_id#</b></td>
<td>#eachMonth# </td>
<td>#cat_counts#</td>
</tr>

</cfoutput>
</cfoutput>

By using the Order By clause in your query, you can use those fields to group by in your cfoutput.