What I have:
SalesPersonName
Month 1
Date Cust Amount YTD
Date Cust Amount YTD
Month 2
Date Cust Amount YTD
Date Cust Amount YTD
What I Need:
SalesPersonName
Month 1
Date Cust Amount YTD
Date Cust Amount YTD
Month 1 Total: [SUM of Amount for Month]
Month 2
Date Cust Amount YTD
Date Cust Amount YTD
Month 2 Total: [Sum of Amount for Month]
I tried writing a function to sum the Amount but it didn't work correctly.
I know there is a way to do this with the qry string, I was hoping for some type of vbscript instead. I'll take help with either.
BTW - thanks all for answer to my other questions.
THe code below might show up messy.
SalesPersonName
Month 1
Date Cust Amount YTD
Date Cust Amount YTD
Month 2
Date Cust Amount YTD
Date Cust Amount YTD
What I Need:
SalesPersonName
Month 1
Date Cust Amount YTD
Date Cust Amount YTD
Month 1 Total: [SUM of Amount for Month]
Month 2
Date Cust Amount YTD
Date Cust Amount YTD
Month 2 Total: [Sum of Amount for Month]
I tried writing a function to sum the Amount but it didn't work correctly.
I know there is a way to do this with the qry string, I was hoping for some type of vbscript instead. I'll take help with either.
BTW - thanks all for answer to my other questions.
THe code below might show up messy.
Code:
<body>
<p>
<%
Dim n ' for name comparison
strID = Request.QueryString("ID")
strSQL = "SELECT rsm.FName, rsm.LName, rsm.Target, rsm.Goal, rsm.SuperGoal, sales.DateSold, sales.CustName, sales.Type, sales.HostVend,sales.SysConfig, sales.Amount, sales.Margin FROM rsm INNER JOIN sales ON rsm.ID = sales.RSMID WHERE rsm.ID=" & strID & " GROUP BY rsm.FName, rsm.LName, rsm.Target, rsm.Goal, rsm.SuperGoal, sales.DateSold, sales.CustName, sales.Type, sales.HostVend,sales.SysConfig, sales.Amount, sales.Margin"
set rs = con.execute(strSQL)
%>
<p></p>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<%
If NOT rs.EOF then
strYear = rs("DateSold")
strName = rs("FName") & " " & rs("LName")
strMonth = monthname(Month(rs("DateSold")))
Function percent(s,t)
p=s/t
percent = p
End Function
Response.Write("<tr colspan=""7""><td>" & strName & " " & year(strYear) &"</td><td width = ""20%""> Sales Details</td></tr>")
tot = 0
Do until rs.EOF
If Month(rs("DateSold")) <> n then
'make a space between the RSM names
Response.Write "<tr height=""20""><td></td></tr>"
Response.Write("<tr><td bgcolor=""#CCCCCC"">" & monthname(Month(rs("DateSold"))) & "</td></tr>")
Response.Write "<tr class=""Sales""><td>Date Sold</td><td>Customer</td><td>Type</td><td>Host</td><td>System</td><td>Amount</td><td>YTD</td><td>Margin</td><td>Target</td><td>Target Percent</td></tr>"
n = Month(rs("DateSold"))
Else
Response.Write "<tr><td></td></tr>"
End If
tot = tot+rs("Amount")'creates year to date totals for each line item
Response.Write "<tr class=""Cellfont1""><td>" & " " & rs("DateSold") & "</td><td>" & rs("CustName") & "</td><td>"& rs("Type") &"</td><td>"& rs("HostVend") &"</td><td>" & rs("SysConfig")& "</td><td>" & FormatCurrency(rs("Amount")) &"</td><td>" & FormatCurrency(tot) &"</td><td>"& rs("Margin") &"</td><td>"& FormatCurrency(rs("Target")) &"</td><td>"& FormatPercent(percent(rs("Amount"),rs("Target"))) &"</td></tr>"
rs.MoveNext
Loop
Else ' No records for the month
Response.Write("No sales for " & strName & " yet. :(")
End If
%>
</table>
</body>