spartacus132
08-15-2006, 06:05 AM
hi there,
i am trying to produce a report in excel and not knowing much i am sort of stuck. Hopefully, one of you can help me on this matter.
The sql below is pulling out how many days help logs from a certain facility have been open. using that information, i want to create a report that puts the query result into brackets of days open.
Basically, i want to show the count of logs in a particular bracket [I am attaching a sample report (samplereport.doc) to help you visualize the information and layout].
The report should provide a count of the number of logs that remain open at each facility for a varying numbers of days. The last column in the report does a count of how many call logs with the call status of "Long Term" were recived by the facility.
Sub LogAging()
' Log Aging
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=xxxxxxxserver;UID=xxxxxxx;APP=Microsoft Office 2003;WSID=xxxxxxx;DATABASE=xxxxxxxdatabase;Trusted_Connection=Yes" _
, Destination:=Range("A7"))
.CommandText = Array( _
"SELECT CallLog.CallStatus, CallLog.RecvdDate, DATEDIFF(Day, CallLog.RecvdDate, GETDATE()) AS Exp1, Subset.HospID, Subset.CallID" & Chr(13) & "" & Chr(10) _
"FROM heatprod.dbo.CallLog CallLog, heatprod.dbo.Subset Subset" & Chr(13) & "" & Chr(10) & "WHERE CallLog.CallID = Subset.CallID AND ((CallLog.CallStatus" _
, " Not Like 'closed%'))" & Chr(13) & "" & Chr(10) & "ORDER BY Subset.CallID")
.Name = "LogAging"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
End Sub
The above query supplies the information need to complete the report, the only two columns that are needed for creating the report are Exp1 and HospID. The file--queryresult.xls is populated with values for the above two columns.
I would really appreciate any help regarding this post.
Thanks,
Anupam
i am trying to produce a report in excel and not knowing much i am sort of stuck. Hopefully, one of you can help me on this matter.
The sql below is pulling out how many days help logs from a certain facility have been open. using that information, i want to create a report that puts the query result into brackets of days open.
Basically, i want to show the count of logs in a particular bracket [I am attaching a sample report (samplereport.doc) to help you visualize the information and layout].
The report should provide a count of the number of logs that remain open at each facility for a varying numbers of days. The last column in the report does a count of how many call logs with the call status of "Long Term" were recived by the facility.
Sub LogAging()
' Log Aging
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER=xxxxxxxserver;UID=xxxxxxx;APP=Microsoft Office 2003;WSID=xxxxxxx;DATABASE=xxxxxxxdatabase;Trusted_Connection=Yes" _
, Destination:=Range("A7"))
.CommandText = Array( _
"SELECT CallLog.CallStatus, CallLog.RecvdDate, DATEDIFF(Day, CallLog.RecvdDate, GETDATE()) AS Exp1, Subset.HospID, Subset.CallID" & Chr(13) & "" & Chr(10) _
"FROM heatprod.dbo.CallLog CallLog, heatprod.dbo.Subset Subset" & Chr(13) & "" & Chr(10) & "WHERE CallLog.CallID = Subset.CallID AND ((CallLog.CallStatus" _
, " Not Like 'closed%'))" & Chr(13) & "" & Chr(10) & "ORDER BY Subset.CallID")
.Name = "LogAging"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
End Sub
The above query supplies the information need to complete the report, the only two columns that are needed for creating the report are Exp1 and HospID. The file--queryresult.xls is populated with values for the above two columns.
I would really appreciate any help regarding this post.
Thanks,
Anupam