The ILLiad Document Deliver Reports will allow you to run Document Delivery-specific reports regarding your requests, patrons, turnaround time, and other information.Transactions that have been routed from Borrowing into Document Delivery will only appear within Document Delivery reports, regardless of their originating process type. 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 Document Delivery Reports can be exported to Microsoft Excel.
The following Document Delivery Web Reports are available:
- Fill Rate Statistics
- Most Filled Journals
- Requests Received by Day
- Requests Filled by Day
- Turnaround Time
- Electronic Delivery Turnaround Time
- Requests by Department and User Status
- Reasons for Cancellation
- Requests Finished and Cancelled
Fill Rate Statistics
This report shows all of the requests that were submitted - broken down by articles and loans - for a 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''Doc Del''
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. |
| User Statuses | Limit results in the report based on user status. |
Most Filled Journals
This report shows the journals obtained through Document Delivery more than X times (X being a variable you can set) for a given time period.
SELECT DISTINCT ESPNumber, PhotoJournalTitle, 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''Doc Del''
AND t.CreationDate BETWEEN @beginDate AND @endDate
GROUP BY t.PhotoJournalTitle, t.ESPNumber
HAVING COUNT(t.PhotoJournalTitle) > 5
order by Number DESC, t.PhotoJournalTitle, t.ESPNumber
',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 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. |
| 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 = ''Doc Del''
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 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, Tracking k, Users u
WHERE t.TransactionNumber = k.TransactionNumber
AND (k.ChangedTo = 'Delivered to Web')
AND t.Username = u.Username
AND ProcessType = 'Doc Del'
AND k.DateTime > '1/1/2008 12:00:00 AM'
AND k.DateTime < '2/1/2008 12:00:00 AM'
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| 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. |
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 = 'Doc Del'
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 Document Delivery Processing'))
AND c.ChangedTo = 'In DD Stacks Searching'
AND ((d.ChangedTo = 'Awaiting Doc Del Customer Contact') OR (d.ChangedTo = 'Awaiting%Odyssey Scanning'))
AND ((e.ChangedTo LIKE '%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] > '1/1/2008'
AND e.[DateTime] < '2/1/2008'
GROUP BY t.TransactionNumber
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 = 'Doc Del'
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 Document Delivery Processing'))
AND c.ChangedTo = 'In DD Stacks Searching'
AND d.ChangedTo = 'Awaiting Doc Del Customer Contact'
AND e.ChangedTo LIKE '%Notified%'
AND b.[DateTime] > a.[DateTime]
AND c.[DateTime] > b.[DateTime]
AND d.[DateTime] > c.[DateTime]
AND e.[DateTime] > d.[DateTime]
AND e.[DateTime] > '1/1/2008 12:00:00 AM'
AND e.[DateTime] < '2/1/2008 12:00:00 AM'
GROUP BY t.TransactionNumber
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 the 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
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.RequestType = 'Article'
AND t.ProcessType = 'Doc Del'
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 Document Delivery Processing'))
AND c.ChangedTo = 'In DD Stacks Searching'
AND d.ChangedTo = 'Delivered to Web'
AND b.[DateTime] > a.[DateTime]
AND c.[DateTime] > b.[DateTime]
AND d.[DateTime] > c.[DateTime]
AND d.[DateTime] > '1/1/2008'
AND d.[DateTime]< '2/1/2008'
GROUP BY t.TransactionNumber
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 the report based on group affiliation and 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''Doc Del''
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. |
Reasons for Cancellation
This report shows the reasons for cancellation, the number of requests cancelled using each reason, and the percentage of cancellations for each reason for transactions cancelled during the given time period.
SELECT DISTINCT ReasonForCancellation, COUNT(t.TransactionNumber) as Number
FROM
Transactions t
LEFT JOIN Users u ON t.Username = u.Username
WHERE t.ProcessType = 'Doc Del'
AND (t.TransactionStatus LIKE 'Cancelled%')
and t.TransactionDate > '2/1/2008'
and t.TransactionDate < '3/1/2008'
GROUP BY ReasonForCancellation, TransactionStatus
ORDER BY Number DESC| 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 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''Doc Del'' 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 = ''Doc Del''
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 the type of request or include both types. |
| Sites | Limit report results based on processing site/delivery location. |