I'm trying to add an export function on to our intranet. I currently have it setup to display the export button only after the report search has been run. If you click on the export button after the search has been run, it calls another ASP page with the following:
I am running 2003 SBE and SQL Server. If there is any other information needed, let me know.
It will prompt you to save the excel file on your computer. The problem I am running into is getting the generated report information into the excel file. Was hoping someone might be able to help me out. I will post the code in hopes it might help:
When the report is run for each indiviual rep, it will provide us with the customers first and last name, the appointment time, and how they heard about us. I only need to gather information on name and how they heard about us and put that into the excel file. If any of you have any ideas about how I can pull that information into the excel file it would be greatly appreciated.
Enkrypted
A+
Code:
<%
Response.ContentType ="Application/vnd.excel"
Response.AddHeader "Content-Disposition", "attachment; filename=qireport.xls"
%>I am running 2003 SBE and SQL Server. If there is any other information needed, let me know.
It will prompt you to save the excel file on your computer. The problem I am running into is getting the generated report information into the excel file. Was hoping someone might be able to help me out. I will post the code in hopes it might help:
Code:
<%
If QIRGetDatesSubmitted = 1 THEN
Select case Request("QIRType")
CASE "newqi"
SQL = "SELECT * FROM ViewQISheetsDetail WHERE (AppTime IS NOT NULL) "
SQL = SQL & "AND (AssignedTo = '" & QIRAssignedTo & "') "
SQL = SQL & "AND (AppTime >= '" & QIRStartDate & " 12:01 AM') "
SQL = SQL & "AND (AppTime <= '" & QIREndDate & " 11:59 PM') "
SQL = SQL & "AND (QISheetCancelled IS NULL and QISheetCancelled <> '' or QISheetCancelled <> '1') ORDER BY AppTime"
CASE "cancelled"
SQL = "SELECT * FROM ViewQISheetsDetail WHERE (AppTime IS NOT NULL) "
SQL = SQL & "AND (AppTime >= '" & QIRStartDate & " 12:01 AM') "
SQL = SQL & "AND (AppTime <= '" & QIREndDate & " 11:59 PM') "
SQL = SQL & "AND (QISheetCancelled = 1) ORDER BY AppTime"
CASE "dead"
SQL = "SELECT * FROM ViewFollowUpCalls WHERE "
SQL = SQL & "((FirstCallMadeOn >= '" & QIRStartDate & " 12:01 AM') OR "
SQL = SQL & "(FirstCallMadeOn <= '" & QIREndDate & " 11:59 PM') OR "
SQL = SQL & "(SecondCallMadeOn >= '" & QIRStartDate & " 12:01 AM') OR "
SQL = SQL & "(SecondCallMadeOn <= '" & QIREndDate & " 11:59 PM') OR "
SQL = SQL & "(ThirdCallMadeOn >= '" & QIRStartDate & " 12:01 AM') OR "
SQL = SQL & "(ThirdCallMadeOn <= '" & QIREndDate & " 11:59 PM') OR "
SQL = SQL & "(FourthCallMadeOn >= '" & QIRStartDate & " 12:01 AM') OR "
SQL = SQL & "(FourthCallMadeOn <= '" & QIREndDate & " 11:59 PM')) "
SQL = SQL & "AND LeadIsDead = 1"
End Select
' get results for report
alldata = getRecordSetToArray(SQL)
' check that there is data present
IF alldata(0,0) = "NO RECORDS FOUND" THEN
' no records
%>
<table cellpadding="0" cellspacing="0" border="0" width="100%">
<tr>
<TD><font class="small"><b>No QI Reports Found</b></font></TD>
</tr>
</table>
<%
ELSEIF alldata(0,0) = "INVALID SQL QUERY" THEN
' invalid SQL
%>
<table cellpadding="0" cellspacing="0" border="0" width="100%">
<tr>
<TD><font class="small"><b>There is an error in the search submitted: <br><%=SQL %><br></b></font></TD>
</tr>
</table>
<%
ELSE
' build report data
strReportData = mergeFormattedStringWithArray(strFormatReportData,alldata)
%>
<%
Const cID = 0
Const cCustomerID = 1
Const cCreationDate = 2
Const cScopeOfWork = 3
Const cCallBackConfirmed = 4
Const cAppTime = 5
Const cAssignedTo = 6
Const cExpr1 = 7
Const cTitle = 8
cFirstName = 9
cLastName = 10
if Request("QIRType") = "dead" Then
cFirstName = 8
cLastName = 9
End if
Const cAddress1 = 11
Const cAddress2 = 12
Const cCity = 13
Const cState = 14
Const cZip = 15
Const cSubdivision = 16
Const cEmail = 17
Const cHomePhone = 18
Const cWorkPhone = 19
Const cCellPhone = 20
Const cDirections = 21
Const cCustomerCreationDate = 22
Const cHeardAboutUsID = 23
Const cHeardAboutUsCustomerID = 24
Const cDirectMail = 25
Const cSNP = 26
Const cYellowPages = 27
Const cReferral = 28
Const cReferralText = 29
Const cNeighbor = 30
Const cNeighborText = 31
Const cAngies = 32
Const cMike = 33
Const cInternet = 34
Const cPreviousCust = 35
Const cServMag = 36
Const cBBB = 37
Const cSawSign = 38
Const cSawTruck = 39
Const cOther = 40
Const cOtherText = 41
Const cQISheetCancelled = 42
Const cQISheetCancelledBy = 43
%>
<TABLE cellpadding="0" cellspacing="0" border="0" width="100%">
<TR>
<TD><FONT class="small"><B>Lastname, Firstname</TD>
<TD> </TD>
<% If Request("QIRType") <> "dead" then %>
<TD><FONT class="small"><B>Appointment Time</TD>
<TD> </TD>
<TD><FONT class="small"><B>How They Heard About Us</TD>
<% end if %>
</TR>
<%
for x = 0 to Ubound(alldata,2)
IF BGCOLOR="#FFFFFF" THEN
BGCOLOR="#E1ECF7"
Else
BGCOLOR="#FFFFFF"
END IF
Heard = ""
on error resume next
If alldata(cDirectMail, x) THEN
Heard = Heard & "Direct Mail<BR>"
END IF
If alldata(cSNP, x) THEN
Heard = Heard & "SNP<BR>"
END IF
If alldata(cYellowPages, x) THEN
Heard = Heard & "Yellow Pages<BR>"
END IF
If alldata(cReferral, x) THEN
Heard = Heard & "Referral<BR>"
END IF
If Len(trim(alldata(cReferralText, x))) > 0 THEN
Heard = Heard & alldata(cReferralText, x) & "<BR>"
END IF
If alldata(cNeighbor, x) THEN
Heard = Heard & "Neighbor<BR>"
END IF
If Len(trim(alldata(cNeighborText, x))) > 0 THEN
Heard = Heard & alldata(cNeighborText, x) & "<BR>"
END IF
If alldata(cAngies, x) THEN
Heard = Heard & "Angies<BR>"
END IF
If alldata(cMike, x) THEN
Heard = Heard & "Mike<BR>"
END IF
If alldata(cInternet, x) THEN
Heard = Heard & "Internet<BR>"
END IF
If alldata(cPreviousCust, x) THEN
Heard = Heard & "Previous Cust<BR>"
END IF
If alldata(cServMag, x) THEN
Heard = Heard & "Serv Mag<BR>"
END IF
If alldata(cBBB, x) THEN
Heard = Heard & "BBB<BR>"
END IF
If alldata(cSawSign, x) THEN
Heard = Heard & "Saw Sign<BR>"
END IF
If alldata(cSawTruck, x) THEN
Heard = Heard & "SawTruck<BR>"
END IF
If alldata(cOther, x) THEN
Heard = Heard & "Other<BR>"
END IF
If Len(trim(alldata(cOtherText, x))) > 0 THEN
Heard = Heard & alldata(cOtherText, x) & "<BR>"
END IF
on error goto 0
ThisAppTime = FormatDate(alldata(cAppTime, x), "n/j/y") & " " & FormatDate(alldata(cAppTime, x), "g:i A")
%>
<TR bgcolor="<%=BGCOLOR%>">
<TD colspan="5"><IMG SRC="images/spacer.gif" width="1" height="4"></TD>
</TR>
<TR bgcolor="<%=BGCOLOR%>">
<TD><FONT class="normal"><%=alldata(cLastName, x)%>, <%=alldata(cFirstname, x)%></TD>
<TD> </TD>
<% If Request("QIRType") <> "dead" then %>
<TD><FONT class="normal"><%=ThisAppTime%></TD>
<TD> </TD>
<TD><FONT class="normal"><%=Heard%></TD>
<% End If %>
</TR>
<TR bgcolor="<%=BGCOLOR%>">
<TD colspan="5"><IMG SRC="images/spacer.gif" width="1" height="4"></TD>
</TR>
<% Next %>
</TABLE>
<BR>
<form name="form1" method="post" action="exportinfo.asp">
<input type="submit" name="Submit" value="Export to Excel">
</form>
<BR><BR><BR><BR><BR><BR>
<%
' end of check for data
END IF
' end of check for submitted form
END IF
%>
<Script language="javascript">
function CheckWholeForm()
{
}
function SubmitFormYear()
{
var today = new Date()
var month = today.getMonth() + 1
var day = today.getDate()
var year = today.getFullYear()
var s = "/"
var jsEndDate = month + s + day + s + year
var jsFullStartDate = DateAdd('yyyy', -1, jsEndDate)
var Smonth = jsFullStartDate.getMonth() + 1
var Sday = jsFullStartDate.getDate()
var Syear = jsFullStartDate.getFullYear()
var jsStartDate = Smonth + s + Sday + s + Syear
document.GetDates.QIRStartDate.value = jsStartDate;
document.GetDates.QIREndDate.value = jsEndDate;
document.GetDates.submit();
}
function SubmitFormMonth()
{
var today = new Date()
var month = today.getMonth() + 1
var day = today.getDate()
var year = today.getFullYear()
var s = "/"
var jsEndDate = month + s + day + s + year
var Smonth = today.getMonth()
var Sday = today.getDate()
var Syear = today.getFullYear()
//alert('Smonth=' + Smonth);
//alert('Sday=' + Sday);
if ((Smonth == 2) && (Sday > 28))
{
Sday = 28;
}
//30 days hath September (9), April (4), June(6) and November(11)
if (((Smonth == 9)||(Smonth == 4)||(Smonth == 6)||(Smonth == 11)) && (Sday > 30))
{
Sday = 30;
}
var jsStartDate = Smonth + s + Sday + s + Syear
document.GetDates.QIRStartDate.value = jsStartDate;
document.GetDates.QIREndDate.value = jsEndDate;
document.GetDates.submit();
}
function SubmitFormWeek()
{
var today = new Date()
var month = today.getMonth() + 1
var day = today.getDate()
var year = today.getFullYear()
var s = "/"
var jsEndDate = month + s + day + s + year
var jsFullStartDate = DateAdd('ww', -1, jsEndDate)
var Smonth = jsFullStartDate.getMonth() + 1
var Sday = jsFullStartDate.getDate()
var Syear = jsFullStartDate.getFullYear()
var jsStartDate = Smonth + s + Sday + s + Syear
document.GetDates.QIRStartDate.value = jsStartDate;
document.GetDates.QIREndDate.value = jsEndDate;
document.GetDates.submit();
}
function SubmitFormDay()
{
var today = new Date()
var month = today.getMonth() + 1
var day = today.getDate()
var year = today.getFullYear()
var s = "/"
var jsEndDate = month + s + day + s + year
var jsFullStartDate = DateAdd('d', -1, jsEndDate)
var Smonth = jsFullStartDate.getMonth() + 1
var Sday = jsFullStartDate.getDate()
var Syear = jsFullStartDate.getFullYear()
var jsStartDate = Smonth + s + Sday + s + Syear
document.GetDates.QIRStartDate.value = jsStartDate;
document.GetDates.QIREndDate.value = jsEndDate;
document.GetDates.submit();
}
</SCRIPT>
<script type="text/javascript" src="b.js"></script>When the report is run for each indiviual rep, it will provide us with the customers first and last name, the appointment time, and how they heard about us. I only need to gather information on name and how they heard about us and put that into the excel file. If any of you have any ideas about how I can pull that information into the excel file it would be greatly appreciated.
Enkrypted
A+
