Administrative Web Reports

Follow

The ILLiad Administrative Web Reports will allow you to run specific reports regarding copyright, borrowing and lending libraries, staff activity, 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 Administrative Web Reports can be exported to Microsoft Excel.

The following Administrative Web Reports are available:

  • Copyright
  • Borrowing Invoices Received
  • Lending Library
  • Customers Cleared
  • Outstanding Requests
  • Requests by Username
  • Staff Activity by Username

Copyright

The Copyright report shows the items requiring payment of copyright royalties to the Copyright Clearance Center for a given time period.

Copyright query
SELECT
PhotoJournalTitle, t.TransactionNumber, ISSN, CCCNumber, CCCOrder, PhotoJournalYear, Pages, 1 AS NoOfCopies, (ISNULL(BaseFee, 0) + ISNULL((PerPage * Pages), 0)) AS TotalFees
FROM
Transactions t
LEFT JOIN Users u ON t.Username = u.Username
LEFT JOIN Tracking k ON t.TransactionNumber = k.TransactionNumber
LEFT JOIN CopyrightSessions cs ON t.TransactionNumber = cs.TransactionNumber
WHERE
k.ChangedTo = 'Awaiting Copyright Clearance'
AND t.PhotoJournalTitle <> '' AND ((t.TransactionStatus = 'Request Finished') OR (t.TransactionStatus = 'Delivered to Web'))
AND CopyrightAlreadyPaid = 'No'
AND t.ProcessType = 'Borrowing'
AND k.DateTime > '1/1/2008 12:00:00 AM'
AND k.DateTime < '2/1/2008 12:00:00 AM'
GROUP BY
t.PhotoJournalTitle, t.PhotoJournalYear, t.TransactionNumber, t.ISSN, t.CCCNumber, t.CCCOrder, t.Pages, t.BaseFee, t.PerPage
HAVING
((ISNULL(BaseFee, 0) + ISNULL((PerPage * Pages), 0) > 0) AND Count(cs.OrderDetail) = 0)
ORDER BY
t.PhotoJournalTitle, t.PhotoJournalYear, t.TransactionNumber, t.ISSN, t.CCCNumber, t.CCCOrder, t.Pages, t.BaseFee, t.PerPage

SELECT PhotoJournalTitle, t.TransactionNumber, t.ISSN, CCCNumber, PhotoJournalYear, Pages, 1 AS NoOfCopies, (ISNULL(BaseFee, 0) + ISNULL((PerPage * Pages), 0)) AS TotalFees 
FROM Transactions t
INNER JOIN Users u ON t.Username = u.Username
INNER JOIN Tracking k ON t.TransactionNumber = k.TransactionNumber
WHERE k.ChangedTo = 'Awaiting Copyright Clearance' and t.photojournaltitle <> '' 
AND ((t.TransactionStatus <> 'Request Finished') 
AND (t.TransactionStatus <> 'Delivered to Web') 
AND (NOT t.TransactionStatus LIKE 'Cancelled%')) 
and CopyrightAlreadyPaid = 'No'
AND ((ISNULL(BaseFee, 0) + ISNULL((PerPage * Pages), 0)) <> 0)
AND t.ProcessType = 'Borrowing' 
AND k.DateTime > '1/1/2008 12:00:00 AM' 
AND k.DateTime < '2/1/2008 12:00:00 AM' 
group by t.PhotoJournalTitle, t.PhotoJournalYear, t.TransactionNumber, t.ISSN, t.CCCNumber, t.Pages, t.BaseFee, t.PerPage 
order by t.PhotoJournalTitle, t.PhotoJournalYear, t.TransactionNumber, t.ISSN, t.CCCNumber, t.Pages, t.BaseFee, t.PerPage

SELECT
t.PhotoJournalTitle, t.TransactionNumber, t.ISSN, t.CCCNumber, t.PhotoJournalYear, cs.OrderHeader, cs.OrderDetail, t.Pages, 1 AS NoOfCopies, t.BaseFee AS TotalFees
FROM
CopyrightSessions cs
LEFT JOIN Transactions t ON t.TransactionNumber = cs.TransactionNumber
LEFT JOIN Users u ON t.Username = u.Username
LEFT JOIN Tracking k on t.TransactionNumber = k.TransactionNumber
WHERE
k.ChangedTo = 'Awaiting Copyright Clearance'
AND t.PhotoJournalTitle <> '' AND ((t.TransactionStatus = 'Request Finished') OR (t.TransactionStatus = 'Delivered to Web'))
AND (NOT(BaseFee IS NULL))
AND (NOT(BaseFee * Pages = 0))
AND t.ProcessType = 'Borrowing'
AND k.DateTime > '1/1/2008 12:00:00 AM'
AND k.DateTime < '2/1/2008 12:00:00 AM'
GROUP BY
t.PhotoJournalTitle, t.PhotoJournalYear, t.TransactionNumber, t.ISSN, t.CCCNumber, t.Pages, t.BaseFee, t.PerPage, cs.OrderHeader, cs.OrderDetail
HAVING
Count(cs.OrderDetail) > 0
ORDER BY
t.PhotoJournalTitle, t.PhotoJournalYear, t.TransactionNumber, t.ISSN, t.CCCNumber, t.Pages, t.BaseFee, t.PerPage, cs.OrderHeader, cs.OrderDetail
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 a report based on group affiliation and processing site/delivery location.

Borrowing Invoices Received

This report shows the borrowing items that had an invoice recorded for a given time period.

Borrowing Invoices Received query
SELECT
DISTINCT i.TransactionNumber, i.InvoiceNumber, t.LendingLibrary, l.LibraryName, i.InvoiceDate, i.EntryDate, i.ItemAmount, i.OtherFees
FROM
Transactions t
JOIN invoices i ON (t.TransactionNumber = i.transactionnumber)
JOIN Users u ON (t.Username = u.Username)
LEFT JOIN LenderAddresses l ON (l.LenderString = t.LendingLibrary and l.AddressNumber = t.LenderAddressNumber)
WHERE
t.ProcessType = 'Borrowing'
AND i.EntryDate > '1/1/2008 12:00:00 AM'
AND i.EntryDate < '2/1/2008 12:00:00 AM'
ORDER BY
i.EntryDate
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.

Lending Library

This report shows the items sent to a given library for a given time period.

Lending Library query
select t.TransactionNumber, ILLNumber, ESPNumber, TransactionStatus, TransactionDate, LendingString, 
LendingLibrary 
FROM Transactions t, users u
WHERE t.Username = u.Username and t.LendingString like N''%'' + @symbol + ''%''
AND t.ProcessType = N''Borrowing'' and t.CreationDate BETWEEN @beginDate AND @endDate
order by ILLNumber, t.TransactionNumber 
',N'@symbol nvarchar(5),@beginDate datetime,@endDate datetime',@symbol=N'TRNAA',@beginDate='2012-07-01 00:00:00',@endDate='2012-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.
Library Symbol The OCLC library symbol of a specific library.
Sites Limit report results based on processing site/delivery location.

Customers Cleared

This report shows the customers cleared or edited (by the customer) for a given time period.

Customers Cleared query
SELECT
Firstname, Lastname, Username, NVTGC, DeliveryMethod, LoanDeliveryMethod, Address, Address2, City, State, Zip, LastChangedDate, Status, Department, Cleared
FROM
Users
WHERE
LastChangedDate > '1/1/2008 12:00:00 AM' AND LastChangedDate < '2/1/2008 12:00:00 AM'
ORDER BY
LastChangedDate, 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.
Sites Limit report results based on processing site/delivery location.

Outstanding Requests

This report shows the items that are at status Request Sent for more than X number of days.

Outstanding Requests query
SELECT
TransactionNumber, ESPNumber, ILLNumber, TransactionDate, LendingString, SystemID, RequestType
FROM
Transactions t, Users u
WHERE
t.Username = u.Username
AND t.TransactionStatus = 'Request Sent'
AND t.ProcessType = 'Borrowing'
AND DateAdd(dd, 5, TransactionDate) < getdate()
ORDER BY
TransactionDate, t.TransactionNumber
Number of Days at
Request Sent
Limit results in the report based on the number of days a request has been at the status of Request Sent.
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 by Username

This report shows the items successfully filled for each username for a given time period.

Requests by Username query
Select u.Username, u.Lastname, u.Firstname, u.Department, u.Status, t.RequestType, 
COUNT(t.TransactionNumber) 
AS NumberActive 
 FROM Users u, Transactions t where u.UserName = t.Username 
 AND ProcessType = @requestType and t.CreationDate BETWEEN @beginDate AND @endDate
 GROUP BY u.Username, u.Lastname, u.Firstname, u.Department, u.Status, t.RequestType ORDER BY u.Username, u.Lastname, u.Firstname, u.Department, u.Status, t.requesttype ',N'@requestType varchar(9),@beginDate datetime,@endDate datetime',@requestType=N'Borrowing', @beginDate='2012-07-01 00:00:00',@endDate='2012-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.
Process Type Limit report results based on process type (Borrowing, Lending, Document Delivery)
Sites Limit report results based on processing site/delivery location.

Staff Activity by Username

This report shows the statuses that staff moved requests to during a given time period.

Staff Activity by Username query
SELECT
DISTINCT ChangedTo, ChangedBy, COUNT(DISTINCT t.TransactionNumber) AS NumberOfTransactions
FROM
Transactions t
INNER JOIN Tracking k ON t.TransactionNumber = k.TransactionNumber
INNER JOIN Staff s ON k.ChangedBy = s.Username
WHERE
t.ProcessType = 'Borrowing'
AND k.DateTime >= '1/1/2008'
AND k.DateTime < '2/1/2008'
AND k.ChangedTo NOT IN ('Request in Processing')
GROUP BY
ChangedBy, ChangedTo
ORDER BY
ChangedTo, ChangedBy
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.
Process Type Limit report results based on process type (Borrowing, Lending, Document Delivery)
Sites Limit report results based on 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