display blocks of records

[weblover]

Hi i've some records which needs to be displayed by block according to the
serial no.. here's the below one.

sl.no -- name -- school -- date
==============================
100 -- abc -- blah -- 10/11/1999
100 -- abc -- blah -- 10/11/1999
101 -- abc -- blah -- 10/11/1999
101 -- abc -- blah -- 10/11/1999
101 -- abc -- blah -- 10/11/1999
102 -- abc -- blah -- 10/11/1999
102 -- abc -- blah -- 10/11/1999
102 -- abc -- blah -- 10/11/1999
103 -- abc -- blah -- 10/11/1999
103 -- abc -- blah -- 10/11/1999
103 -- abc -- blah -- 10/11/1999
103 -- abc -- blah -- 10/11/1999
103 -- abc -- blah -- 10/11/1999
======================================

i need to display like this below...

sl.no -- name -- school -- date
==============================
100 -- abc -- blah -- 10/11/1999
100 -- abc -- blah -- 10/11/1999

101 -- abc -- blah -- 10/11/1999
101 -- abc -- blah -- 10/11/1999
101 -- abc -- blah -- 10/11/1999

102 -- abc -- blah -- 10/11/1999
102 -- abc -- blah -- 10/11/1999
102 -- abc -- blah -- 10/11/1999

103 -- abc -- blah -- 10/11/1999
103 -- abc -- blah -- 10/11/1999
103 -- abc -- blah -- 10/11/1999
103 -- abc -- blah -- 10/11/1999
103 -- abc -- blah -- 10/11/1999
======================================

can anybody pls.help...

[The ScareCrow]

Assuming your query has columns (sl.no, name, school, date)

<cfoutput query="qryName" group="sl.no">
<cfoutput>
#sl.no# -- #name# -- #school# -- #date#<br>
</cfoutput>
<br>
</cfoutput>

Ken

[mxstu]

Assuming you're using a query, one method is to use the GROUP attribute. Just
make sure to order your query by the serial no. column.

This example prints the headers before each block, which is easier to read
IMO, but you can modify it as needed.




<!--- Need to add date formatting to output --->
<cfquery name="getData" datasource="myAccessDSN">
SELECT SerialNumber, School, Name, YourDate
FROM YourTable
ORDER BY SerialNumber
</cfquery>

<cfoutput query="getData" group="serialNumber">
<table border="0">
<tr>
<td>SerialNumber</td>
<td>School</td>
<td>Name</td>
<td>YourDate</td>
</tr>
<cfoutput>
<tr>
<td>#SerialNumber#</td>
<td>#School#</td>
<td>#Name#</td>
<td>#YourDate#</td>
</tr>
</cfoutput>
</table>
<br>
</cfoutput>

[weblover]

but GROUP BY Displays only 4 records...it doesn't display all records... if i do group by. pls.advise...

[The ScareCrow]

Post your code

Ken

[weblover]

Code is pretty big..pls.bare with it..
=============================

<cfquery name="Report_Card_Data" datasource="#application.dsn#">

SELECT
Report_Projects_V.Request_ID, Report_Projects_V.OriginalComplete,
Report_Projects_V.RevisedComplete,
Report_Projects_V.ActualComplete, Report_Projects_V.HourlyCost,
Report_Projects_V.FunctionalArea_Name, Report_Projects_V.Team_Name,
Report_Projects_V.User_Name, Report_Projects_V.pctStatus_Name,
Report_Projects_V.ActualHours, Report_Projects_V.Sequence_ID,
Report_Projects_V.dept_name, Report_Projects_V.div_name,
Report_Projects_V.EVPOwnerName, Report_Projects_V.BusnOwnerName,
Requests_V.Request_ID, Requests_V.CustomerName, Requests_V.Title
FROM
Report_Projects_V INNER JOIN Requests_V ON
Report_Projects_V.Request_ID = Requests_V.Request_ID
WHERE 0=0

<!--- business owner name/evp owner name/functional area name --->
<cfif URL.key EQ "2">
AND BusnOwnername in ('#getvalue1#')
<cfelseif URL.key eq "3">
AND EvpOwnerName in ('#getvalue1#')
<cfelseif URL.key eq "4">
AND FunctionalArea_name in ('#getvalue1#')
</cfif>

<!--- duedate between dates. --->
<cfif isdefined("url.prompt2") and isdefined("url.prompt3") and url.prompt2
neq "" and url.prompt2 neq "">
AND convert(char(10),RevisedComplete,101) between '#url.prompt2#' and
'#url.prompt3#'
</cfif>

<!--- complete date between dates. --->
<cfif isdefined("url.prompt4") and isdefined("url.prompt5") and url.prompt4
neq "" and url.prompt5 neq "">
AND convert(char(10),ActualComplete,101) between '#url.prompt4#' and
'#url.prompt5#'
</cfif>

<!--- open requests --->
<cfif isdefined("url.prompt6") and url.prompt6 eq "on">
OR PCTSTATUS_NAME = 'ACTIVE'
</cfif>

<!--- closed requests --->
<CFIF isdefined("url.prompt7") and URL.PROMPT7 EQ "on">
OR PCTSTATUS_NAME = 'COMPLETE'
</CFIF>

<!--- customer name --->
<cfif isdefined("url.prompt8") and URL.PROMPT8 NEQ "">
AND CUSTOMERNAME like '%#TRIM(URL.PROMPT8)#%'
</cfif>

<!--- request numbers --->
<cfif isdefined("url.prompt9") and url.prompt9 neq "">
AND Report_Projects_V.Request_ID IN (#url.prompt9#)
</cfif>

and Report_Projects_V.Request_ID between 2000 and 3000

<cfif URL.prompt1 EQ "BusnOwnPri">
ORDER BY BusnOwnerName

<cfelseif URL.prompt1 EQ "Dept">
ORDER BY DEPT_NAME

<cfelseif URL.prompt1 EQ "Divn">
ORDER BY DIV_NAME

<cfelseif URL.prompt1 EQ "FuncArea">
ORDER BY Functionalarea_Name

<cfelseif URL.prompt1 EQ "RqstNbr">
ORDER BY REQUEST_ID
</cfif>

</cfquery>


<style type="text/css">
<!--
.style1 {
font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.style2 {
font-size: 12px;
font-weight: bold;
font-family: Arial, Helvetica, sans-serif;
}
.style3 {font-size: 12px}
.style4 {font-family: Arial, Helvetica, sans-serif}
-->
</style>
<cfoutput>

<table width="100%" border="1">
<tr>
<td colspan="4" bgcolor="##1F5E92"><img src="portal_title.gif" width="700"
height="71"></td>
</tr>
</table>
<table width="100%" >
<tr>
<td width="30%" class="TD" valign="top">#Dateformat(now(),"mmm dd yyyy")#
#Timeformat(now(),"HH:MM:SS tt")# #sortname#</td>
<td width="70%" class="TD" valign="top"><div align="left">
<p><strong><font face="Arial, Helvetica, sans-serif" size="+2"> Customer
Report Card</font></strong> </p>
<p>&nbsp;</p>
</div>
</td>
</tr>
<tr class="textlook">
<td colspan="4"><!--- #RptSelection# ---></td>
</tr>
</cfoutput>

<cfset sum_ActualHours = 0>
<cfset sum_Actualcost = 0 >

<cfset grand_ActualHours = 0 >
<cfset grand_Actualcost = 0 >

<cfoutput query="Report_Card_Data">

</table>
<table width="100%" border="0">
<tr align="left" class="style2">
<td width="25%">EVP : #EVPOwnerName# </td>
<td width="25%">Business Owner : #BusnOwnerName# </td>
<td width="29%">Functional Area : #FunctionalArea_Name#</td>
<td width="21%">Customer : #CustomerName#</td>
</tr>
<tr align="left" class="style2">
<td width="25%">Request No : #request_id#</td>
<td colspan="3">Request Title : #title# </td>
</tr>
</table>
</tr>
</table>
</span>

<table width="100%" border="0">
<tr class="TD">
<td width="8%">Proj.No</td>
<td width="10%">Due Date </td>
<td width="10%">Completion Date </td>
<td width="23%">Resource</td>
<td width="25%">Team Name </td>
<td width="11%">Status</td>
<td width="5%">Hours</td>
<td width="8%">Cost</td>
</tr>

<CFIF len(ACTUALHOURS) EQ 0>
<CFSET ACTUALHOURS = 0>
</CFIF>

<CFIF len(HourlyCost) EQ 0>
<CFSET HourlyCost = 0>
</CFIF>

<cfif HourlyCost EQ 0 >
<CFSET ACTUALCOST = 65 * ActualHours>
<CFELSEIF ACTUALHOURS GTE 0 AND HOURLYCOST GTE 0 >
<CFSET ACTUALCOST = HOURLYCOST * ACTUALHOURS>
<cfelse>
<CFSET ACTUALCOST = 0>
</cfif>


<tr class="listbox">
<td>#Request_ID# - #Sequence_ID# </td>
<td>#dateformat(RevisedComplete,"mm/dd/yyyy")#</td>
<td>#dateformat(ActualComplete,"mm/dd/yyyy")#</td>
<td>#User_Name#</td>
<td>#Team_Name#</td>
<td>#pctStatus_Name#</td>
<td>#decimalformat(ActualHours)#</td>
<td align="right">#dollarformat(ACTUALCOST)#</td>
</tr>


<CFIF ACTUALHOURS GTE 0>
<cfset sum_ActualHours = sum_ActualHours + ActualHours >
<cfset grand_ActualHours = grand_ActualHours + ActualHours >
</CFIF>
<cfset sum_Actualcost = sum_Actualcost + ACTUALCOST >
<cfset grand_Actualcost = grand_Actualcost + ACTUALCOST >

<tr class="listbox">
<td colspan="3"></td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td ><div align="right">Total:</div></td>
<td>#decimalformat(sum_ActualHours)#</td>
<td align="right">#dollarformat(sum_Actualcost)#</td>
</tr>

<cfset sum_ActualHours = 0 >
<cfset sum_Actualcost =0 >


</cfoutput>
<cfoutput>
<tr class="listbox">
<td colspan="3">&nbsp;</td>
<td>&nbsp;</td>
<td>&nbsp;</td>
<td>Grand Total: </td>
<td>#decimalformat(grand_ActualHours)#</td>
<td align="right">#dollarformat(grand_Actualcost)#</td>
</tr>
</cfoutput>
</table>

[mxstu]

weblover,

GROUP is an attribute of CFOUTPUT. This is not the same as a GROUP BY clause
of query statement. Perhaps that's what you're thinking of. If you take a
look at the first two responses in this thread, you will see two examples that
should be what you're looking for.

[Ro]

User the GROUP Attribute.

There is something that almost everybody forgets to explain whenusing
group in the output and that it's necessary to nest an output where
the repetitive group is going to show the sub items in the group.

e.g.

<cfoutput query="RecordSet" Group="SerialNo">

<h1>#SerialNo#</h1>
<cfoutput>
<p>#SerialNo#</p>
<p>#School#</p>
<p>#Name#</p>
<p>#Date#</p>
<cfoutput>
<p>&nbsp;</p> <!---This extra line will allow for the break
between the groups --->

</cfoutput>

On Tue, 7 Jun 2005 23:35:13 +0000 (UTC), "mxstu"
<webforumsuser@macromedia.com> wrote:

>Assuming you're using a query, one method is to use the GROUP attribute. Just
>make sure to order your query by the serial no. column.
>
> This example prints the headers before each block, which is easier to read
>IMO, but you can modify it as needed.
>
>
>
>
> <!--- Need to add date formatting to output --->
> <cfquery name="getData" datasource="myAccessDSN">
> SELECT SerialNumber, School, Name, YourDate
> FROM YourTable
> ORDER BY SerialNumber
> </cfquery>
>
> <cfoutput query="getData" group="serialNumber">
> <table border="0">
> <tr>
> <td>SerialNumber</td>
> <td>School</td>
> <td>Name</td>
> <td>YourDate</td>
> </tr>
> <cfoutput>
> <tr>
> <td>#SerialNumber#</td>
> <td>#School#</td>
> <td>#Name#</td>
> <td>#YourDate#</td>
> </tr>
> </cfoutput>
> </table>
> <br>
> </cfoutput>