Technical Services Reports

Cataloging Reports

BibTOM / Item Material Type Combinations

Returns a list of all Bibliographic Type of Material (TOM) and Item Material Type combinations. Useful for identifying cleanup candidates.

Additional Information

Report Instructions: Use the Branch parameter to narrow the results.
Report does not include: Record Status: Deleted; Item Status: Withdrawn, On Order, In Processing; Collection: Interlibrary Loan, On-The-Fly
Additional Notes: When the report is exported to Excel, the first column in each row can be used as an Item SQL Search to identify titles in the system.

Run Report Preview

Cataloging Exceptions

Find item records with Collection/Stat Code/Call Number Prefix combinations that don't match the mapping list.

Additional Information

Report Instructions: Use the Branch parameter to narrow results.
Report does not include: Record Status: Deleted; Item Status: Lost, Missing, Claims Returned/Never Had, Withdrawn, On Order, In Processing; Items where First Available Date is NULL
Additional Notes: Corrected item combinations will drop off the report.; To add a valid combo, add one item record to record set 116637.

Run Report Preview

Items in Processing

Find "In Processing" items that can fulfill patron holds.

Additional Information

Report Instructions: Use the Branch parameter to narrow the report.; Days in Processing: The minimum number of days the item has been "in processing"; Detail Level: Include only in processing items with a hold or all items in processing.
Report does not include: Item barcodes starting with "3"; Record Status: Deleted

Run Report Preview

In Processing with Holds

Use in Item Find Tool

SELECT CIR.ItemRecordID
FROM Polaris.CircItemRecords CIR (nolock)
JOIN Polaris.RWRITER_BibDerivedDataView BDD
ON CIR.AssociatedBibRecordID = BDD.BibliographicRecordID
WHERE CIR.AssignedBranchID IN (999)
AND CIR.ItemStatusID = 15
AND CIR.RecordStatusID = 1
AND (CIR.Barcode IS NULL OR CIR.Barcode NOT LIKE '3%')
AND CIR.ItemStatusDate < GetDate() - 3
AND BDD.NumberActiveHolds > 0

Notes
Replace 999 with your Branch ID
Replace 3 with min. days in processing
Remove final condition to include all items

Loan Period / Fine Code Combinations

Find loan period and fine code pairings for cleanup across the system.

Additional Information

Report Instructions: Use the Branch parameter to narrow the results.
Report does not include: Record Status: Deleted; Item Status: Withdrawn, On Order, In Processing; Collection: eBook, eAudiobook, ILL, On-The-Fly
Additional Notes: When the report is exported to Excel, the first column in each row can be used as an Item SQL Search to identify titles in the system.

Run Report Preview

PinTech Weekly Cleanup

Provides multi-tab Excel output for cleanup tasks like OTFs, missing collections, provisional records, and more.

Additional Information

Report Instructions: Use the Organizations parameter to narrow the report to your library's materials.; In Process Months: Used in the "In Processing over __ Months" report. The minimum number of months an item has been in processing.; On Order Months: Used in the "On Order over __ Months" report. The minimum number of months an item has been on order
Report List: Missing Collection-Stat - Lists items that are missing Collections or Stat Codes; Bibs with No Items - Lists bib records owned at the system leel with no items attached; OTF Items - Lists all items owned by a library with an OTF block; No 300 - Lists recently added records that do not have a correct 300 tag; No OCLC - Lists circulating bib records that are missing OCLC numbers; Bibs with 099 - Lists fully cataloged records that still have an 099 tag; On Order Over __ Months - Lists items that have been "on order" for an extended period of time; Provisional Record - Lists items/bib that have a record status of "provisional"; In Process Over __ Months - Lists items that have been "In processing" for an extended period of time

Run Report

Acquisitions Reports

Fund Hierarchy Report

Lists funds for the current fiscal year with budgeted, encumbered, and available amounts.

Additional Information

Report Instructions: Use the Fiscal Year parameter to narrow results.
Report does not include: Closed Fiscal Years

Run Report Preview

Funds with Encumbrances

Lists purchase orders still encumbered and grouped by fund. Helps prep for fiscal rollover.

Additional Information

Report Instructions: Use Fiscal Year and select relevant Polaris Funds.; Funds: Select the Polaris funds to be included in the report.
Report does not include: Closed Fiscal Years

Run Report Preview

Funds with Encumbrances

Use in Purchase Order Find Tool

SELECT DISTINCT PO.PurchaseOrderID
FROM Polaris.PurchaseOrders PO (nolock)
JOIN Polaris.POLines POL (nolock)
  ON PO.PurchaseOrderID = POL.PurchaseOrderID
JOIN Polaris.POLineItemSegments POLIS (nolock)
  ON POLIS.POLineItemID = POL.POLineItemID
JOIN Polaris.POLineItemSegmentAmts POLISA (nolock)
  ON POLISA.POLineItemSegmentID = POLIS.POLineItemSegmentID
JOIN Polaris.Funds F (Nolock)
  ON F.FundID = POLISA.FundID
JOIN Polaris.FiscalYears FY (nolock)
  ON F.FiscalYearID = FY.FiscalYearID
WHERE FY.OrganizationID IN (999)
  and AmountEncumbered <> 0

Notes
Replace 999 with OrganizationID

Open POs

Identifies open purchase orders older than a set date.

Additional Information

Report Instructions: Use the Library parameter to narrow the report to your library.; POs Older Than: A date value to limit the report to older POs. Use today's date to return all Open Purchase Orders

Run Report Preview

Open POs

Use in Purchase Order Find Tool

Select PO.PurchaseOrderID
From Polaris.PurchaseOrders PO (nolock)
JOIN Polaris.Organizations O (nolock)
 ON O.OrganizationID = PO.OrganizationID
WHERE PO.CreationDate < DATEADD(month,-12,GetDate())
 AND PO.OrderStatusID NOT IN (2)
 AND O.OrganizationID IN (999)

Notes
Replace -12 with PO age
Replace 999 with OrganizationID

POs Received, Not Invoiced

Lists Purchase Orders that have been received in Polaris, but not invoiced. Received POs will rollover until they are invoiced and paid.

Additional Information

Report Instructions: Use Branch parameter to narrow results.

Run Report Preview

POs Received, not Invoiced

Use in Purchase Order Find Tool

SELECT DISTINCT PO.PurchaseOrderID
FROM Polaris.PurchaseOrders PO (nolock)
JOIN Polaris.POLines POLI (nolock)
  ON PO.PurchaseOrderID = POLI.PurchaseOrderID
LEFT JOIN Polaris.InvLines IL (nolock)
  ON POLI.POLineItemID = IL.POLineItemID
WHERE POLI.StatusID IN (5,13,18,19)
  AND (IL.PaymentStatusID IS NULL)
  AND PO.OrderStatusID NOT IN (1,2)
  And PO.OrganizationID IN (999)

Notes
Replace 999 with OrganizationID

POs Not Sent via EDI

Lists POs linked to EDI vendors that haven’t yet been transmitted.

Additional Information

Report Instructions: Use the Branch parameter to narrow the report to your library's materials.; Days: The maximum age of the PO (in days). Filters the results to include only recently-created POs.
Report does not include: POs attached to Suppliers that do not have "Accepts EDI Orders" checked in the Supplier Record

Run Report Preview

POs Not Sent via EDI

Use in Purchase Order Find Tool

SELECT PurchaseOrderID
FROM Polaris.PurchaseOrders AS PO (nolock)
JOIN Polaris.Suppliers S (nolock)
  ON S.SupplierID = PO.SupplierID
WHERE PO.OrganizationID IN (999)
  AND PO.CreationDate > getdate()-90
  AND S.ElecCommFlag =1
  AND PO.OrderStatusID NOT IN (2)
  AND PO.EDIFileName IS NULL

Notes
Replace 999 with OrganizationID
Replace 90 with PO age limit (in days)