Grouping Table Problem

[szeiss]

Using CF8. I'm creating a report using cfdocument what will export as a PDF. I want the report to look like the following:

[b]Vendors Within the City[/b]
  [i]comm_cd sub-heading[/i]
    vendor Name     address    loc_code
    vendor Name     address    loc_code

  [i]comm_cd sub-heading[/i]
    vendor Name     address    loc_code

  [i]comm_cd sub-heading[/i]
    vendor Name     address    loc_code
    vendor Name     address    loc_code
    vendor Name     address    loc_code

[b]Vendors Outside the City[/b]
  [i]comm_cd sub-heading[/i]
    vendor Name     address    loc_code
    vendor Name     address    loc_code

  [i]comm_cd sub-heading[/i]
    vendor Name     address    loc_code

  [i]comm_cd sub-heading[/i]
    vendor Name     address    loc_code
    vendor Name     address    loc_code
    vendor Name     address    loc_code

The loc_code is what determines which grouping the record will be in. Here is the code I'm working with so far without the second heading...not sure where to put it.

My query that is being called from the switch

	<cfquery dbtype="query" name="getVendor">
	SELECT DISTINCT
			comm_cd,
			comm_desc,
			vend10,
			mbe,
			gender,
			ethnic,
			lgl_nm,
			street_address,
			city,
			state,
			zip,
			loc_code,	
			phone,
			fax
	FROM
			variables.savedlist
	ORDER BY
			comm_cd, lgl_nm			
	</cfquery>

<cfscript>
	variables.location = 0;
</cfscript>	

<cfif listFind('LO,AU,SL',getvendor.loc_code)>
	<cfset variables.location = 1 />
</cfif>	

<cfif variables.location EQ 1>	
		<div style="text-align:left;font-size:10pt;font-weight:bold;font-family:arial;">Vendors Within the City</div> 
<cfoutput query="getvendor" group="comm_cd">	
	<table>
		<div style="width: 100%; border: 1px; table-layout:fixed;">
		<colgroup>
                <col align="left" style="width:400px;"/>
                <col align="left" style="width:400px;"/>				
                <col align="left" style="width:300px;"/>
                <col align="left" style="width:300px;"/>
                <col align="left" style="width:300px;"/>
                <col align="left" style="width:300px;"/>								
        </colgroup>
	<tbody>		
	<tr>	
		<td colspan="6"><div style="text-align:left;font-size:8pt;font-weight:bold;font-family:arial;">#getVendor.COMM_CD#&nbsp;&nbsp;#getVendor.comm_desc#</div></td>
	</tr>	
<cfoutput>	
	<tr>
		<td><div style="text-align:left;font-size:8pt;font-family:arial;">&nbsp;</div></td>		
		<td><div style="text-align:left;font-size:8pt;font-family:arial;">#getVendor.VEND10#<br>#getVendor.LGL_NM#<br>#getVendor.street_address#<br>#getVendor.city# #getVendor.state# #getVendor.zip#</div></td>
		<td><div style="text-align:left;font-size:8pt;font-family:arial;">#getVendor.phone#<br>#getVendor.fax#</div></td>
		<td><div style="text-align:left;font-size:8pt;font-family:arial;">#getVendor.MBE#</div></td>
		<td><div style="text-align:left;font-size:8pt;font-family:arial;">#getVendor.GENDER#/#getVendor.ETHNIC#</div></td>	
		<td><div style="text-align:left;font-size:8pt;font-family:arial;">#getVendor.loc_code#</div></td>								
	</tr>
</cfoutput>	
	<tr>&nbsp;</tr>		
	</tbody>	
		</div>				
	</table>
</cfoutput>	
</cfif>

What am I doing wrong?

Thanks

 

[SBonfiglio]

I can't understand your problem.
Is it only that you can't obtain the precise layout that you show in the output example ?
For instance where do you set the variable variables.location ?
It seems to me that loc_code can contain only one value (distinct) per record, not a list of values, unless you concatenate them
I suggest you to dump the query and start from the values that you want to group on.
Then:
1. determine what are the values of loc_codes you want display and put them in a list.
2. loop over the list
3. display the header of the group
4. grand lop over the whole query and display only the records that match the loc_code you have in the loop(2)
5. close the loop(2)
Hope it helps.

Sergio
P.S.
Too many divs that CFDocument hates as the devil...
Use:

<CFSEt cellstyle="text-align:left;font-size:8pt;font-family:arial;">
<td style="#cellstyle#">

notation instead. Quicker, better modifiable.

 

[cfSearching]

Not quite sure I understand the problem either. However, why not just use a CASE to establish the header/subheader groups, like I mentioned in your other thread? Then sort by those values and use a nested cfoutput group.

Assuming "comm_cd" is the subheader value:

<cfquery name="yourQuery" ...>
SELECT  VendorName 
       , comm_cd
       , comm_desc
       , CASE 
              WHEN loc_code IN ('LO','AU','S') THEN 1
              ELSE 2
         END AS VendorGroup
FROM   YourTable
ORDER BY VendorGroup, comm_cd, VendorName 
</cfquery>

<cfoutput query="yourQuery" group="HeaderGroup">
      <!--- headers --->
      <cfif VendorGroup eq 1>
             Vendors Within the City
      <cfelse>
              Vendors Outside the City
      </cfif>
      <cfoutput group="comm_cd">
          <!--- subheaders --->
          #comm_cd# #comm_desc#
         <cfoutput>
             <!--- details --->
             #street_address# ... 
         </cfoutput>
     </cfoutput>
</cfoutput>

----------------------------------

http://cfsearching.blogspot.com/