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.
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
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. |