Report Problems

Richard Sensale ( (no email) )
Wed, 14 Jan 1998 14:12:56 -0500

This is a multi-part message in MIME format.

------=_NextPart_000_008E_01BD20F6.82F01E20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Okay lets see f someone can help me out. Im trying to do a web based =
report utilizing the emerald database to report the total payments =
grouped by login ID and Date of payment for people with a certain region =
and account type. With subtotals for each user and a grand total for =
the region and account type combo at the bottom. I need to have it set =
up to only select records that have payment dates between a range that =
is entered on a form on the page. I have been trying to get this to =
work for two weeks now (Im not the greatest at sql or asp design. If =
anyone could spare a minute to take a look at this code and see if you =
can see where I am making my mistakes I would really appreciate it. I =
don't get any sql errors now but it still is ust bouncing people back to =
the form without displaying any records.

<% StartDate =3D Request("StartDate")

EndDate =3D Request("EndDate") %>

<% IF (StartDate <> "") AND (EndDate <> "") THEN

Session.Timeout =3D 1

IF IsObject(Session("EmeraldDSN_conn")) THEN

Set conn =3D Session("EmeraldDSN_conn")

ELSE

Set conn =3D Server.CreateObject("ADODB.Connection")

conn.open "DSN=3DEmeraldDSN; UID=3Dsa; PWD=3Dauth5434sq"

Set Session("EmeraldDSN_conn") =3D conn

END IF

sql1 =3D "SELECT Payments.PaymentID, Payments.Amount, Payments.Date, =
MasterAccounts.CustomerID, MasterAccounts.Region, =
MasterAccounts.PayMethod, SubAccounts.Login, SubAccounts.LastName, =
SubAccounts.AccountType, Invoices.InvoiceID, InvoiceItems.ItemID "

sql1 =3D sql1 & "FROM Emerald.dbo.Payments Payments, =
Emerald.dbo.MasterAccounts MasterAccounts, Emerald.dbo.SubAccounts =
SubAccounts, Emerald.dbo.Invoices Invoices, Emerald.dbo.InvoiceItems =
InvoiceItems "

sql1 =3D sql1 & "WHERE Payments.CustomerID =3D MasterAccounts.CustomerID =
AND"

sql1 =3D sql1 & " MasterAccounts.CustomerID =3D SubAccounts.CustomerID =
AND"

sql1 =3D sql1 & " SubAccounts.CustomerID =3D Invoices.CustomerID AND"

sql1 =3D sql1 & " Invoices.InvoiceID =3D InvoiceItems.InvoiceID AND"

sql1 =3D sql1 & " MasterAccounts.Region =3D 'SEV' AND"

sql1 =3D sql1 & " SubAccounts.AccountType =3D 'SEV-Residential' AND "

sql1 =3D sql1 & "(Payments.Date >=3D CONVERT(datetime, '" & StartDate & =
"')) AND "

sql1 =3D sql1 & "(Payments.Date <=3D CONVERT(datetime, '" & EndDate & =
"')) "

sql1 =3D sql1 & "ORDER BY SubAccounts.Login ASC, Payments.Date ASC, =
MasterAccounts.CustomerID ASC, Invoices.InvoiceID ASC, =
InvoiceItems.ItemID ASC"

sql2 =3D "SELECT Payments.PaymentID, Payments.Amount, Payments.Date, =
MasterAccounts.CustomerID, MasterAccounts.Region, =
MasterAccounts.PayMethod, SubAccounts.Login, SubAccounts.LastName, =
SubAccounts.AccountType, Invoices.InvoiceID, InvoiceItems.ItemID "

sql2 =3D sql2 & "FROM Emerald.dbo.Payments Payments, =
Emerald.dbo.MasterAccounts MasterAccounts, Emerald.dbo.SubAccounts =
SubAccounts, Emerald.dbo.Invoices Invoices, Emerald.dbo.InvoiceItems =
InvoiceItems "

sql2 =3D sql2 & "WHERE Payments.CustomerID =3D MasterAccounts.CustomerID =
AND"

sql2 =3D sql2 & " MasterAccounts.CustomerID =3D SubAccounts.CustomerID =
AND"

sql2 =3D sql2 & " SubAccounts.CustomerID =3D Invoices.CustomerID AND"

sql2 =3D sql2 & " Invoices.InvoiceID =3D InvoiceItems.InvoiceID AND"

sql2 =3D sql2 & " MasterAccounts.Region =3D 'SEV' AND"

sql2 =3D sql2 & " SubAccounts.AccountType =3D 'SEV-Residential' AND "

sql2 =3D sql2 & "(Payments.Date >=3D CONVERT(datetime, '" & StartDate & =
"')) AND "

sql2 =3D sql2 & "(Payments.Date <=3D CONVERT(datetime, '" & EndDate & =
"')) "

sql2 =3D sql2 & "ORDER BY SubAccounts.Login ASC, Payments.Date ASC, =
MasterAccounts.CustomerID ASC, Invoices.InvoiceID ASC, =
InvoiceItems.ItemID ASC "

sql2 =3D sql2 & "COMPUTE SUM(ISNULL(Payments.Amount,0)) BY =
SubAccounts.Login ASC, Payments.Date ASC, MasterAccounts.CustomerID ASC, =
Invoices.InvoiceID ASC, InvoiceItems.ItemID ASC"

sql3 =3D "SELECT Payments.PaymentID, Payments.Amount, Payments.Date, =
MasterAccounts.CustomerID, MasterAccounts.Region, =
MasterAccounts.PayMethod, SubAccounts.Login, SubAccounts.LastName, =
SubAccounts.AccountType, Invoices.InvoiceID, InvoiceItems.ItemID "

sql3 =3D sql3 & "FROM Emerald.dbo.Payments Payments, =
Emerald.dbo.MasterAccounts MasterAccounts, Emerald.dbo.SubAccounts =
SubAccounts, Emerald.dbo.Invoices Invoices, Emerald.dbo.InvoiceItems =
InvoiceItems "

sql3 =3D sql3 & "WHERE Payments.CustomerID =3D MasterAccounts.CustomerID =
AND"

sql3 =3D sql3 & " MasterAccounts.CustomerID =3D SubAccounts.CustomerID =
AND"

sql3 =3D sql3 & " SubAccounts.CustomerID =3D Invoices.CustomerID AND"

sql3 =3D sql3 & " Invoices.InvoiceID =3D InvoiceItems.InvoiceID AND"

sql3 =3D sql3 & " MasterAccounts.Region =3D 'SEV' AND"

sql3 =3D sql3 & " SubAccounts.AccountType =3D 'SEV-Residential' AND "

sql3 =3D sql3 & "(Payments.Date >=3D CONVERT(datetime, '" & StartDate & =
"')) AND "

sql3 =3D sql3 & "(Payments.Date <=3D CONVERT(datetime, '" & EndDate & =
"')) "

sql3 =3D sql3 & "ORDER BY SubAccounts.Login ASC, Payments.Date ASC, =
MasterAccounts.CustomerID ASC, Invoices.InvoiceID ASC, =
InvoiceItems.ItemID ASC "

sql3 =3D sql3 & "COMPUTE SUM(ISNULL(Payments.Amount,0))"

Set rs =3D Server.CreateObject("ADODB.Recordset")

rs.Open sql1, conn, 3, 3

On Error Resume Next

Set rs =3D Server.CreateObject("ADODB.Recordset")

rs.Open sql2, conn, 3, 3

On Error Resume Next

total_dyt_u_336 =3D rs.Fields("subtotals").value

Set rs =3D Server.CreateObject("ADODB.Recordset")

rs.Open sql3, conn, 3, 3

On Error Resume Next

total_dyt_u_336 =3D rs.Fields("totals").value

END IF %>

<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">

Sev Payments Residential report

StartingDate:(mm/dd/yy)
EndingDate:(mm/dd/yy)">

 

<% ELSE %>

Glass City Internet SEV =Users.

Residential Users

UserPayment Report For Period ( <% =3D StartDate%> through <% =3D EndDate =%>)

<% On Error Resume Nextrs.MoveFirstdo while not rs.EOF %>
<% rs.MoveNextloop %>
CustomerIDDateof PaymentAmountPaidLogin =IDlastNamePaymentIDInvoiceIDPaymentMethod
<% =3D rs.Fields("CustomerID").Value =%><% =3D rs.Fields("Date").Value %> =<% =3D rs.Fields("Amount").Value =%><% =3D rs.Fields("Login").Value =%><% =3D rs.Fields("LastName").Value =%><% =3D rs.Fields("PaymentID").Value =%><% =3D rs.Fields("InvoiceID").Value =%><% =3D rs.Fields("PayMethod").Value =%>
Subtotal of Payments<% =3D =rs.Fields("subtotals").Value %>


Total Payments <% =3D =rs.Fields("totals").Value %>

<% END IF %>

------=_NextPart_000_008E_01BD20F6.82F01E20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD W3 HTML//EN">

Okay lets see f someone can help me =out. =20Im trying to do a web based report utilizing the emerald database to =report the=20total payments  grouped by login ID and Date of payment for people =with a=20certain region and account type.  With subtotals for each user and =a grand=20total for the region and account type combo at the bottom.  I need =to have=20it set up to only select records that have payment dates between a range =that is=20entered on a form on the page.  I have been trying to get this to =work for=20two weeks now (Im not the greatest at sql or asp design.  If anyone =could=20spare a minute to take a look at this code and see if you can see where =I am=20making my mistakes I would really appreciate it.  I don't get any =sql=20errors now but it still is ust bouncing people back to the form without=20displaying any records.
 

<% =StartDate =3D =Request("StartDate")

EndDate =3D Request("EndDate") %>

<% =IF (StartDate <> ="") AND=20(EndDate <> "") THEN

 

 

Session.Timeout =3D 1

IF IsObject(Session("EmeraldDSN_conn")) THEN

Set conn =3D Session("EmeraldDSN_conn")

ELSE

Set conn =3D Server.CreateObject("ADODB.Connection")

conn.open "DSN=3DEmeraldDSN; UID=3Dsa; =PWD=3Dauth5434sq"

Set Session("EmeraldDSN_conn") =3D conn

END IF

sql1 =3D "SELECT Payments.PaymentID, Payments.Amount, =Payments.Date,=20MasterAccounts.CustomerID, MasterAccounts.Region, =MasterAccounts.PayMethod,=20SubAccounts.Login, SubAccounts.LastName, SubAccounts.AccountType,=20Invoices.InvoiceID, InvoiceItems.ItemID "

sql1 =3D sql1 & "FROM Emerald.dbo.Payments Payments,=20Emerald.dbo.MasterAccounts MasterAccounts, Emerald.dbo.SubAccounts =SubAccounts,=20Emerald.dbo.Invoices Invoices, Emerald.dbo.InvoiceItems InvoiceItems ="

sql1 =3D sql1 & "WHERE Payments.CustomerID =3D =MasterAccounts.CustomerID=20AND"

sql1 =3D sql1 & " MasterAccounts.CustomerID =3D =SubAccounts.CustomerID=20AND"

sql1 =3D sql1 & " SubAccounts.CustomerID =3D =Invoices.CustomerID=20AND"

sql1 =3D sql1 & " Invoices.InvoiceID =3D =InvoiceItems.InvoiceID=20AND"

sql1 =3D sql1 & " MasterAccounts.Region =3D 'SEV' =AND"

sql1 =3D sql1 & " SubAccounts.AccountType =3D ='SEV-Residential' AND=20"

sql1 =3D sql1 & "(Payments.Date >=3D CONVERT(datetime, ='" &=20StartDate & "')) AND "

sql1 =3D sql1 & "(Payments.Date <=3D CONVERT(datetime, ='" &=20EndDate & "')) "

sql1 =3D sql1 & "ORDER BY SubAccounts.Login ASC, =Payments.Date ASC,=20MasterAccounts.CustomerID ASC, Invoices.InvoiceID ASC, =InvoiceItems.ItemID=20ASC"

sql2 =3D "SELECT Payments.PaymentID, Payments.Amount, =Payments.Date,=20MasterAccounts.CustomerID, MasterAccounts.Region, =MasterAccounts.PayMethod,=20SubAccounts.Login, SubAccounts.LastName, SubAccounts.AccountType,=20Invoices.InvoiceID, InvoiceItems.ItemID "

sql2 =3D sql2 & "FROM Emerald.dbo.Payments Payments,=20Emerald.dbo.MasterAccounts MasterAccounts, Emerald.dbo.SubAccounts =SubAccounts,=20Emerald.dbo.Invoices Invoices, Emerald.dbo.InvoiceItems InvoiceItems ="

sql2 =3D sql2 & "WHERE Payments.CustomerID =3D =MasterAccounts.CustomerID=20AND"

sql2 =3D sql2 & " MasterAccounts.CustomerID =3D =SubAccounts.CustomerID=20AND"

sql2 =3D sql2 & " SubAccounts.CustomerID =3D =Invoices.CustomerID=20AND"

sql2 =3D sql2 & " Invoices.InvoiceID =3D =InvoiceItems.InvoiceID=20AND"

sql2 =3D sql2 & " MasterAccounts.Region =3D 'SEV' =AND"

sql2 =3D sql2 & " SubAccounts.AccountType =3D ='SEV-Residential' AND=20"

sql2 =3D sql2 & "(Payments.Date >=3D CONVERT(datetime, ='" &=20StartDate & "')) AND "

sql2 =3D sql2 & "(Payments.Date <=3D CONVERT(datetime, ='" &=20EndDate & "')) "

sql2 =3D sql2 & "ORDER BY SubAccounts.Login ASC, =Payments.Date ASC,=20MasterAccounts.CustomerID ASC, Invoices.InvoiceID ASC, =InvoiceItems.ItemID ASC=20"

sql2 =3D sql2 & "COMPUTE SUM(ISNULL(Payments.Amount,0)) BY=20SubAccounts.Login ASC, Payments.Date ASC, MasterAccounts.CustomerID ASC, =Invoices.InvoiceID ASC, InvoiceItems.ItemID ASC"

sql3 =3D "SELECT Payments.PaymentID, Payments.Amount, =Payments.Date,=20MasterAccounts.CustomerID, MasterAccounts.Region, =MasterAccounts.PayMethod,=20SubAccounts.Login, SubAccounts.LastName, SubAccounts.AccountType,=20Invoices.InvoiceID, InvoiceItems.ItemID "

sql3 =3D sql3 & "FROM Emerald.dbo.Payments Payments,=20Emerald.dbo.MasterAccounts MasterAccounts, Emerald.dbo.SubAccounts =SubAccounts,=20Emerald.dbo.Invoices Invoices, Emerald.dbo.InvoiceItems InvoiceItems ="

sql3 =3D sql3 & "WHERE Payments.CustomerID =3D =MasterAccounts.CustomerID=20AND"

sql3 =3D sql3 & " MasterAccounts.CustomerID =3D =SubAccounts.CustomerID=20AND"

sql3 =3D sql3 & " SubAccounts.CustomerID =3D =Invoices.CustomerID=20AND"

sql3 =3D sql3 & " Invoices.InvoiceID =3D =InvoiceItems.InvoiceID=20AND"

sql3 =3D sql3 & " MasterAccounts.Region =3D 'SEV' =AND"

sql3 =3D sql3 & " SubAccounts.AccountType =3D ='SEV-Residential' AND=20"

sql3 =3D sql3 & "(Payments.Date >=3D CONVERT(datetime, ='" &=20StartDate & "')) AND "

sql3 =3D sql3 & "(Payments.Date <=3D CONVERT(datetime, ='" &=20EndDate & "')) "

sql3 =3D sql3 & "ORDER BY SubAccounts.Login ASC, =Payments.Date ASC,=20MasterAccounts.CustomerID ASC, Invoices.InvoiceID ASC, =InvoiceItems.ItemID ASC=20"

sql3 =3D sql3 & "COMPUTE =SUM(ISNULL(Payments.Amount,0))"

 

Set rs =3D Server.CreateObject("ADODB.Recordset")

rs.Open sql1, conn, 3, 3

On Error Resume Next

Set rs =3D Server.CreateObject("ADODB.Recordset")

rs.Open sql2, conn, 3, 3

On Error Resume Next

total_dyt_u_336 =3D rs.Fields("subtotals").value

Set rs =3D Server.CreateObject("ADODB.Recordset")

rs.Open sql3, conn, 3, 3

On Error Resume Next

total_dyt_u_336 =3D rs.Fields("totals").value

END IF %>

<!DOCTYPE =HTML PUBLIC "-//IETF//DTD HTML//EN">

<html>

<head>

<meta =http-equiv=3D"Content-Type"

content=3D"text/html;=20charset=3Diso-8859-1">

<meta =name=3D"GENERATOR" content=3D"Microsoft FrontPage 2.0">

<title>Sev Payments Residential =report</title>

</head>

<body =bgcolor=3D"#FFFF00">

<form =action=3D"sev_res_pay.asp" method=3D"POST">

<div =align=3D"center"><center><table border=3D"0">

<tr>

<td><font=20color=3D"#000000" face=3D"Arial"><strong>Starting

Date:</strong></font><font face=3D"Arial">(mm/dd/yy)</font></td>

<td><font=20face=3D"Arial"><input type=3D"text" =size=3D"10"

name=3D"StartDate"></font></td>

</tr>

<tr>

<td><font=20color=3D"#000000" face=3D"Arial"><strong>Ending

Date:</strong></font><font face=3D"Arial">(mm/dd/yy)</font></td>

<td><font=20face=3D"Arial"><input type=3D"text" =size=3D"10"

name=3D"EndDate" =value=3D"<% =3DDate %>"></font></td>

</tr>

</table>

</center></div><p align=3D"center"><font=20face=3D"Arial"><input

type=3D"submit" name=3D"B1"=20value=3D"View=20Report"></font></p>

</form>

<p>&nbsp;</p>

<p><font face=3D"Arial"><% ELSE=20%></font></p>

<div =align=3D"center"><center>

<table =border=3D"3"=20cellpadding=3D"2" =cellspacing=3D"3" width=3D"100%"

bgcolor=3D"#000080">

<tr>

<td =align=3D"center"=20width=3D"100%" =bgcolor=3D"#FFFFFF"

bordercolor=3D"#0000FF"=20bordercolordark=3D"#000080"

bordercolorlight=3D"#00FFFF"><font=20color=3D"#000000" size=3D"7"

face=3D"Arial"><strong>Glass City Internet SEV =Users.</strong></font></td>

</tr>

</table>

</center></div><div align=3D"center"><center>

<table =border=3D"1"=20cellpadding=3D"2" =cellspacing=3D"3" width=3D"100%"

bgcolor=3D"#0000FF">

<tr>

<td =width=3D"100%"><p align=3D"center"><font=20color=3D"#FFFF00"

size=3D"6">Residential Users</font></p>

</td>

</tr>

</table>

</center></div>

<p =align=3D"center"><font color=3D"#000080"=20size=3D"6" face=3D"Arial"><strong>User

Payment Report For Period ( <% =3D =StartDate%> through <% =3D =EndDate %>)</strong></font></p>

<% =On Error Resume Next

rs.MoveFirst

do while not rs.EOF %><div align=3D"center"><center>

<table =border=3D"1"=20cellpadding=3D"2" =cellspacing=3D"1" width=3D"100%"

bgcolor=3D"#0000FF">

<tr>

<td =width=3D"50"=20bgcolor=3D"#FFFF00"><font face=3D"Arial"><strong>Customer

ID</strong></font></td>

<td =width=3D"50"=20bgcolor=3D"#FFFF00"><font face=3D"Arial"><strong>Date

of Payment</strong></font></td>

<td =width=3D"50"=20bgcolor=3D"#FFFF00"><font face=3D"Arial"><strong>Amount

Paid</strong></font></td>

<td =bgcolor=3D"#FFFF00"><font face=3D"Arial"><strong>Login =ID</strong></font></td>

<td =bgcolor=3D"#FFFF00"><font face=3D"Arial"><strong>last

Name</strong></font></td>

<td =bgcolor=3D"#FFFF00"><font face=3D"Arial"><strong>Payment

ID</strong></font></td>

<td =bgcolor=3D"#FFFF00"><font face=3D"arial"><strong>InvoiceID</strong></font></td>

<td =bgcolor=3D"#FFFF00"><font face=3D"arial"><strong>Payment

Method</strong></font></td>

</tr>

<tr>

<td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"4"

face=3D"Arial"><strong><%=20=3D=20rs.Fields("CustomerID").Value %></strong></font></td>

<td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"

face=3D"Arial"><strong><%=20=3D=20rs.Fields("Date").Value %>=20</strong></font></td>

<td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"

face=3D"Arial"><strong><%=20=3D=20rs.Fields("Amount").Value %></strong></font></td>

<td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"

face=3D"Arial"><strong><%=20=3D=20rs.Fields("Login").Value %></strong></font></td>

<td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"

face=3D"Arial"><strong><%=20=3D=20rs.Fields("LastName").Value %></strong></font></td>

<td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"

face=3D"arial"><strong><%=20=3D=20rs.Fields("PaymentID").Value %></strong></font></td>

<td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"

face=3D"arial"><strong><%=20=3D=20rs.Fields("InvoiceID").Value %></strong></font></td>

<td =bgcolor=3D"#FFFFFF"><font color=3D"#000000"=20size=3D"3"

face=3D"arial"><strong><%=20=3D=20rs.Fields("PayMethod").Value %></strong></font></td>

</tr>

<tr>

<td =width=3D"50"=20bgcolor=3D"#000000"><font color=3D"#FFFF00"

face=3D"Arial"><strong>Subtotal of Payments</strong></font></td>

<td =bgcolor=3D"#FFFFFF"><font face=3D"Arial"><strong><% =3D=20rs.Fields("subtotals").Value %></strong></font></td>

</tr>

<font =face=3D"Arial"><strong><% rs.MoveNext

loop %></strong></font>

</table>

</center></div>

<p =align=3D"center"><br>

Total Payments <font=20face=3D"Arial"><strong><% =3D rs.Fields("totals").Value %></strong></font><br>

</p>

<form =action=3D"new_users.asp" method=3D"POST">

<p =align=3D"center"><font face=3D"Arial"><strong><input

type=3D"submit" name=3D"B1"=20value=3D"New=20Report"></strong></font></p>

</form>

<p><font face=3D"Arial"><% END IF=20%></font></p>

</body>

</html>

------=_NextPart_000_008E_01BD20F6.82F01E20--