Break/Group reports in ASP

bookouri · Apr 26, 2002

How can I do group/break reports from a database with ASP/HTML.

I'm trying to move from crystal reports to pure ASP/HTML reports from a database. I can reproduce all of our simple reports in tables like:

Name address number
bubba 123 4
smith 234 6

but how can I reproduce a group/break report like:

Name
bubba
phonetype number
cell 666
home 777
work 888

bubbette
phonetype number
work 123
pager 444

Total Employees: 2

Our reporting needs are very basic and it's not cost effective any more to depend on high end products like Crystal Reports. We are really intent on getting away from products like Crystal Reports etc. and depending only on ASP/JSP and HTML.

thanks for any suggestions

durug · Apr 26, 2002

I think you will have to put the whole CR logic into the SQL statements

bookouri · Apr 26, 2002

I can use the sql to get the data, but I cant format the HTML output using the sql. All the sql can do it retrieve the record set. I'll have to use HTML/ASP to do the formatting for the actual presentation of the data.

travmak · Apr 26, 2002

try using tables. Something like this.

<table>
<tr>
<td>
response.write(recordset("firstname&quot

)
</td>
</tr>
<tr>
<td>
Blank cell
</td>
<td>
Phone type
</td>
<td>
Phone number
</td>
</tr>
<tr>
<td>
Cellphone number:
</td>
<td>
response.write(recordset("cellphonenumber&quot

)
</td>
and so on...
</tr>
</table>

durug · Apr 26, 2002

What I meant is: creating a select statement that is returning values like this

bubba cell 666
bubba home 777
bubba work 888
bubbette work 123
bubbette pager 444

And then use if then and a counter to find out how many times you had different names

Turkbear · Apr 26, 2002

Here one example that I use to do a grouped and counted report:

Code:

<H3>Listing and Count of Employees In your chosen Job Classes, With Office and Phone Number</H3>
<BODY>
<% 
   Qs = Trim(Request.Form(&quot;prompt0&quot;))
   Set Conn = Server.CreateObject(&quot;ADODB.CONNECTION&quot;)
   Conn.Open &quot;Provider=MSDAORA.1;Password=secret;User ID=alsosecret;Data Source=myoracle;Persist Security Info=TRUE&quot;
   SqlStr = &quot;SELECT job_desc,empl_nm,office_nm,work_phone_nbr FROM warehouse.hr_public where job_cd  IN &quot; & Qs & &quot; order by job_desc,empl_nm&quot;
  '  response.Write(SqlStr)
   Set RS = Conn.Execute(SqlStr)
   arrDBData = RS.GetRows()
   RS.Close
  Set RS = Nothing
  Conn.Close
   Set Conn = Nothing
%>
<%
iRecFirst   = LBound(arrDBData, 2)
iRecLast    = UBound(arrDBData, 2)
iFieldFirst = LBound(arrDBData, 1)
iFieldLast  = UBound(arrDBData, 1)

' Display a table of the data in the array.
' We loop through the array displaying the values.
%>
<table border=&quot;1&quot;>
<%
' Loop through the records
CurOfc = &quot;&quot;
cnt = 0
For I = iRecFirst To iRecLast
	' A table row for each record
	
	Response.Write &quot;<tr>&quot; & vbCrLf
	
	' Loop through the fields 
	If CurOfc <> arrDBData(0,I) then
	if cnt > 0 then
	Response.Write &quot;<tr BGCOLOR='66FF99'>&quot; & vbCrLf
	Response.Write &quot;<td><br>&quot; & &quot;Count in This Job Class :&quot; & &quot;</b></td>&quot; & vbTab & &quot;<td><b>&quot; &  cnt & &quot;</b></td>&quot;
	Response.Write &quot;</tr>&quot;
	cnt = 0
	end if
	 CurOfc = arrDBData(0,I)
	' A table cell for each field
	Response.Write &quot;<tr BGCOLOR='#00FFFF'>&quot;
	 Response.Write vbTab & &quot;<td><H3>&quot; &  arrDBData(0, I) &  &quot;</td></b>&quot; & vbTab & &quot;<td><b>Name</td></H3>&quot; & vbTab & &quot;<td>&quot; & &quot;</td>&quot; & &quot;<td><b>&quot; & &quot;Office&quot; & &quot;</td></b>&quot; & &quot;<td>&quot; & &quot;</td>&quot; & &quot;<td><b>&quot; & &quot;Phone&quot; & &quot;</td></b>&quot;
	 Response.Write vbCrLf & &quot;</tr>&quot; & vbCrLf  	 	 
	
	
        End If
       If CurOfc = arrDBData(0,I) then
	 For T = 1 to 3
	   Response.Write &quot;<td>&quot; & vbTab & &quot;</td>&quot; & &quot;<td>&quot; &  arrDBData(T, I) &  &quot;</td>&quot; 
	 Next ' T
	 Response.Write vbCrLf & &quot;</tr>&quot; & vbCrLf
	 cnt = cnt + 1
	 
	End If
	
	
Next ' I	
Response.Write &quot;<tr BGCOLOR='66FF99'>&quot; & vbCrLf
Response.Write &quot;<td><b>&quot; & &quot;Count in This Job Class :&quot; & &quot;</b></td>&quot; & vbTab & &quot;<td><b>&quot; &  cnt & &quot;</b></td>&quot;	

%>
</table>
<BR>
<BR>
<P>

</BODY>
</HTML>

It should give you some ideas to work with...

bookouri · Apr 26, 2002

Thanks, Ill give that grouped report a try. That looks like what Im trying to accomplish...

bookouri · Apr 26, 2002

thanks, i think the two loops is the answer. Is there any way other than getting the recordset count to control the loop? Some of my datasets are very dynamic and could often change from the time the recordset count was calculated and before the data was actually fed into the detail table.

i tried something like

do while not recordset.eof

tempidnumber = recordset("idnumber&quot

do while tempidnumber = recordset("idnumber&quot

write out table
movenext
loop

movenext
tempidnumber = recordset("idnumber&quot

loop

but I get a datatype mismatch error with the do while tempidnumber = recordset("idnumber&quot

comparison. I suspect that it is not possible to do this kind of comparison

thanks

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Break/Group reports in ASP

bookouri

IS-IT--Management

durug

Technical User

bookouri

IS-IT--Management

travmak

Programmer

durug

Technical User

Turkbear

Technical User

bookouri

IS-IT--Management

bookouri

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor