Is it possible to group data with a header? [Archive]

Illufox

03-29-2005, 07:41 PM

I have been asked to create different reports. For example Events by location. This is easy but now they want me to group the locations . For example all Chicago events are under a header called "Chicago" and so on. How can I set up a Recordset to create a header and then list all the locations that belong to the header below? :confused:

Current view:

Chicago Date Event Name Contact
Chicago Date Event Name Contact
Chicago Date Event Name Contact
New York Date Event Name Contact
New York Date Event Name Contact
...
....
....

Desired view:

Chicago
Date Event Name Contact
Date Event Name Contact
Date Event Name Contact

New York
Date Event Name Contact
Date Event Name Contact

...
...
...

I hope this makes sense..... :D

Thanks

buntine

03-29-2005, 08:45 PM

It should be simple. You may need to use several SQL queries, though.

I don't really know where its best to start. What are you actually stuck on?

phpnovice

03-29-2005, 10:00 PM

How can I set up a Recordset to create a header and then list all the locations that belong to the header below? :confused:
Generally, all that is required is a single query without any special considerations. You just make sure that you use the ORDER BY clause in your query and then use manual control breaks when you create the output. The only other way that I can think to do it is to use the GROUP BY clause and have SQL count the number of rows in each group as a calculated column that is added to the recordset.

However, in my opinion, using such a group count is no easier than using manual control breaks. The manual control break method requires only a single loop along with an extra variable or variables to check for when a particular column or columns change value. The group count method requires a loop within a loop and the inner loop requires a loop counter max control variable which is initially set to the group counter value. The outer loop, as with the single loop method, of course, continues until the end of the recordset is reached.

Now... If you followed all that -- you're doing real good! :D

betheball

03-29-2005, 10:05 PM

Here is an example of what you want. I am using an array with getrows, but hopefully you get the idea.

<%
Dim conntemp, myDSN, myRS, mySQL, arrPosition
Set conntemp=Server.CreateObject("ADODB.Connection")
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("fpdb/Review304.mdb")
conntemp.open myDSN
mySQL = "SELECT IDRS, InputDate, [10Volume], [10Hours] FROM Closures ORDER BY IDRS"
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, conntemp, 0, 1
arrPosition=myRS.GetRows
myRS.Close
Set myRS = nothing
conntemp.Close
Set conntemp = nothing
%>
<html>
<head>
</head>
<body>
<%
For i = 0 to Ubound(arrPosition,2)
DIM GroupName,tmpGroupName
GroupName=arrPosition(0,i)
If GroupName <> tmpGroupName Then
tmpGroupName=GroupName
Response.Write("<strong>"&tmpGroupName&"</strong><br />")
end if
Response.Write(arrPosition(1,i)&"  "&arrPosition(2,i)&"  "&arrPosition(3,i)&"<br />")
Next
%>
</body>
</html>

Modified from this tutorial:

http://www.outfront.net/spooky/adv_grouping.htm

Edit - Slow fingers. Same idea as above.

phpnovice

03-29-2005, 10:20 PM

Yes, the above method uses manual control breaks.

Illufox

03-30-2005, 12:28 PM

Thanks for your help guys! Unfortunately I'm a novice so this all sounds new to me. Here is the code I have so far:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_EventsDB_STRING
Recordset1.Source = "SELECT IdMeeting, Date_S, EventName, Location, Status, Industry FROM Meeting WHERE Date_S >= Date() ORDER BY Location"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<%
Dim MM_paramName
%>
<%
' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters

Dim MM_keepNone
Dim MM_keepURL
Dim MM_keepForm
Dim MM_keepBoth

Dim MM_removeList
Dim MM_item
Dim MM_nextItem

' create the list of parameters which should not be maintained
MM_removeList = "&index="
If (MM_paramName <> "") Then
MM_removeList = MM_removeList & "&" & MM_paramName & "="
End If

MM_keepURL=""
MM_keepForm=""
MM_keepBoth=""
MM_keepNone=""

' add the URL parameters to the MM_keepURL string
For Each MM_item In Request.QueryString
MM_nextItem = "&" & MM_item & "="
If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
MM_keepURL = MM_keepURL & MM_nextItem & Server.URLencode(Request.QueryString(MM_item))
End If
Next

' add the Form variables to the MM_keepForm string
For Each MM_item In Request.Form
MM_nextItem = "&" & MM_item & "="
If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then
MM_keepForm = MM_keepForm & MM_nextItem & Server.URLencode(Request.Form(MM_item))
End If
Next

' create the Form + URL string and remove the intial '&' from each of the strings
MM_keepBoth = MM_keepURL & MM_keepForm
If (MM_keepBoth <> "") Then
MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)
End If
If (MM_keepURL <> "") Then
MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1)
End If
If (MM_keepForm <> "") Then
MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)
End If

' a utility function used for adding additional parameters to these strings
Function MM_joinChar(firstItem)
If (firstItem <> "") Then
MM_joinChar = "&"
Else
MM_joinChar = ""
End If
End Function
%>

phpnovice

03-30-2005, 12:37 PM

Where's the loop on your recordset?

Illufox

03-30-2005, 12:42 PM

I don't have any loop set up yet since I don't understand "arrPosition" and "Ubound"....like I said, it's all new to me.....

Betheball's code is so much different from mine (Dreamweaver MX code) that it's hard for me to know how and where to insert the correct code....

Illufox

03-30-2005, 01:38 PM

I even tried to insert the code from the tutorial but it doesn't seem to work with my code. Here is the rest of the code:

<TABLE cellSpacing=0 cellPadding=1 width="600" border=0>
<TBODY>
<TR>
<TD><strong>Events by Location</strong></TD>
</TR>
<TR>
<TD vAlign=top><table width="100%" border="0" cellpadding="3" cellspacing="3">
<tr bgcolor="#CCCCCC">
<td><strong>Location</strong></td>
<td><strong>Date</strong></td>
<td><strong>Event Name</strong></td>
<td><strong>Industry</strong></td>
<td><strong>Status</strong></td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
<%If (Repeat1__numRows Mod 2) Then%>
<tr bgcolor="#EFEFEF">
<%End If%>
<td><strong><%=(Recordset1.Fields.Item("Location").Value)%></strong><br>
</td>
<td><%=(Recordset1.Fields.Item("Date_S").Value)%></td>
<td><a href="details.asp?<%= MM_keepNone & MM_joinChar(MM_keepNone) & "IdMeeting=" & Recordset1.Fields.Item("IdMeeting").Value %>"><%=(Recordset1.Fields.Item("EventName").Value)%></a></td>
<td><%=(Recordset1.Fields.Item("Industry").Value)%></td>
<td><%=(Recordset1.Fields.Item("Status").Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table></TD>
</TR>
</TBODY>
</TABLE>

Illufox

03-30-2005, 02:21 PM

Just got the answer at the Macromedia Forum:

Step 1

Above your repeat region create this line of code:

<%
Dim DisplayName, TestName
DisplayName = ""
%>

Step2

Then inside of the repeat region where you want the subcategory name to appear have this code:

<%
TestName = Recordset1.fields.item("location").value
if DisplayName <> TestName then %>

<%= TestName%>

<%
End If
DisplayName = TestName
%>

Normally you will wrap the <%=TestName%> in the other html tags that make up the line.

phpnovice

03-30-2005, 03:12 PM

Yep, that be the manual control break method.

Illufox

03-30-2005, 04:13 PM

The only problem is that I have now some data (locations) that display under the wrong category headers and those category headers are missing altogether.

It doesn't make sense to me because I wasn't able to find a misspelling..... (?)

I guess it has something to do with the statement:

Normally you will wrap the <%=TestName%> in the other html tags that make up the line. wich I don't understand...

phpnovice

03-30-2005, 05:41 PM

Normally you will wrap the <%=TestName%> in the other html tags that make up the line.
That means that you would do something like changing this that they supplied:

<%
TestName = Recordset1.fields.item("location").value
if DisplayName <> TestName then %>

<%= TestName%>

<%
End If
DisplayName = TestName
%>

to this -- if you're building a table:

<%
TestName = Recordset1.fields.item("location").value
if DisplayName <> TestName then
%>

<tr>
<th><%= TestName%></th>
<td colspan="4"> </td>
</tr>

<%
End If
DisplayName = TestName
%>

Illufox

03-30-2005, 07:47 PM

I've already done this. Here is my code:

<TABLE cellSpacing=0 cellPadding=1 width="600" border=0>
<TBODY>
<TR>
<TD> </TD>
<TD> </TD>
</TR>
<TR>
<TD> <strong><font size="3">Events by Location</font></strong></TD>
<TD> </TD>
</TR>
<TR>
<TD align="right" valign="baseline" bgcolor="#efefef">
<form name="form1" method="get" action="result3.asp">
<font size="1">Filter list by keyword:</font>
<input name="search" type="text" id="search"><input type="submit" name="Submit" value="Go!"></form></TD>
</TR>
<TR>
<TD vAlign=top><table width="100%" border="0" cellpadding="3" cellspacing="3">
<tr>
<td bgcolor="#CCCCCC"><font size="1"><strong>Date</strong></font></td>
<td bgcolor="#CCCCCC"><font size="1"><strong>Event Name</strong></font></td>
<td bgcolor="#CCCCCC"><font size="1"><strong>Industry</strong></font></td>
<td bgcolor="#CCCCCC"><font size="1"><strong>Status</strong></font></td>
</tr>
<%
Dim DisplayName, TestName
DisplayName = ""
%>
<% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
<% If (Repeat1__numRows Mod 2) Then%>
<% TestName = Recordset1.Fields.Item("location").Value
if DisplayName <> TestName then %>
<tr>
<td colspan="4" bgcolor="#EFEFEF"><strong><font size="1"><%= TestName %></font></strong></td>
</tr><%
End If
DisplayName = TestName
%>
<tr>
<%End If%>
<td><font size="1"><%=(Recordset1.Fields.Item("Date_S").Value)%></font></td>
<td><font size="1"><a href="details.asp?<%= MM_keepNone & MM_joinChar(MM_keepNone) & "IdMeeting=" & Recordset1.Fields.Item("IdMeeting").Value %>"><%=(Recordset1.Fields.Item("EventName").Value)%></a></font></td>
<td><font size="1"><%=(Recordset1.Fields.Item("Industry").Value)%></font></td>
<td><font size="1"><%=(Recordset1.Fields.Item("Status").Value)%></font></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table></TD>
</TR>
</TBODY>
</TABLE>

What I noticed is that if each header has at least a pair of rows, so the categories that only have on row don't display correctly. They display along with another row under the wrong header.

I think it has something to do with the loop:

<% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
<% If (Repeat1__numRows Mod 2) Then%>

but changing it to Mod 1 doesn't do the trick....

Illufox

03-30-2005, 07:51 PM

Wow, I just go it!!!!! I simply removed the first "IF" statement. Here is the correct code (part that was fixed):

<%
Dim DisplayName, TestName
DisplayName = ""
%>
<% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>

<% TestName = Recordset1.Fields.Item("location").Value
if DisplayName <> TestName then %>
<tr>
<td colspan="4" bgcolor="#EFEFEF"><strong><font size="1"><%= TestName %></font></strong></td>
</tr><%
End If
DisplayName = TestName
%>

phpnovice

03-30-2005, 08:28 PM

Cheers.