Lending 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 upon the report, visual charts and graphs may also be generated. You may choose how you wish the data to display 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 Lending Web Reports are available:

  • Fill Rate Statistics
  • Who We Lend To
  • Requests Received by Day
  • Requests Received by System ID
  • Requests Filled by Day
  • Requests Filled and Unfilled
  • Most Loaned Journals
  • Most Loaned Monographs
  • Most Unfilled Journals
  • Turnaround Time
  • Requests Submitted From Lending Web Page
  • IFM Charges

Fill Rate Statistics

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

Fill Rate Statistics query
select t.RequestType, t.TransactionStatus, count(t.RequestType) as Number 
from Transactions t, Users u 
WHERE t.Username = u.Username 
AND t.ProcessType = N''Lending'' 
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 the report based on group affiliation and processing site/delivery location.

Who We Lend To

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

Who We Lend To query
select t.lendinglibrary, count(DISTINCT t.TransactionNumber) as Number, l.libraryName as LibName, l.Address1 as Addy1 
from transactions t 
left join users u on t.username = u.username 
 LEFT JOIN LenderAddresses l ON (l.LenderString = t.LendingLibrary AND l.AddressNumber = (select min(temp1.AddressNumber) from LenderAddresses as temp1 where temp1.lenderstring = t.lendinglibrary)) 
where 
(t.TransactionStatus = N''Request Finished'' or t.TransactionStatus = N''Item Shipped'') 
and t.ProcessType = N''Lending'' 
and t.CreationDate BETWEEN @beginDate AND @endDate 
GROUP BY t.LendingLibrary, l.LibraryName, l.Address1 
HAVING (COUNT(DISTINCT t.TransactionNumber) > 5) 
 ORDER BY COUNT(DISTINCT t.TransactionNumber) DESC, t.LendingLibrary
',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 the type of request or include both types.
Number of Requests Limit results in the 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 the report based on group affiliation and 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 distinct 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 ProcessType = N''Lending''
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.
Groups by Site Limit results in the report based on group affiliation and processing site/delivery location.

Requests Received by System ID

This report shows the number of requests received by the system via a specific system ID. System ID options are OCLC, Docline (DOC), ISO ILL (ISO), Other (OTH), and Local (LOCL).

Requests Received by System ID query
SELECT DISTINCT SystemID, RequestType, COUNT(*) AS Number 
FROM Transactions t, Users u
WHERE
 t.Username = u.Username 
 AND ProcessType = N''Lending''
 AND t.CreationDate BETWEEN @beginDate AND @endDate 
GROUP BY
 SystemID, t.RequestType 
ORDER BY
 SystemID, 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.
Groups By Site Limit results in the report based on group affiliation and processing site/delivery location.

Requests Filled by Day

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

Requests Filled by Day query
SELECT DISTINCT
MONTH(k.datetime) as M, DAY(k.datetime) as D, YEAR(k.datetime) as Y, t.RequestType, count(*) as Number
FROM
Transactions t
JOIN Tracking k ON (t.TransactionNumber = k.TransactionNumber)
JOIN Users u ON (t.Username = u.Username)
WHERE
t.Username = u.Username
AND ((t.RequestType = N''Loan'' AND k.ChangedTo = N''Item Shipped'') OR (t.RequestType = N''Article'' AND k.ChangedTo = N''Request Finished''))
AND ProcessType = N''Lending''
AND k.DateTime > @beginDate
AND k.DateTime < @endDate
 group by Year(k.datetime), Month(k.datetime), Day(k.Datetime), t.requesttype 
 order by Year(k.datetime), Month(k.datetime), Day(k.Datetime), 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.
Groups By Site Limit results in the report based on group affiliation and processing site/delivery location.

Requests Filled and Unfilled

This report shows the number of requests filled and unfilled for a given time period. The unfilled 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 Filled and Unfilled 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''Lending'' 
 and t.CreationDate > @beginDate 
 and t.CreationDate < @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''Lending''
 and ((t.TransactionStatus = N''Request Finished'') or (t.TransactionStatus = N''Item Shipped''))
 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 the type of request or include both types.
Groups By Site Limit results in the report based on group affiliation and processing site/delivery location.

Most Loaned Journals

This report shows the journals loaned from Lending more than X times (X being a variable you can set) for a given time period.

Most Loaned Journals query
SELECT DISTINCT
 ESPNumber, PhotoJournalTitle, COUNT(ESPNumber) AS Number 
FROM
 Transactions t, Users u, LenderAddresses l
WHERE
 t.Username = u.Username 
 AND l.LenderString = t.LendingLibrary 
 AND l.AddressNumber = t.LenderAddressNumber 
 AND t.PhotoJournalTitle <> N'''' 
 AND t.RequestType = N''Article'' 
 AND ((t.TransactionStatus = N''Request Finished'') OR (t.TransactionStatus = N''Item Shipped'')) 
 AND t.ProcessType = N''Lending'' 
 AND t.CreationDate BETWEEN @beginDate AND @endDate 
GROUP BY t.ESPNumber, t.PhotoJournalTitle
HAVING COUNT(t.ESPNumber) > 5
order by Number DESC, t.ESPNumber, t.PhotoJournalTitle
',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.
Number of Requests Limit results in the 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 the report based on group affiliation and processing site/delivery location.

Most Loaned Monographs

This report shows the monographs loaned from Lending more than X times (X being a variable you can set) for a given time period.

Most Loaned Monographs query
SELECT DISTINCT
 ESPNumber, LoanTitle , COUNT(ESPNumber) AS Number 
FROM
 Transactions t, Users u, LenderAddresses l
WHERE
 t.Username = u.Username 
 AND l.LenderString = t.LendingLibrary 
 AND l.AddressNumber = t.LenderAddressNumber 
 AND t.LoanTitle <> N'''' 
 AND t.RequestType = N''Loan'' 
 AND ((t.TransactionStatus = N''Request Finished'') OR (t.TransactionStatus = N''Item Shipped'')) 
 AND t.ProcessType = N''Lending'' 
 AND t.CreationDate BETWEEN @beginDate AND @endDate 
GROUP BY t.ESPNumber, t.LoanTitle 
HAVING COUNT(t.ESPNumber) > 5
order by Number DESC, t.ESPNumber 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.
Group By The field that results should be grouped by.
Number of Requests Limit results in the 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 the report based on group affiliation and processing site/delivery location.

Most Unfilled Journals

This report shows the journals that were unfilled more than X times (X being a variable you can set) for a given time period.

Most Unfilled Journals query
SELECT DISTINCT
 ESPNumber, PhotoJournalTitle, ReasonforCancellation, COUNT(DISTINCT t.TransactionNumber) AS Number 
FROM
 Transactions t, Users u, LenderAddresses l
WHERE
 t.Username = u.Username 
 AND l.LenderString = t.LendingLibrary 
 AND l.AddressNumber = t.LenderAddressNumber 
 AND t.PhotoJournalTitle <> N'''' 
 AND t.RequestType = N''Article'' 
 AND (t.TransactionStatus = N''Cancelled by ILL Staff'') 
 AND t.ProcessType = N''Lending'' 
 AND t.CreationDate BETWEEN @beginDate AND @endDate 
GROUP BY t.ESPNumber, t.PhotoJournalTitle, t.ReasonForCancellation
HAVING COUNT(t.ESPNumber) > 5
order by Number DESC, t.ESPNumber DESC, t.PhotoJournalTitle, t.ReasonforCancellation
',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.
Number of Requests Limit results in the 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 the report based on group affiliation and 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 = 'Lending'
AND ((a.ChangedTo LIKE 'Imported from%') OR (a.ChangedTo = 'Request Added through Client') OR (a.ChangedTo = 'Awaiting Local Request Processing') OR (a.ChangedTo = 'Awaiting ISO ILL Request Processing') OR (a.ChangedTo = 'Submitted via Lending Web'))
AND b.ChangedTo = 'Request in Processing'
AND c.ChangedTo = 'In Stacks Searching'
AND (d.ChangedTo = 'Awaiting Shipping Label Printing' OR d.ChangedTo = 'Awaiting Lending Scanning')
AND e.ChangedTo = 'Request Finished'
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 = 'Lending'
AND ((a.ChangedTo LIKE 'Imported from%') OR (a.ChangedTo = 'Request Added through Client') OR (a.ChangedTo = 'Awaiting Local Request Processing') OR (a.ChangedTo = 'Awaiting ISO ILL Request Processing') OR (a.ChangedTo = 'Submitted via Lending Web'))
AND b.ChangedTo = 'Request in Processing'
AND c.ChangedTo = 'In Stacks Searching'
AND (d.ChangedTo = 'Awaiting Shipping Label Printing' OR d.ChangedTo = 'Awaiting Lending Scanning')
AND e.[DateTime] = (SELECT MIN(DateTime) FROM Tracking WHERE TransactionNumber = t.TransactionNumber AND ChangedTo = 'Item Shipped')
AND e.[DateTime] > '2/1/2011 12:00:00 AM'
AND e.[DateTime] < '3/1/2011 12:00:00 AM'
AND b.[DateTime] > a.[DateTime]
AND c.[DateTime] > b.[DateTime]
AND d.[DateTime] > c.[DateTime]
AND e.[DateTime] > d.[DateTime]
GROUP BY t.TransactionNumber, a.[DateTime], b.[DateTime], c.[DateTime], d.[DateTime], e.[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.
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 a report based on group affiliation and processing site/delivery location.

Requests Submitted From Lending Web Page

This report shows the number of requests submitted using the Lending web page in the given time period.

Requests Submitted from Lending Web Pages query
SELECT
TransactionNumber, ESPNumber, ILLNumber, TransactionDate, LendingString, RequestType
FROM
Transactions t, Users u
WHERE
t.Username = u.Username
AND t.SystemID = 'LWeb'
AND TransactionDate >= '2/1/2008'
AND TransactionDate < '3/1/2008'
ORDER BY TransactionDate, 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.
Sites Limit report results based on processing site/delivery location.

IFM Charges

This report shows the items billed via IFM for a given time period. This can be used to compare against the OCLC bills.

IFM Charges query
SELECT DISTINCT
t.TransactionNumber, t.LendingLibrary, t.ILLNumber, t.Username, t.IFMCost
FROM
Transactions t, Users u, Billing b, LenderAddresses l
WHERE
t.Username = u.Username
AND t.TransactionNumber = b.TransactionNumber
AND b.Processed = 'IFM'
AND ((t.TransactionStatus = 'Request Finished') OR (t.TransactionStatus = 'Item Shipped'))
AND t.ProcessType = 'Lending'
AND b.BillingDate > '1/1/2008 12:00:00 AM'
AND b.BillingDate < '2/1/2008 12:00:00 AM'
AND l.LenderString = t.LendingLibrary
AND l.AddressNumber = t.LenderAddressNumber
ORDER BY
t.LendingLibrary, t.ILLNumber, t.Username
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 the report based on group affiliation and processing site/delivery location.

Questions?

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

Feedback