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