Borrowing Web Reports

Follow

The ILLiad Borrowing Web Reports will allow you to run Borrowing-specific reports regarding your requests, patrons, turnaround time, and other information. You will be able to see report results in both grid and chart data, and you will be able to export data to Microsoft Excel. Hover over the parameters of the report (i.e., Begin Date, End Date, Sites, Groups) for an explanation of the specific parameter. Report data will be displayed in a grid. Depending on the report, visual charts and graphs may also be generated. You may choose how the data is displayed using the Chart Options and by rearranging and reordering the report grids. All generated Borrowing Web Reports can be exported to Microsoft Excel.

The following Borrowing Web Reports are available:

  • Fill Rate Statistics
  • Most Requested Journals
  • Most Requested Loans
  • Registered Users by Department
  • Requests Received by Day
  • Requests Sent by Day
  • Requests Sent by Hour
  • Requests by Department
  • Requests by Department and Status
  • Requests Finished
  • Requests Finished and Cancelled
  • Turnaround Time
  • Electronic Delivery Turnaround Time
  • Delivery Time Distribution
  • Who We Borrow From
  • Journals Received
  • WorldCat Information

Fill Rate Statistics

This report shows all of the requests that were submitted - broken down by articles and loans - in a specific time period. It also shows the percentage filled, cancelled and still in process for each request type.

Fill Rate Statistics query
SELECT DISTINCT t.RequestType, t.TransactionStatus, COUNT(DISTINCT t.TransactionNumber) as Number FROM
Transactions t LEFT JOIN Users u ON (t.Username = u.Username)
WHERE
t.ProcessType = N''Borrowing''
AND t.CreationDate BETWEEN @beginDate AND @endDate
GROUP BY t.RequestType, t.TransactionStatus
ORDER BY t.RequestType, t.TransactionStatus 
',N'@beginDate datetime,@endDate datetime',@beginDate='2014-07-01 00:00:00',@endDate='2014-08-01 00:00:00
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Groups By Site

Limit results in report based on group affiliation and processing site/
delivery location.

NOTE: If a lender belongs to more than one group and if you select
more than one of these groups in the report, you will get duplicate
transaction results for that lender.

User Statuses Limit results in report based on user status.

Most Requested Journals

This report shows the journals obtained through Borrowing more than X times (X being a variable you can set) for a given time period.

Most Requested Journals query
SELECT DISTINCT ESPNumber, PhotoJournalTitle, u.Department,  COUNT(ESPNumber) AS Number 
FROM
Transactions t LEFT JOIN Users u ON t.Username = u.Username
WHERE
t.ProcessType = N''Borrowing'' 
AND t.photojournaltitle <> N''''
AND ((t.TransactionStatus = N''Request Finished'') OR (t.TransactionStatus = N''Delivered to Web''))
AND t.CreationDate BETWEEN @startDate AND @endDate
GROUP BY t.ESPNumber, t.PhotoJournalTitle , u.Department
HAVING COUNT(t.ESPNumber) > 5
order by Number DESC, t.PhotoJournalTitle, t.ESPNumber
',N'@startDate nvarchar(8),@endDate nvarchar(8)',@startDate=N'7/1/2014',@endDate=N'8/1/2014
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Group By The field that results should be grouped by.
Number of Requests

Limit results in report based on the number of requests that
fit the criteria.
Typing 0 will return anything that fits the other criteria. Typing
higher numbers will limit the results.

Department Limit results in report based on user's department.
Groups By Site Limit results in report based on group affiliation and processing
site/delivery location.

Note that using the group feature may eliminate requests from your report that you would like to be included. For example, most RAPID requests do not have an ESPNumber so grouping by OCLC System Number would eliminate most RAPID request from the report.

  • Grouping by OCLC System Number will not return results that have a NULL ESPNumber.
  • Grouping by Title will not return results that have a NULL PhotoJournalTitle.
  • Grouping by ISxN will not return results that have a NULL ISxN.

Most Requested Loans

This report shows the Loan titles obtained through Borrowing more than X times (X being a variable you can set) for a given time period.

Most Requested Loans query
SELECT DISTINCT LoanTitle, COUNT(LoanTitle) AS Number
FROM Transactions t, Users u
WHERE t.Username = u.Username
and t.loantitle <> N''''
AND ((t.TransactionStatus = N''Request Finished'')
OR (t.TransactionStatus = N''Checked Out to Customer''))
AND t.ProcessType = N''Borrowing''
AND t.CreationDate BETWEEN @beginDate AND @endDate 
GROUP BY t.LoanTitle
HAVING COUNT(t.LoanTitle) > 5
order by Number DESC, t.LoanTitle
',N'@beginDate datetime,@endDate datetime',@beginDate='2014-07-01 00:00:00',@endDate='2014-08-01 00:00:00
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Number of Requests Limit results in report based on the number of requests that fit the
criteria.
Typing 0 will return anything that fits the other criteria. Typing higher
numbers will limit the results.
Groups by Site Limit results in report based on group affiliation and processing site/
delivery location.

Registered Users by Department

This report shows the departments registered and the number of users in each department. There is also the option of seeing pie charts for departments having more than X users registered (X being a variable you can set).

Registered Users by Department query
SELECT Status, COUNT(Status) AS StatusTotal FROM UsersALL WHERE
ISNULL(Status, '') <> '' AND ISNULL(Department, '') <> '' GROUP BY Status
ORDER BY Status SELECT Department, Status, COUNT(*) AS StatusCount FROM
UsersALL WHERE ISNULL(Status, '') <> '' AND ISNULL(Department, '') <> '' GROUP
BY Department, Status ORDER BY Department, Status SELECT COUNT(Username)
AS NumberOfElecDelUsers FROM UsersALL WHERE ISNULL(Status, '') <> '' AND
ISNULL(Department, '') <> '' AND Web = 'Yes'
Include Disavowed Users? Yes or No if disavowed users will be included in the report results.
Number of Users in
Department before
inclusion on Graph
Limit results in report based on the number of users that fit the criteria.
Typing 0 will return everything that fits the other criteria. Typing
higher numbers will limit the results.
Sites Limit report results based on processing site/delivery location.

Requests Received by Day

This report shows the number of requests received by ILL staff for a particular day in the given time period. There is a line graph option as well.

Requests Received by Day query
SELECT
MONTH(t.CreationDate) AS M, DAY(t.CreationDate) AS D, YEAR(t.CreationDate) AS Y, RequestType,  count(*) as 
Number  
FROM
Transactions t, Users u
WHERE
t.Username = u.Username
AND t.ProcessType = N''Borrowing''
AND t.CreationDate BETWEEN @beginDate AND @endDate
 group by Year(t.CreationDate), Month(t.CreationDate), Day(t.CreationDate), t.requesttype 
ORDER BY
YEAR(t.CreationDate), MONTH(t.CreationDate), DAY(t.CreationDate), t.RequestType
',N'@beginDate datetime,@endDate datetime',@beginDate='2014-07-01 00:00:00',@endDate='2014-08-01 00:00:00
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Sites Limit report results based on processing site/delivery location.

Requests Sent by Day

This report shows the number of requests sent by ILL staff for a particular day in the given time period. There is a line graph option as well.

Requests Sent by Day query
SELECT
MONTH(k.DateTime) AS M, DAY(k.DateTime) AS D, YEAR(k.DateTime) AS Y, RequestType, k.TransactionNumber
FROM
Transactions t, Tracking k, Users u
WHERE
t.TransactionNumber = k.TransactionNumber
AND t.Username = u.Username
AND k.ChangedTo = 'Request Sent'
AND k.DateTime > '1/1/2008'
AND k.DateTime < '2/1/2008'
ORDER BY
YEAR(k.DateTime), MONTH(k.DateTime), DAY(k.DateTime), t.RequestType, k.TransactionNumber
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Include Duplicate Transactions Yes or No to include transactions that appear more than once.
Sites Limit report results based on processing site/delivery location.

Requests Sent by Hour

This report shows the number of requests sent by ILL staff for a particular hour during the given day or date range.

The Requests Sent by Hour report allows you to enter a begin and end date so you can see more than one day at a time. By default, the report is configured to show the number of requests sent by hour for the previous day. The begin date is set to the previous day's date and the end date, because it is not inclusive, is set for the current date (today's date). To run the report for a longer time period, enter the beging and end dates. When running the report for multiple days, the cumulative total of number of requests sent per hour across all days will be used, i.e., the end date is included in the results. Note that you can only run the report for multiple days from the web; the ILLiad client defaults to yesterday's date when generating the report in the Reports group.

Requests Sent by Hour query
SELECT
t.SystemID, DATEPART(hh, k.DateTime) AS Hour, Count(DISTINCT k.TransactionNumber) AS Number
FROM
Transactions t, Tracking k, Users u
WHERE
t.TransactionNumber = k.TransactionNumber
AND t.Username = u.Username
AND k.ChangedTo = 'Request Sent'
AND k.DateTime >= '2/1/2011'
AND k.DateTime < '3/1/2011'
GROUP BY
DATEPART(hh, k.DateTime), t.SystemID
ORDER BY
DATEPART(hh, k.DateTime), t.SystemID
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Sites Limit report results based on processing site/delivery location.

Requests by Department

This report shows all the registered departments and the number of articles and loans obtained for each of those departments for the given time period.

Requests by Department query
SELECT
 u.Department, t.RequestType, COUNT(t.TransactionNumber) AS NumberActive 
FROM
 Users u, Transactions t 
WHERE
 u.Username = t.Username 
 AND ProcessType = N''Borrowing'' and ISNULL(Department, N'''') <> N''''
 AND t.CreationDate BETWEEN @beginDate and @endDate 
GROUP BY
 u.Department, t.RequestType 
ORDER BY
 u.department, t.requesttype
',N'@beginDate datetime,@endDate datetime',@beginDate='2014-07-01 00:00:00',@endDate='2014-08-01 00:00:00
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Sites Limit report results based on processing site/delivery location.

Requests by Department and User Status

This report shows all the registered customers by patron type and department and the number of requests obtained for each of those departments for the given time period.

Requests by Department and User Status query
select u.Department, u.Status, count(t.transactionnumber) as StatusCount from transactions t, users u 
 where t.username = u.username and t.ProcessType = N''Borrowing''
 and u.Department <> N'''' and u.Status <> N''''
 and t.CreationDate BETWEEN @beginDate and @endDate 
 GROUP BY u.department, u.status ORDER BY u.department, u.status
',N'@beginDate datetime,@endDate datetime',@beginDate='2014-07-01 00:00:00',@endDate='2014-08-01 00:00:00
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Sites Limit report results based on processing site/delivery location.

Requests Finished

This report shows the number of requests finished for a given time period.

Requests FInished query
SELECT RequestType, COUNT(t.RequestType) AS Number 
FROM
Transactions t JOIN Users u ON t.Username = u.Username
WHERE
t.ProcessType = N''Borrowing''
AND ((t.TransactionStatus = N''Request Finished'') OR (t.TransactionStatus = N''Delivered to Web'') or 
(t.TransactionStatus = N''Checked Out to Customer''))
AND t.CreationDate BETWEEN @beginDate and @endDate
GROUP BY t.RequestType
order by t.RequestType, Number DESC
',N'@beginDate datetime,@endDate datetime',@beginDate='2014-07-01 00:00:00',@endDate='2014-08-01 00:00:00
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Group By The field that results should be grouped by.
Groups By Site Limit results in report based on group affiliation and processing
site/delivery location.

Requests Finished and Cancelled

This report shows the number of requests finished and cancelled for a given time period. The cancelled requests break down by the reason for cancellation. There are two pie chart options for both the reasons for cancellation and the overall requests percentages.

Requests Finished and Cancelled query
SELECT DISTINCT ReasonForCancellation, COUNT(t.TransactionNumber) as Number 
 FROM Transactions t, Users u 
 WHERE t.Username = u.Username 
 and (t.transactionstatus LIKE N''Cancelled%'') 
 AND t.ProcessType = N''Borrowing'' 
 and t.CreationDate BETWEEN @beginDate and @endDate 
GROUP BY ReasonForCancellation, TransactionStatus 
order by Number DESC 
',N'@beginDate datetime,@endDate datetime',@beginDate='2014-07-01 00:00:00',@endDate='2014-08-01 00:00:00

select Count(t.TransactionNumber) as FinishedNumber 
 from Transactions t, Users u 
 where t.Username = u.Username 
 AND t.ProcessType = N''Borrowing'' 
 AND ((t.TransactionStatus = N''Request Finished'') OR (t.TransactionStatus = N''Delivered to Web'') OR 
 (t.TransactionStatus LIKE N''Customer Notified%'') OR (t.TransactionStatus = N''Checked Out to Customer''))
 and t.CreationDate BETWEEN @beginDate and @endDate 
',N'@beginDate datetime,@endDate datetime',@beginDate='2014-07-01 00:00:00',@endDate='2014-08-01 00:00:00
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Request Type Limit report results based on type of request, or include both types.
Sites Limit report results based on processing site/delivery location.

Turnaround Time

This report shows the amount of time required to fill articles, loans and both request types for a given period. It also shows the percentage of time spent by each part of the process. There is an option to turn on details for this report to view every request's turnaround time individually. Turning on details results in a very large report.

Turnaround Time query
SELECT
t.TransactionNumber,
MAX(a.[DateTime]) AS Step1,
MIN(b.[DateTime]) as Step2,
MIN(c.[DateTime]) as Step3,
MIN(d.[DateTime]) as Step4,
MIN(e.[DateTime]) AS Step5
FROM
Transactions t
JOIN Users u ON (t.Username = u.Username)
JOIN Tracking a ON (t.TransactionNumber = a.TransactionNumber)
JOIN Tracking b ON (t.TransactionNumber = b.TransactionNumber)
JOIN Tracking c ON (t.TransactionNumber = c.TransactionNumber)
JOIN Tracking d ON (t.TransactionNumber = d.TransactionNumber)
JOIN Tracking e ON (t.TransactionNumber = e.TransactionNumber)
WHERE
t.RequestType = 'Article'
AND t.ProcessType = 'Borrowing'
AND ((a.ChangedTo LIKE '%Submitted by Customer') OR (a.ChangedTo = 'Request Added through Client') OR 
(a.ChangedTo like 'Imported from%'))
AND ((b.ChangedTo = 'Request in Processing') OR (b.ChangedTo = 'Awaiting Direct Request Sending') OR 
(b.ChangedTo = 'Awaiting Rapid Request Sending') OR (b.ChangedTo = 'Awaiting OCLC Sending'))
AND c.ChangedTo = 'Request Sent'
AND ((d.ChangedTo = 'In Electronic Delivery Processing') OR (d.ChangedTo = 'Awaiting Odyssey Processing') OR 
(d.ChangedTo = 'Awaiting Post Receipt Processing'))
AND ((e.ChangedTo LIKE 'Customer%Notified%') OR (e.ChangedTo = 'Delivered to Web'))
AND b.[DateTime] > a.[DateTime]
AND c.[DateTime] > b.[DateTime]
AND d.[DateTime] > c.[DateTime]
AND e.[DateTime] > d.[DateTime]
AND e.[DateTime] > '2/1/2011'
AND e.[DateTime] < '3/1/2011'
GROUP BY t.TransactionNumber, a.[DateTime], b.[DateTime], c.[DateTime], d.[DateTime], e.[DateTime]
ORDER BY t.TransactionNumber

SELECT
t.TransactionNumber,
MAX(a.[DateTime]) AS Step1,
MIN(b.[DateTime]) as Step2,
MIN(c.[DateTime]) as Step3,
MIN(d.[DateTime]) as Step4,
MIN(e.[DateTime]) AS Step5
FROM
Transactions t
JOIN Users u ON (t.Username = u.Username)
JOIN Tracking a ON (t.TransactionNumber = a.TransactionNumber)
JOIN Tracking b ON (t.TransactionNumber = b.TransactionNumber)
JOIN Tracking c ON (t.TransactionNumber = c.TransactionNumber)
JOIN Tracking d ON (t.TransactionNumber = d.TransactionNumber)
JOIN Tracking e ON (t.TransactionNumber = e.TransactionNumber)
WHERE
t.RequestType = 'Loan'
AND t.ProcessType = 'Borrowing'
AND ((a.ChangedTo LIKE '%Submitted by Customer') OR (a.ChangedTo = 'Request Added through Client') OR 
(a.ChangedTo like 'Imported from%'))
AND ((b.ChangedTo = 'Request in Processing') OR (b.ChangedTo = 'Awaiting Direct Request Sending') OR 
(b.ChangedTo = 'Awaiting OCLC Sending'))
AND c.ChangedTo = 'Request Sent'
AND ((d.ChangedTo = 'In Electronic Delivery Processing') OR (d.ChangedTo = 'Awaiting Odyssey Processing') OR 
(d.ChangedTo = 'Awaiting Post Receipt Processing'))
AND ((e.ChangedTo LIKE 'Customer%Notified%') OR (e.ChangedTo = 'Delivered to Web'))
AND b.[DateTime] > a.[DateTime]
AND c.[DateTime] > b.[DateTime]
AND d.[DateTime] > c.[DateTime]
AND e.[DateTime] > d.[DateTime]
AND e.[DateTime] > '2/1/2011 12:00:00 AM'
AND e.[DateTime] < '3/1/2011 12:00:00 AM'
GROUP BY t.TransactionNumber, a.[DateTime], b.[DateTime], c.[DateTime], d.[DateTime], e.[DateTime]
ORDER BY t.TransactionNumber

SELECT
t.TransactionNumber,
MAX(a.[DateTime]) AS Step1,
MIN(b.[DateTime]) as Step2,
MIN(c.[DateTime]) as Step3,
MIN(d.[DateTime]) AS Step4
FROM
Transactions t
JOIN Users u ON (t.Username = u.Username)
JOIN Tracking a ON (t.TransactionNumber = a.TransactionNumber)
JOIN Tracking b ON (t.TransactionNumber = b.TransactionNumber)
JOIN Tracking c ON (t.TransactionNumber = c.TransactionNumber)
JOIN Tracking d ON (t.TransactionNumber = d.TransactionNumber)
WHERE
t.ProcessType = 'Borrowing'
AND ((a.ChangedTo LIKE '%Submitted by Customer') OR (a.ChangedTo = 'Request Added through Client') OR 
(a.ChangedTo like 'Imported from%'))
AND ((b.ChangedTo = 'Request in Processing') OR (b.ChangedTo = 'Awaiting Direct Request Sending') OR 
(b.ChangedTo = 'Awaiting Rapid Request Sending') OR (b.ChangedTo = 'Awaiting OCLC Sending'))
AND c.ChangedTo = 'Request Sent'
AND ((d.ChangedTo LIKE 'Customer%Notified%') OR (d.ChangedTo = 'Delivered to Web'))
AND b.[DateTime] > a.[DateTime]
AND c.[DateTime] > b.[DateTime]
AND d.[DateTime] > c.[DateTime]
AND d.[DateTime] > '2/1/2011 12:00:00 AM'
AND d.[DateTime] < '3/1/2011 12:00:00 AM'
GROUP BY t.TransactionNumber, a.[DateTime], b.[DateTime], c.[DateTime], d.[DateTime]
ORDER BY t.TransactionNumber

The results for this report include options to see the Overall turnaround time and also the Overall w/ Manually Processed turnaround time.

The Overall option will give you the average total turnaround time as well as the average time broken down to submitted to processing, processing to sent, sent to received, and received to notified.

The Overall w/ Manually Processed option will also give you an average turnaround time, but it is designed for workflows that may process a request from sent to notified and skip any received step (for example, changing the status from Request Sent to Customer Notified via E-mail or Request Finished without going to Awaiting Post Receipt Processing). This may include hard copy articles or other non-standard requests.

Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Show Detailed Records
for Articles

Show detailed records for articles to display every transaction
number, specific times for each step of the process for that
transaction, and the overall transaction turnaround time.

Selecting this option will result in a large report.

Show Detailed Records
for Loans

Show detailed records for loans to display every transaction
number, specific times for each step of the process for that
transaction, and the overall transaction turnaround time.

Selecting this option will result in a large report.

Groups By Site Limit results in report based on group affiliation and
processing site/delivery location.

Electronic Delivery Turnaround Time

This report shows the amount of time required to fill electronic articles for a given period. It also shows the percentage of time spent by each part of the process. There is an option to turn on details for this report to view every request's turnaround time individually. Turning on details results in a very large report.

Electronic Delivery Turnaround Time query
SELECT
SELECT
t.TransactionNumber,
MAX(a.[DateTime]) AS Step1,
MIN(b.[DateTime]) as Step2,
MIN(c.[DateTime]) as Step3,
MIN(d.[DateTime]) as Step4,
MIN(e.[DateTime]) AS Step5
FROM
Transactions t
JOIN Users u ON (t.Username = u.Username)
JOIN Tracking a ON (t.TransactionNumber = a.TransactionNumber)
JOIN Tracking b ON (t.TransactionNumber = b.TransactionNumber)
JOIN Tracking c ON (t.TransactionNumber = c.TransactionNumber)
JOIN Tracking d ON (t.TransactionNumber = d.TransactionNumber)
JOIN Tracking e ON (t.TransactionNumber = e.TransactionNumber)
WHERE
t.RequestType = 'Article'
AND t.ProcessType = 'Borrowing'
AND ((a.ChangedTo LIKE '%Submitted by Customer') OR (a.ChangedTo = 'Request Added through Client') OR 
(a.ChangedTo like 'Imported from%'))
AND ((b.ChangedTo = 'Request in Processing') OR (b.ChangedTo = 'Awaiting Direct Request Sending') OR 
(b.ChangedTo = 'Awaiting Rapid Request Sending') OR (b.ChangedTo = 'Awaiting OCLC Sending'))
AND c.ChangedTo = 'Request Sent'
AND ((d.ChangedTo = 'In Electronic Delivery Processing') OR (d.ChangedTo = 'Awaiting Odyssey Processing'))
AND (e.ChangedTo = 'Delivered to Web')
AND b.[DateTime] > a.[DateTime]
AND c.[DateTime] > b.[DateTime]
AND d.[DateTime] > c.[DateTime]
AND e.[DateTime] > d.[DateTime]
AND e.[DateTime] > '3/1/2011'
AND e.[DateTime] < '4/1/2011'
GROUP BY t.TransactionNumber, a.[DateTime], b.[DateTime], c.[DateTime], d.[DateTime], e.[DateTime]
ORDER BY t.TransactionNumber

SELECT
t.TransactionNumber,
MAX(a.[DateTime]) AS Step1,
MIN(b.[DateTime]) as Step2,
MIN(c.[DateTime]) as Step3,
MIN(d.[DateTime]) AS Step4
FROM
Transactions t
JOIN Users u ON (t.Username = u.Username)
JOIN Tracking a ON (t.TransactionNumber = a.TransactionNumber)
JOIN Tracking b ON (t.TransactionNumber = b.TransactionNumber)
JOIN Tracking c ON (t.TransactionNumber = c.TransactionNumber)
JOIN Tracking d ON (t.TransactionNumber = d.TransactionNumber)
WHERE
t.ProcessType = 'Borrowing'
AND ((a.ChangedTo LIKE '%Submitted by Customer') OR (a.ChangedTo = 'Request Added through Client') OR 
(a.ChangedTo like 'Imported from%'))
AND ((b.ChangedTo = 'Request in Processing') OR (b.ChangedTo = 'Awaiting Direct Request Sending')  OR 
(b.ChangedTo = 'Awaiting Rapid Request Sending') OR (b.ChangedTo = 'Awaiting OCLC Sending'))
AND c.ChangedTo = 'Request Sent'
AND d.ChangedTo = 'Delivered to Web'
AND b.[DateTime] > a.[DateTime]
AND c.[DateTime] > b.[DateTime]
AND d.[DateTime] > c.[DateTime]
AND d.[DateTime] > '3/1/2011 12:00:00 AM'
AND d.[DateTime] < '4/1/2011 12:00:00 AM'
GROUP BY t.TransactionNumber, a.[DateTime], b.[DateTime], c.[DateTime], d.[DateTime]
ORDER BY t.TransactionNumber
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Show Detailed Records
for Articles

Show detailed records for articles to display every transaction
number, specific times for each step of the process for that
transaction, and the overall transaction turnaround time.

Selecting this option will result in a large report.

Groups By Site Limit results in report based on group affiliation and processing
site/delivery location.

Delivery Time Distribution

This report graphs the number of Articles and Loans delivered for a given month and the number of days taken to do so. It also gives you a cumulative percentage of the items delivered over the course of the selected month.

Delivery Time Distribution query
SELECT
 t.RequestType, DATEDIFF (dd, t.CreationDate, b.DateTime) AS Dist, COUNT(DATEDIFF 
 (dd, t.CreationDate, b.DateTime)) AS TDist 
FROM
 Transactions t
 LEFT JOIN Tracking b ON (t.TransactionNumber = b.TransactionNumber)
 LEFT JOIN Users u ON (t.Username = u.Username)
WHERE
 t.ProcessType = N''Borrowing''
 AND (b.ChangedTo LIKE N''Customer Notified%'' OR b.ChangedTo = N''Delivered to Web'')
 AND DATEDIFF(dd, t.CreationDate, b.DateTime) > 0
 AND DATEDIFF(dd, t.CreationDate, b.DateTime) < 31
 AND (DATEPART(month,t.CreationDate) = @month)
 AND (DATEPART(year, t.CreationDate) = @year)
GROUP BY
 DATEDIFF(dd, t.CreationDate, b.DateTime), t.RequestType 
ORDER BY
 DATEDIFF(dd, t.CreationDate, b.DateTime), t.RequestType 
',N'@month int,@year int',@month=7,@year=2014

SELECT
 t.RequestType, COUNT(*) AS TDist 
FROM
 Transactions t LEFT JOIN Tracking b ON t.TransactionNumber = b.TransactionNumber 
WHERE
 t.ProcessType = N''Borrowing''
 AND (b.ChangedTo LIKE N''Customer Notified%'' OR b.ChangedTo = N''Delivered to Web'')
 AND DATEDIFF(dd, t.CreationDate, b.datetime) > 30
 AND (DATEPART(month,t.CreationDate) = @month)
 AND (DATEPART(year,t.CreationDate) = @year)
GROUP BY
 t.RequestType
ORDER BY
 t.RequestType
',N'@month int,@year int',@month=7,@year=2014
Month Month to display report results from.
Year Year to display report results from.
Groups By Site Limit results in report based on group affiliation and processing site/
delivery location.

Who We Borrow From

This report shows the libraries who supply more than X items in a given time period (X being a variable to set). It shows the number of requests filled by each library, ordered from greatest to least, and the percentage of the overall amount filled.

Who We Borrow From query
SELECT
 t.LendingLibrary, t.LenderAddressNumber, u.NVTGC, COUNT(DISTINCT t.TransactionNumber) AS Number 
FROM
 Transactions t, Users u
WHERE
 t.Username = u.Username 
 AND ((t.TransactionStatus = N''Request Finished'') OR (t.TransactionStatus = N''Checked Out to Customer'') 
OR (t.TransactionStatus = N''Delivered to Web'') OR (t.TransactionStatus LIKE N''Customer Notified via%''))
 AND t.ProcessType = N''Borrowing''
 AND t.CreationDate BETWEEN @beginDate AND @endDate
GROUP BY
 t.LendingLibrary, t.LenderAddressNumber, u.NVTGC 
HAVING
 (COUNT(DISTINCT t.TransactionNumber) > @numberOfRequests) 
ORDER BY
 COUNT(DISTINCT t.TransactionNumber) DESC, t.LendingLibrary
',N'@numberOfRequests int,@beginDate datetime,@endDate datetime',@numberOfRequests=5,@beginDate=
'2014-07-01 00:00:00',@endDate='2014-08-01 00:00:00
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Request Type Limit report results based on type of request, or include both types.
Number of Requests

Limit results in report based on the number of requests that fit the
criteria.

Typing 0 will return anything that fits the other criteria. Typing higher
numbers will limit the results.

Groups By Site Limit results in report based on group affiliation and processing site/
delivery location.

Journals Received

This report shows the journals obtained through Borrowing for a given time period.

Journals Received query
SELECT DISTINCT PhotoJournalTitle, PhotoJournalYear, COUNT(PhotoJournalTitle) AS Number 
FROM Transactions t, Users u
WHERE t.Username = u.Username 
and t.photojournaltitle <> N'''' 
AND ((t.TransactionStatus = N''Request Finished'') 
OR (t.TransactionStatus = N''Delivered to Web'')) 
AND t.ProcessType = N''Borrowing'' 
and t.CreationDate BETWEEN @beginDate AND @endDate 
GROUP BY t.PhotoJournalTitle, t.PhotoJournalYear 
HAVING COUNT(t.PhotoJournalTitle) > 5
order by t.PhotoJournalTitle, t.PhotoJournalYear 
',N'@beginDate datetime,@endDate datetime',@beginDate='2014-07-01 00:00:00',@endDate='2014-08-01 00:00:00
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Number of Requests

Limit results in report based on the number of requests that fit the
criteria.

Typing 0 will return anything that fits the other criteria. Typing higher
numbers will limit the results.

Groups By Site Limit results in report based on group affiliation and processing site/
delivery location.

WorldCat Information

This report shows items requested from OCLC and their current transaction status based off of call number ranges provided in the report parameters.

WorldCat Information query
select distinct t.TransactionNumber, t.RequestType, Title =  
CASE RequestType 
WHEN ''Loan'' THEN LoanTitle 
ELSE PhotoJournalTitle 
END, t.CallNumber, w.Type, w.Data, t.ESPNumber, t.ISSN, t.TransactionStatus, u.Department, u.Status 
from transactions t inner join users u on t.username = u.username 
inner join tracking k on t.transactionnumber = k.transactionnumber 
inner join WorldCatInformation w on t.transactionnumber = w.transactionnumber 
LEFT JOIN LenderAddresses l ON (l.LenderString = t.LendingLibrary and l.AddressNumber = 
t.LenderAddressNumber)
WHERE t.CreationDate BETWEEN @beginDate AND @endDate
ORDER BY t.TransactionNumber
',N'@beginDate datetime,@endDate datetime',@beginDate='2014-07-01 00:00:00',@endDate='2014-08-01 00:00:00
Begin Date The beginning date of the report. This date is inclusive.
End Date The end date of the report. This date is not inclusive.
Groups By Site Limit results in report based on group affiliation and processing site/
delivery location.
Call Number (LC) LC call number. Use % for a wildcard character.
Call Number (NAL) NAL call number. Use % for a wildcard character.
Classification Number
(DEWEY)
Dewey classification number. Use % for a wildcard character.

Questions?

If this article didn’t resolve your issue, please take a moment and answer a few questions to help improve our documentation:

Feedback