ASP & Conditional Formatting [Archive]

Click to See Complete Forum and Search --> : ASP & Conditional Formatting

Chamark

01-06-2007, 09:19 PM

I am a newbie at ASP so I am hoping someone can assist me with being able to format Web field background colors like in Excel. I want to have the bgcolor of a cell on a results page from a SQL query be red if the amount is below 90 and be yellow if 90 to 95 and green if above 95. Any help on syntax or where to start is greatly appreciated.

russell

01-06-2007, 11:17 PM

Dim bgcolor

If someVar < 90 Then
bgcolor = "red"
ElseIf someVar < 96 Then
bgcolor = "yellow"
Else
bgcolor = "green"
End If

Response.Write "<td bgcolor=" & bgcolor & ">hello world</td>"

Chamark

01-07-2007, 02:43 PM

Hi Russell. Thanks for your help. Obviously I am overlooking something because I am getting all green see partial code.

<%Dim bgcolor

If (AHTDirSum.Fields.Item("AHT").Value) > 450 Then
bgcolor = "red"
ElseIf (AHTDirSum.Fields.Item("AHT").Value) < 335 Then
bgcolor = "yellow"
Else
bgcolor = "green"
End If
%>
<td><%=(AHTDirSum.Fields.Item("Director").Value)%></td>
<td><%=(AHTDirSum.Fields.Item("Segment").Value)%></td>
<td><%=(AHTDirSum.Fields.Item("Calls").Value)%></td>

<td bgcolor=" & bgcolor & "><%= FormatNumber((AHTDirSum.Fields.Item("AHT").Value), 2, -2, -2, -2) %></td>

russell

01-07-2007, 03:01 PM

try

If Clng(AHTDirSum.Fields.Item("AHT").Value)) > 450 Then
bgcolor = "red"
ElseIf Clng(AHTDirSum.Fields.Item("AHT").Value) < 335 Then
bgcolor = "yellow"
Else
bgcolor = "green"
End If

Chamark

01-07-2007, 03:15 PM

I've tried to make it even more simple with just red & green and still get all green (see code). Thanks
<%Dim bgcolor

If Clng(AHTDirSum.Fields.Item("AHT").Value) < (AHTDirSum.Fields.Item("Goal").Value) Then
bgcolor = "green"
Else
bgcolor = "red"
End If
%>

<table border="1" align="center" bordercolor="#EEEEEE" class="sortable" id="unique_id">
<tr>
<td>Director</td>
<td>Segment</td>
<td>Calls</td>
<td>AHT</td>
<td>Talk Time</td>
<td>Wrap Time</td>
<td>Hold Time</td>
<td>Goal</td>
<td>Variance</td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT AHTDirSum.EOF)) %>
<tr>
<td><%=(AHTDirSum.Fields.Item("Director").Value)%></td>
<td><%=(AHTDirSum.Fields.Item("Segment").Value)%></td>
<td><%=(AHTDirSum.Fields.Item("Calls").Value)%></td>

<td bgcolor=" & bgcolor &"><%= FormatNumber((AHTDirSum.Fields.Item("AHT").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Talk Time").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Wrap Time").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Hold Time").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Goal").Value), 0, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Variance").Value), 2, -2, -2, -2) %></td>
</tr>

russell

01-07-2007, 05:20 PM

If Clng(AHTDirSum.Fields.Item("AHT").Value) < Clng(AHTDirSum.Fields.Item("Goal").Value) Then
also, what is the value of "AHT" and "Goal" ?

what does style12 apply to the span?

oh, wait....
this

If Clng(AHTDirSum.Fields.Item("AHT").Value) < (AHTDirSum.Fields.Item("Goal").Value) Then
bgcolor = "green"
Else
bgcolor = "red"
End If

needs to be right after this line:
While ((Repeat1__numRows <> 0) AND (NOT AHTDirSum.EOF))

Chamark

01-07-2007, 05:24 PM

try

If Clng(AHTDirSum.Fields.Item("AHT").Value)) > 450 Then
bgcolor = "red"
ElseIf Clng(AHTDirSum.Fields.Item("AHT").Value) < 335 Then
bgcolor = "yellow"
Else
bgcolor = "green"
End If

_______________________________________________________________
OK I've tried a different approach (see the following code) and get the right colors but my AHT column is blank and it writes my colors in the Talk Time column. I'd also like to be able to use formatnumber in the syntax. Any help is
appreciated.
<tr>
<td>Director</td>
<td>Segment</td>
<td>Calls</td>
<td>AHT</td>
<td>Talk Time</td>
<td>Wrap Time</td>
<td>Hold Time</td>
<td>Goal</td>
<td>Variance</td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT AHTDirSum.EOF)) %>
<tr>
<td><%=(AHTDirSum.Fields.Item("Director").Value)%></td>
<td><%=(AHTDirSum.Fields.Item("Segment").Value)%></td>
<td><%=(AHTDirSum.Fields.Item("Calls").Value)%></td>
<td><% If(AHTDirSum.Fields.Item("AHT").Value) < (AHTDirSum.Fields.Item("Goal").Value)Then
Response.Write"<td bgcolor= green >"
Else
Response.Write "<td bgcolor= red >" End If %></td> <td><%= FormatNumber((AHTDirSum.Fields.Item("Talk Time").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Wrap Time").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Hold Time").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Goal").Value), 0, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Variance").Value), 2, -2, -2, -2) %></td>
</tr>

russell

01-07-2007, 06:09 PM

psot the entire script. i'll try to show u how to clean it up

Chamark

01-07-2007, 06:31 PM

Thank you russell

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

<%
Dim AHTDirSum__MMColParam1
AHTDirSum__MMColParam1 = "1"
If (Request.Form("StartDate") <> "") Then
AHTDirSum__MMColParam1 = Request.Form("StartDate")
End If
%>
<%
Dim AHTDirSum__MMColParam2
AHTDirSum__MMColParam2 = "1"
If (Request.Form("EndDate") <> "") Then
AHTDirSum__MMColParam2 = Request.Form("EndDate")
End If
%>
<%
Dim AHTDirSum
Dim AHTDirSum_cmd
Dim AHTDirSum_numRows

Set AHTDirSum_cmd = Server.CreateObject ("ADODB.Command")
AHTDirSum_cmd.ActiveConnection = MM_MetricsCONN_STRING
AHTDirSum_cmd.CommandText = "SELECT TOP 10000 dbo.[National Call Stats].Director, dbo.[National Call Stats].Segment, SUM(dbo.[National Call Stats].NchQty) AS Calls, SUM(dbo.[National Call Stats].TotalHandleTime) / SUM(dbo.[National Call Stats].NchQty) AS AHT, SUM(dbo.[National Call Stats].[Talk Secs Qty]) / SUM(dbo.[National Call Stats].NchQty) AS [Talk Time], SUM(dbo.[National Call Stats].[Call Wrap Secs Qty]) / SUM(dbo.[National Call Stats].NchQty) AS [Wrap Time], SUM(dbo.[National Call Stats].HoldSecsQty) / SUM(dbo.[National Call Stats].NchQty) AS [Hold Time], dbo.[AHTGoals-DIRGM].Goal, dbo.[AHTGoals-DIRGM].Goal - SUM(dbo.[National Call Stats].TotalHandleTime) / SUM(dbo.[National Call Stats].NchQty) AS Variance FROM dbo.[National Call Stats] INNER JOIN dbo.[AHTGoals-DIRGM] ON dbo.[National Call Stats].Segment = dbo.[AHTGoals-DIRGM].Segment WHERE (dbo.[National Call Stats].[Date] >= ?) AND (dbo.[National Call Stats].[Date] <= ?) AND (dbo.[National Call Stats].Site = N'Dallas') GROUP BY dbo.[National Call Stats].Segment, dbo.[National Call Stats].Segment, dbo.[AHTGoals-DIRGM].Goal, dbo.[National Call Stats].Director HAVING (SUM(dbo.[National Call Stats].NchQty) > 0) ORDER BY dbo.[National Call Stats].Director, dbo.[National Call Stats].Segment"
AHTDirSum_cmd.Prepared = true
AHTDirSum_cmd.Parameters.Append AHTDirSum_cmd.CreateParameter("param1", 200, 1, 255, AHTDirSum__MMColParam1) ' adVarChar
AHTDirSum_cmd.Parameters.Append AHTDirSum_cmd.CreateParameter("param2", 200, 1, 255, AHTDirSum__MMColParam2) ' adVarChar

Set AHTDirSum = AHTDirSum_cmd.Execute
AHTDirSum_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
AHTDirSum_numRows = AHTDirSum_numRows + Repeat1__numRows
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<script src="sorttable.js"></script>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Westlake AHT Director Summary - CSS Reporting & Analysis</title>
<style type="text/css">

</style>
</head>

<body>
<table width="100%" border="0" cellpadding="1" cellspacing="0" bgcolor="#C6d7c6">
<tr>
<th width="57%" scope="col"><div align="left" class="style2">Report: AHT Director Summary </div></th>
<th width="43%" rowspan="5" scope="col"><img src="../National/fidlogo.gif" width="138" height="34" /> 
This information should be treated as confidential. </th>
</tr>
<tr>
<td><div align="left" class="style4">Reporting Period: <%=Request.Form("StartDate")%> - <%=Request.Form("EndDate")%></div></td>
</tr>
<tr>
<td><div align="left" class="style4">Level: Site (Westlake) </div></td>
</tr>
<tr>
<td><div align="left" class="style4">Practice: All Practices </div></td>
</tr>
<tr>
<td class="style4">Source: CSS Metrics </td>
</tr>
</table>
 

<table border="1" align="center" bordercolor="#EEEEEE" class="sortable" id="unique_id">
<tr>
<td>Director</td>
<td>Segment</td>
<td>Calls</td>
<td>AHT</td>
<td>Talk Time</td>
<td>Wrap Time</td>
<td>Hold Time</td>
<td>Goal</td>
<td>Variance</td>
</tr>
<% While ((Repeat1__numRows <> 0) AND (NOT AHTDirSum.EOF)) %>
<%Dim bgcolor
If Clng(AHTDirSum.Fields.Item("AHT").Value) < Clng(AHTDirSum.Fields.Item("Goal").Value) Then
bgcolor = "green"
Else
bgcolor = "red"
End If
%>
<tr>
<td><%=(AHTDirSum.Fields.Item("Director").Value)%></td>
<td><%=(AHTDirSum.Fields.Item("Segment").Value)%></td>
<td><%=(AHTDirSum.Fields.Item("Calls").Value)%></td>
<td bgcolor=" & bgcolor&"><%= FormatNumber((AHTDirSum.Fields.Item("AHT").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Talk Time").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Wrap Time").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Hold Time").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Goal").Value), 0, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Variance").Value), 2, -2, -2, -2) %></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
AHTDirSum.MoveNext()
Wend
%>
</table>
</body>
</html>
<%
AHTDirSum.Close()
Set AHTDirSum = Nothing
%>

russell

01-07-2007, 07:04 PM

this looks like the problem

change this line
<td bgcolor=" & bgcolor&"><%= FormatNumber((AHTDirSum.Fields.Item("AHT").Value), 2, -2, -2, -2) %></td>
to
<td bgcolor="<%=bgolor%>"><%= FormatNumber((AHTDirSum.Fields.Item("AHT").Value), 2, -2, -2, -2) %></td>

Chamark

01-07-2007, 07:15 PM

russell - I'm not getting any color now?

<% While ((Repeat1__numRows <> 0) AND (NOT AHTDirSum.EOF)) %>
<%Dim bgcolor
If Clng(AHTDirSum.Fields.Item("AHT").Value) < Clng(AHTDirSum.Fields.Item("Goal").Value) Then
bgcolor = "green"
Else
bgcolor = "red"
End If
%>
<tr>
<td><%=(AHTDirSum.Fields.Item("Director").Value)%></td>
<td><%=(AHTDirSum.Fields.Item("Segment").Value)%></td>
<td><%=(AHTDirSum.Fields.Item("Calls").Value)%></td>
<td bgcolor="<%=bgolor%>"><%= FormatNumber((AHTDirSum.Fields.Item("AHT").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Talk Time").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Wrap Time").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Hold Time").Value), 2, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Goal").Value), 0, -2, -2, -2) %></td>
<td><%= FormatNumber((AHTDirSum.Fields.Item("Variance").Value), 2, -2, -2, -2) %></td>
</tr>

Chamark

01-07-2007, 07:17 PM

Russell - I see the syntax error. It works great. Again thank you for your help and patience. You truly are a good person!!!

russell

01-07-2007, 08:19 PM

Glad to help. :)

Keep up the hard work and always post back if u run into issues. There are a lot of helpful folks here.

cheers!
russell