Skip to main content
N
Nystix
Participating Frequently
April 19, 2006
Question

Assistance with an online report

  • April 19, 2006
  • 5 replies
  • 832 views
Hey everyone,

I've developed a reporting application, but can't get the display to work. What I want it to do is display the results, based off the name (as it does now), but in ONE row. For example, currently "Connectivity" displays twice. Once for September, once for October. I want it to display once with September and October on the same line.

How can I accomplish that?

Thanks for any/all assistance you can provide!

Here's what I've got right now:
View Screenshot here
    This topic has been closed for replies.

    5 replies

    P
    philh
    Inspiring
    May 2, 2006
    You're trying to create what's known as a pivot table, in which the contents of a field (in this case, the month of the incident date) become fields that hold summary data. Google for "pivot table" and you can come up with a query statement that structures the data without having to jump through the CFLOOP hoop in your display logic.
    P
    philh
    Inspiring
    May 2, 2006
    You're trying to create what's known as a pivot table, in which the contents of a field (in this case, the month of the incident date) become fields that hold summary data. Google for "pivot table" and you can come up with a query statement that structures the data without having to jump through the CFLOOP hoop in your display logic.
    P
    philh
    Inspiring
    May 2, 2006
    You're trying to create what's known as a pivot table, in iwhich the contents of a field (in this case, the month of the incident date) become fields that hold summary data. Google for "pivot table" and you can come up with a query statement that structures the data without having to jump through the CFLOOP hoop in your display logic.
    N
    NystixAuthor
    Participating Frequently
    April 20, 2006
    Not that easy, unfortunately, then it only displays one of the records.

    MY query is as follows:

    SELECT DISTINCT DatePart('m', CFDIncident_Date) AS DisplayMe, SubjectIDName, COUNT(*) AS Count2 FROM Customers
    GROUP BY SubjectIDName, DatePart('m', CFDIncident_Date)
    ORDER BY SubjectIDName, DatePart('m', CFDIncident_Date)

    It displays like this:
    <CFOUTPUT QUERY="SubjectAndMonth" GROUP="SubjectIDName">
    <TR bgcolor="###Iif(((CurrentRow MOD 2) is 0),de('cccccc'),de('ffffff'))#">
    <TD align="center">#SubjectIDName#</TD>
    <CFLOOP INDEX="TestLoop" FROM="1" TO="12" STEP="1">
    <CFIF #TestLoop# IS #DisplayMe#>
    <TD align="center">#Count2#</TD>
    <CFELSE>
    <TD> </TD>
    </CFIF>
    </CFLOOP>
    </TR>
    </CFOUTPUT>
    D
    Dan_Bracuk
    Inspiring
    April 20, 2006
    You don't have enough cfoutput tags. The cfml reference manual has an example in the cfoutput part. If you don't have one, the internet does.
    N
    NystixAuthor
    Participating Frequently
    April 20, 2006
    Thanks for the responses so far. I'll try to clarify what I'm looking for.

    This is what I want it to do: Final Screenshot
    Note that the subjectIDName is never repeated, but the records (count, per month) are on one line.

    If I do a GROUP on the CFOUTPUT, then it displays one subjectidname, but does NOT move the second (or any after) to that same line.

    Any help is greatly appreciated.

    This is the full code, which displays current screenshot (see original post for link):
    <!---this displays everything of subject name for month--->
    <CFQUERY NAME="GetSubjects" DATASOURCE="#database#">
    SELECT DISTINCT SubjectIDName
    FROM Customers
    ORDER BY SubjectIDName
    </CFQUERY>

    <CFQUERY NAME="SubjectAndMonth" DATASOURCE="#database#">
    SELECT DISTINCT DatePart('m', CFDIncident_Date) AS DisplayMe, SubjectIDName, COUNT(*) AS Count2 FROM Customers
    GROUP BY SubjectIDName, DatePart('m', CFDIncident_Date)
    ORDER BY SubjectIDName, DatePart('m', CFDIncident_Date)
    </CFQUERY>

    <table width="75%" border="0" cellpadding="0" cellspacing="0">
    <TR>
    <TD align="left"><strong>NAME</strong></TD>
    <TD align="center"><strong>JAN</strong></TD>
    <TD align="center"><strong>FEB</strong></TD>
    <TD align="center"><strong>MAR</strong></TD>
    <TD align="center"><strong>APR</strong></TD>
    <TD align="center"><strong>MAY</strong></TD>
    <TD align="center"><strong>JUN</strong></TD>
    <TD align="center"><strong>JUL</strong></TD>
    <TD align="center"><strong>AUG</strong></TD>
    <TD align="center"><strong>SEP</strong></TD>
    <TD align="center"><strong>OCT</strong></TD>
    <TD align="center"><strong>NOV</strong></TD>
    <TD align="center"><strong>DEC</strong></TD>
    </tr>
    <CFOUTPUT QUERY="SubjectAndMonth">
    <TR bgcolor="###Iif(((CurrentRow MOD 2) is 0),de('cccccc'),de('ffffff'))#">
    <TD align="center">#SubjectIDName#</TD>
    <CFLOOP INDEX="TestLoop" FROM="1" TO="12" STEP="1">
    <CFIF #TestLoop# IS #DisplayMe#>
    <TD align="center">#Count2#</TD>
    <CFELSE>
    <TD> </TD>
    </CFIF>
    </CFLOOP>
    </TR>
    </CFOUTPUT>
    </table>
    D
    dempster
    Inspiring
    April 19, 2006
    Use the GROUP attribute in your CFOUTPUT tag to limit the display to one row per name.
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices