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.
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/ NOTE: If a lender belongs to more than one group and if you select |
| 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.
This report is looking for duplicate requests based on the ESPNumber (usually OCLC Number) and will not include items which may have the same title but was requested on a record with a different OCLC number. This report will likely be different than your Journal Title List in the Copyright Clearance processing window.
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 |
| 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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
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.TransactionNumberThe 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 Selecting this option will result in a large report. |
| Show Detailed Records for Loans |
Show detailed records for loans to display every transaction 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.
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 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.
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.
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 Typing 0 will return anything that fits the other criteria. Typing higher |
| 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.
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 Typing 0 will return anything that fits the other criteria. Typing higher |
| 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.
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. |