Document Delivery Web Reports

Follow

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.

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

Most Filled Journals query
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.

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 = ''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.

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(*y) 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.

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 = '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.

Electronic Delivery 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
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.

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

Reasons for Cancellation
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.

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''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.
0 out of 0 found this helpful

Comments

0 comments

Article is closed for comments.