Data Shaping Question

[Grandkathy]

I have an ASP page that I am trying to show a list of someones
Level 1: Direct Report
Level 2: Their Direct reports
Level 3: Their Direct Reports, etc........
I'm going to be able to go 5 different levels.

I've been able to get the page to run beautifully finally, except that the Level 3 are showing up above their respective Supervisor.

My code is as follows:

dim rsLevel1, rsLevel2, rsLevel3, rsLevel4, rsLevel5

strShapeSQL = "SHAPE{SELECT EmpID, EmpLName, EmpFName, ParentID " &_
					"FROM dbo.qEmployeesParents WHERE EmpID='"&Session("MyID")&"'} " &_
				"APPEND((SHAPE{SELECT EmpID, EmpLName, EmpFName, ParentID FROM dbo.qEmployeesParents} AS Level2 "&_
					"APPEND({SELECT EmpID, EmpLName, EmpFName, ParentID FROM dbo.qEmployeesParents} AS Level3 "&_
					"RELATE EmpID TO ParentID))"&_
					"RELATE EmpID TO ParentID)"
					

Set rsLevel1 = Server.CreateObject("ADODB.Recordset")
rsLevel1.Open strShapeSQL, objConn

if not rsLevel1.EOF then 'Professor
do while not rsLevel1.EOF
	response.Write("Level 1...<B>Your Direct Reports!</B>")&"<br>"
	response.Write("<br>")
		set rslevel2 = rsLevel1("Level2").Value
		if not rsLevel2.EOF then
		do while not rsLevel2.EOF
			response.Write"Level 2...&nbsp;&nbsp;&nbsp;&nbsp;"&(rsLevel2.Fields.Item("EmpFName").Value)&" "&(rsLevel2.Fields.Item("EmpLName").Value)&" "&(rsLevel2.Fields.Item("EmpID"))&"<br>"
			rsLevel2.movenext
				set rslevel3 = rsLevel2("Level3").Value
				if not rsLevel3.EOF then
				do while not rsLevel3.EOF
					response.Write"&nbsp;&nbsp;&nbsp;&nbsp;Level 3...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"&(rsLevel3.Fields.Item("EmpFName").Value)&" "&(rsLevel3.Fields.Item("EmpLName").Value)&" "&(rsLevel3.Fields.Item("EmpID"))&"<br>"
					rsLevel3.movenext
				Loop
				end if
		
		Loop
		end if
	rsLevel1.movenext
Loop
else
'give them a message they don't have any direct reports.
end if
%>
<%
objConn.Close()
Set objConn = Nothing	
%>

This is the sample of data I'm recieving when I run the above code: The words Level 1... are just so I can see things easier for now. Will remove them when I get this running better.

Level 1...Your Direct Reports!

Level 2... Patrick Achurra ACHT235
Level 3... Cynthia Ademujohn ADEC235
Level 2... Cynthia Ademujohn ADEC235
Level 3... Mark Aguiar AGUI235
Level 2... Mark Aguiar AGUI235
Level 3... Kelli Anthony ANTK235
Level 2... Kelli Anthony ANTK235
Level 3... Christopher Bowe BOWE235
Level 2... Christopher Bowe BOWE235
Level 3... Christine Bowlen bowq235
Level 2... Christine Bowlen bowq235
Level 3... Bob Burnett BURB255
Level 2... Bob Burnett BURB255
Level 3... John Coate COAT235
Level 2... John Coate COAT235
Level 3... Renee Hauser HAUT235
Level 2... Renee Hauser HAUT235
Level 3... Hector Smith SMIH255
Level 2... Hector Smith SMIH255
Level 3... Jon Taurman TAUJ235
Level 2... Jon Taurman TAUJ235

 

[Tarwn]

You might be better off executing a single query to get all of the data in one go. Executing in the loop like that will requie a lot more overhead in creating the recordset objects, querying the server multiple times, obtaining memory to store the recordsets, etc.

If you lump this into a large join statement you will end up with more data in your query, but it will be a one shot deal. I haven't seen the SHAPE thing before, but I can give you an example of a simple set of joins and show you sample code for that.
Sample SQL:
SELECT L1.EmpID, L2.EmpID, L3.EmpID
FROM ((Employee L1 INNER JOIN Employee L2 ON L2.ParentID = L1.EmpID)
INNER JOIN Employee L3 ON L3.ParentID = L2.EmpID)
ORDER BY L1.EmpID, L2.EmpID, L3.EmpID

Now to output this recordset in levels we just need to setup a couple variables to remember the last level 1 and level 2 entries we output. I'm going to output these two differant ways for the example:

[b]Output With tabbed levels and level prefixes[/b]
Dim last_lvl1, last_lvl2
Dim tabChars
tabChars = "   "
'queue to the beginning - important in reordered recordsets
If Not MyRS.EOF Then MyRS.MoveFirst
'loop through all of the returned records at once
Do Until MyRS.EOF
   'First check if the L1 person has changed
   If MyRS("L1.EmpID") <> last_lvl1 Then
      'output an entry for this level 1 person
      Response.Write "Level 1: " & MyRS("L1.EmpID") & "<br>"
      'update the last_lvl1 entry
      last_lvl1 = MyRS("L1.EmpID")
   End If

   'next check if the level 2 person has changed
   If MyRS("L2.EmpID") <> last_lvl2 Then
      'output an entry for this level 2 person
      Response.Write tabChars & "Level 2: " & MyRS("L2.EmpID") & "<br>"
      'update the last_lvl2 entry
      last_lvl2 = MyRS("L2.EmpID")
   End If

   'next output the L3 person
   Response.Write tabChars & tabChars & "Level 3: " & MyRS("L3.EmpID") & "<br>"

   'and finally moveto the next record
   MyRS.MoveNext
Loop

[b]Nested Unordered List Output (<ul> tags)[/b]
Dim last_lvl1, last_lvl2
'queue to the beginning - important in reordered recordsets
If Not MyRS.EOF Then MyRS.MoveFirst
'start the list of level one people
Response.Write "<ul>"
'loop through all of the returned records at once
Do Until MyRS.EOF
   'First check if the L1 person has changed
   If MyRS("L1.EmpID") <> last_lvl1 Then
      'output an entry for this level 1 person
      Response.Write "<li>" & MyRS("L1.EmpID")
      'start a level 2 sublist
      Response.Write "<ul>"
      'update the last_lvl1 entry
      last_lvl1 = MyRS("L1.EmpID")
   End If

   'next check if the level 2 person has changed
   If MyRS("L2.EmpID") <> last_lvl2 Then
      'output an entry for this level 2 person
      Response.Write "<li>" & MyRS("L2.EmpID")
      'start a level 3 sublist
      Response.Write "<ul"
      'update the last_lvl2 entry
      last_lvl2 = MyRS("L2.EmpID")
   End If

   'next output the L3 person
   Response.Write "<li>" & MyRS("L3.EmpID") & "</li>"

   'move to the next record
   MyRS.MoveNext

   'if this next record is blank or the level 2 person has changed we need to end the level 3 sublist and this level 2's <li>
   If MyRS.EOF Then
      Response.Write "</ul></li>"
   ElseIf MyRS("L2.EmpID") <> last_lvl2 Then
      Response.Write "</ul></li>"
   End If

   'same test to see if we have to end the level 2 sublist and level 1 <li>
   If MyRS.EOF Then
      Response.Write "</ul></li>"
   ElseIf MyRS("L1.EmpID") <> last_lvl1 Then
      Response.Write "</ul></li>"
   End If
Loop

'End the level 1 list
Response.Write "</ul>"

I wrote these examples on the fly, so their may be some minor syntactical or spelling errors, but the logic should be sound. Obviously you have more fields that you want to pull out besides the EmpID. You could easily add L1.EmpLName, etc for each of the levels and use them in the loop output, but I would suggest you stick with using the EmpID values to track whether you have switched people, since those values should be unique.

The additional advantage to this method, besides creating only a single recordset object and querying the database only once, is that you have a very clean loop to maintain and the query can easily be made into a stored procedure. If you need to add a filter to only return records for a specific level 1 person, you can easily extend that single query to include a where statement to filter on and the logic remains the same. You can also extend the logic of the ORDERing to order by the persons last name in each level, provided you keep the levels in the same order so that results will group together under the parents correctly.

hope this helps,
-T

 

[Bullschmidt]

You might be better off executing a single query to get all of the data in one go.

And here's a related link whose ideas could be expanded into handling more levels:

Classic ASP Design Tips - Grouping Data

http://www.bullschmidt.com/devtip-groupdata.asp

Best regards,
-Paul

http://www.Bullschmidt.com

- Freelance Web and Database Developer

http://www.Bullschmidt.com/DevTip.asp

- Classic ASP Design Tips