Staff Web

StaffWeb: UG | UW | WLU
Libraries: UG | UW | WLU
Campus: UG | UW | WLU

Documents

Contacts

TUG Access/User Services Statistics and Reports Review Team

Vera Fesnak
Donna Sartori
Heather Martin
Wish Leonard
Alex McCulloch (chair)
Ellen Tom
Bill Halahan
Gail Sperling

FINAL REPORT

April 7, 2000

to:
Mark Haslett
Ron MacKinnon
Debbie Thayer

Appendix A …Process Followed by the Team
Appendix B ... Report Request Form
Appendix C ... Data Elements Required in the Reports Status Spreadsheets
Appendix D ... PowerPlay Report Documentation
Appendix E ... Impromptu Report Documentation
Appendix F ... PowerPlay Cube Documentation
Appendix G ...Access/User Services Revised Report List

MANDATE AND TERMS OF REFERENCE

Mandate

This review team will act as a test case for developing a framework that will sustain the effective evolution of TRELLIS reports activities responsive to the needs of managers in support of service goals. The team’s principle focus will be operational and managerial activities related to Access/User Services.

Terms of Reference

Identify what TRELLIS statistics and reports are required in the Access/User Services area, in the context of needs related to operational services and managerial activities. An updated version of the “TUG reports” spreadsheet may serve as a useful point of reference.
Identify what is currently being produced, what is not, etc.
Review if and how the following provide effective information management support to address Access/User Services needs
1. Cognos Powerplay cubes;
2. Impromptu reports;
3. Access/Excel/SQL+/Voyager "canned" reports
Identify other sources of data required to supplement TRELLIS data in support of Access/User Services needs.
Extrapolate from the work the Access/User Services team has done for items #1 through #4, and identify issues and provide recommendations for a framework to deal with TRELLIS statistics and reports in other functional areas.

The Team

Managerial stakeholders from each of the TUG libraries were Vera Fesnak, Donna Sartori, Heather Martin, Wish Leonard, and Alex McCulloch (chair). In addition, Ellen Tom in her role as TRELLIS Prototyping Co-ordinator represented and liaised with the TRELLIS Operational Management Group and acted as a link to the TUG Holdings-Counts Review Team. Bill Halahan and Gail Sperling represented Systems.

An interim report was circulated to Mark Haslett, Ron MacKinnon and Debbie Thayer on February 25. Comments received were incorporated into the development of the final report.

EXECUTIVE SUMMARY

The focus of the TUG Access/User Services Statistics and Reports Review team was to develop and implement a structure that would sustain the effective evolution of TRELLIS and other reports activities in the Access/User Services area. The team also formulated recommendations for a framework to deal with statistics and reports in support of service goals in other functional areas.

The review process resulted in a series of recommendations aimed specifically at addressing the need for a formal infrastructure for ongoing reports development and maintenance, and the need for an increased level of support during the extended “ramp-up” phase. These recommendations were grouped under the following headings:

Administration
Support
Framework for Other Functional Areas

In the area of administration of reports, we are recommending the creation of specific tools to facilitate the management of reports development and maintenance. These tools structure the process of requesting, documenting, and distributing reports, and improve communication between report developers and end-users. It is critical for these recommendations to be implemented as quickly as possible, so that an infrastructure is in place to enable other areas to begin the reports review process.

Discussions of support issues led to a conclusion that each software tool requires a specialized and significant investment in resources in order to maintain and improve upon current levels of report development. We discussed in depth how the software might be used to deploy the development and modification of reports to the functional areas, specifically the issue of where expertise for report creation could reside - should it be centralized in Systems departments, or decentralized to functional departments?

There was agreement that PowerPlay is an effective and powerful tool for statistical reporting needs, and that expertise should be developed in the functional areas for this software.

While Impromptu and MS Access are designed to put report development into the hands of the end-user, there are significant obstacles to doing this at this time. The team agreed that the decentralization of this responsibility to the functional units should be reviewed in the future.

There was also agreement that there is an urgent and critical need for the development of TUGdoc statistical management reports.

Appendix A, Process Followed by the Team, outlines the approach taken which was vital to the success of this pilot. The results demonstrated the work that still needs to be done in the area of reports and raised issues in the areas of support and training as well. Most important, the process was successful and should be used as a model for other functional groups. Refer to the Recommendations – Framework for Other Functional Areas section.

RECOMMENDATIONS

Administration

A web based report request form should be created which all TUG staff would use to submit requests for new reports and revisions to existing reports. This form could possibly be used to create records in a reports database. A sample form template is attached as Appendix B.
A web based spreadsheet or database report should be created which would be used to track reports development status. A list of data elements required in the spreadsheet is attached as Appendix C.
There should be web links between the spreadsheet and any related documentation so that one can go from the spreadsheet to the documentation for a particular report/cube or from the documentation to the spreadsheet.
Report developers will need access to support from staff with web expertise for the creation and maintenance of the web forms and pages.
All requests for new reports/cubes or revisions to existing reports/cubes must be authorized by the requestor’s TUG functional group before being submitted to the report developers. We acknowledge that there will be some reports/cubes that are specific to one institution but feel that it is necessary that there be discussion at the TUG level in case the report can be made generic enough to be used by other institutions.
A small cross-functional TUG group should be created to set priorities for the report developers. After the report backlog is cleared this group would only meet as required when the reports developers need direction on prioritizing report requests. Membership should include representatives from the functional areas for circulation, acquisitions, cataloguing, collections, MIS, and reports.
A listserv should be created for use by report developers to communicate to subscribers when a new report/cube/documentation is available or when an existing report/cube/documentation has been revised.
All TUG managers should subscribe to this listserv as well as other staff in each functional area who are responsible for running reports.
TUG Functional/Prototyping groups should include testing of reports when a new release of TRELLIS is installed.
Latest revision date should be included in the spreadsheet and documentation as well as in the footer of each Impromptu report.
Web based documentation for Impromptu reports, PowerPlay reports and PowerPlay cubes should be created by the report/cube developer with the first draft of any report/cube created. The documentation should be updated for each revision made to the report/cube. Templates for documentation are attached as Appendix D, E, and F.

Support

General

It is vital that each institution maintains resident experts in Impromptu and PowerPlay report development, PowerPlay cube development, and TRELLIS canned report troubleshooting.
Each TUG institution should provide 1 FTE for the next year to address Impromptu report and PowerPlay cube creation and revisions as well as non-TRELLIS-related reports. This may require that additional resource staff be identified at each institution and given comprehensive training, however it would provide much needed backup and cross training. After one year the resource allocation to reports development should be reviewed.
As PowerPlay and Impromptu become more widely used across TUG some hardware upgrades may be required to staff workstations.
It is recognized that running Impromptu reports and building PowerPlay cubes can affect TRELLIS response time. TUG should investigate alternatives to using the live TRELLIS database for the reporting environment.
Support must be provided for non-TRELLIS reporting needs. Currently the reports group has only been addressing reports associated with TRELLIS data.

Impromptu

Until the initial backlog of new report request and revision requests have been addressed Impromptu report development should continue to be centralized in the Systems departments. This is necessary due to significant short-term difficulties in achieving a sufficient level of expertise with this technology within functional units at this point in time; it is most cost effective to retain Impromptu reports development within systems departments while working through the current backlog.
Once the reports backlog has been cleared decentralization of Impromptu report development to the functional areas should be reviewed.
The Cognos Impromptu Web Report product should be investigated. Advantages of this product may include;
- A uniform interface for running reports
- Facilitation of report and catalogue distribution
- Elimination of the need to upgrade PC software
- Reduced hard drive requirements on user’s PC’s

PowerPlay

PowerPlay report creation and cube content expertise should be developed in each functional area at each TUG institution.
Training in the use of PowerPlay and the content of the cubes must be made available to staff and staff time must be freed up for training.
All PowerPlay cubes should be built on a weekly schedule so managers can have current statistics as required throughout the year.
Additional PowerPlay Cubes should be developed for patron related statistics and fine fee statistics
The Cognos PowerPlay Web product should be investigated as an alternative to the PC client.
Cubes should be designed to meet the specific needs of functional areas. Exceptions can be made if incorporating the needs of other areas does not compromise the overall usability of the cube.
PowerPlay Models should be designed to incorporate as many cubes as possible to reduce the amount of maintenance and cube build times.

TUGdoc

It is critical that resources be committed to work on development of TUGdoc reports. Both patron and collection usage data is urgently required.
- Currently staff are manually recording statistics regarding the number of requests received, filled etc. This method is very time consuming and labour intensive and there is a great deal of room for error. Statistics are required so that we may reconcile the load within TUG.
- Collections staff are in critical need of TUGdocs collection usage statistics so that they may make appropriate decisions regarding journal subscriptions and renewals.

Voyager Reporter

Until the initial backlog of new report request and revision requests have been addressed Voyager Reporter development should continue to be centralized in the Systems departments.
Once the reports backlog has been cleared decentralization of Voyager reporter development to the functional areas should be reviewed.
Release 2000 Voyager canned reports should be reviewed to see if they preclude the need for creation of some outstanding report requests.

Framework for Other Functional Areas

Each functional area should perform a TUG review of reporting requirements including:
- identification of modifications required to existing reports
- identification of outstanding report requirements, both TRELLIS and non-TRELLIS related
Report requests should be submitted using the recommended report request form.
PowerPlay cube designs should be analyzed and finalized by cube stakeholders.
PowerPlay functional training and cube content training should be conducted (as already mentioned above under Support-PowerPlay).

NEXT STEPS

Steps that are taken after the submission of this report will mostly depend on library executive members and the TRELLIS Operational Management Group. However, since the team has generated some momentum, we plan to continue building on what has been done to date as outlined below, while this report is being reviewed:

Access/User Services managers will continue to develop their list of desired modifications to existing Impromptu and Voyager Reporter reports, and add these details to the spreadsheet (attached as Appendix G).
Access/User Services managers will modify the spreadsheet while it is being added to, so that it conforms to the recommendations for the TUG spreadsheet as detailed in Appendix C. This spreadsheet could form the basis of the TUG spreadsheet.
Bill and Gail will implement the proposed modifications to the existing circulation Powercubes, (and, of course, continue work on other reports as they have time).
Report developers (Gail and Bill) will begin to adopt the proposed documentation formats as they update and create new reports.

We believe that a necessary first step is the implementation of the Administration recommendations.

While this document is being reviewed, managers in other areas could begin the process of reviewing and documenting report requirements. However, it must also be acknowledged that without the commitment of systems staff resources, as noted in the section on Recommendations--Support--General, the clearing of the existing reports back-log and new report development will be compromised.

APPENDICES

Appendix A …Process Followed by the Team

Early in the Access/User Services reports review process, the review team agreed upon a core list of desired outcomes and identified key tasks which needed to be completed in order to achieve these goals within the relatively short time frame available. As the review process continued, the plan mapped out in the group’s initial meetings was tracked, critically analyzed, and modified as required, in order to develop an effective model for other TUG functional areas to utilize when engaging in similar reviews.

In addition to a list of specific tasks, the review team identified the following issues that needed to be examined as part of the review:

aspects of reports development that have not worked well to date, and how they could be improved or changed
the process to be followed for requesting new reports/cubes, or for changing existing ones
requirements of a reports tracking system to monitor the status of reports
the need for appropriate, effective documentation for each report developed
staff training issues
analysis, maintenance and regeneration of PowerPlay cubes
notification and implementation of new or revised reports/cubes
flexibility of process to accommodate differences in organizational structure at each TUG site

The first major task to be completed was a review of the entire TUG circulation reports list by the Access/User Services members of the group in order to provide an up to date assessment of the current reports situation and to identify what new reports might be required. The following questions were posed as part of this assessment:

what are TUG Access/User Services current reporting requirements, and how do these fit with those in other areas of the TUG libraries?
what functional reports are already in place, which ones require modification, which ones are yet to be started?
what are reporting requirements for OCUL, ARL and other external agencies?
are we utilizing the appropriate tools/software for the reports we are producing?
how might PowerPlay meet some of our identified reporting needs?
what other data is required to support TUG Access/User Services operational and management information needs, which has not yet been identified?

Completion of the TUG Circulation Reports list enabled the group to recommend improvements to the existing reports development process in order to make it more efficient, streamlined, and meaningful for both Access/User Services and Systems staff. The team then moved on to identify areas requiring additional attention and effort. This included an in-depth analysis of existing PowerPlay cubes and how they might be better organized to meet Access/User Services reporting needs, as well as an assessment of staff resource and training needs required to develop Access/User Services staff proficiency with this tool. The review group then considered Access/User Services’ future short-term and long-term reporting needs and discussed how we might address these. This involved assessment of the feasibility of several models for future reports maintenance and development, as well as consideration of how quickly we might reasonably move to a new model.

Throughout the review process the team maintained a TUG focus as a result of the recognition that the Access/User Services areas in each library share common goals and reporting needs. The process highlighted the benefits of continuing a consultative, shared approach to requesting, reviewing and refining reports, as well as the need for continued consultation with other TUG functional areas as required, in order to avoid duplication of effort.

Appendix B…Report Request Form

Report Title:	Request type:	new/change
Report Reference # (if known)	Request date:
Report Type: functional/statistical/cube	Date Required:
Requester:
Report Description:
Data Elements (in order they should appear on the report):
Filters and prompts:
Sort Order:
Important Notes:

Appendix C…Data elements required in the reports status spreadsheet.

Latest revision date of the spreadsheet
Report number (e.g. CIR0011-76)
- A new numbering system should be created as follows and applied retroactively to the existing report requests. The numbering system used for reports should include;
  - An alpha code to identify the functional area requesting the report. Valid codes would include CIR, CIRRES, CAT, COL, ACQ, ACQSER, MIS, OPAC
  - A four digit number representing the year and month the request was submitted
  - A consecutive number within each alpha code
Report type (functional or statistical)
Status (Complete, Complete – Under review, Incomplete, Incomplete – not yet started)
Use colour coding in spreadsheet to highlight status
Report name – this should be the title or header for Impromptu reports
Description – includes a brief description of the purpose of the report, the data elements required and sorting and filtering requirements.
Software (Impromptu, PowerPlay, Reporter …)
Developer – the name of the person assigned to develop the report
Requested enhancement – used to describe changes requested to a report which has a status of “Complete – under review”
File/cube name – the full path of the Impromptu/PowerPlay report file name or the cube name – this should link to the report documentation
Contact – the name of the person who can be contacted regarding questions about the report – used by the report developer during the development phase or by other staff interested in the report once it has been developed

Appendix D…PowerPlay Report Documentation

Report Title: Revision date:

Report Reference #: Requestor:

PowerPlay File Name	Directory	Cube Name
	C:\My Documents\Cognos\Powerplay	.mdc

Report Description:

Measure	Row(s)	Column(s)	Layer(s)

Filters:

Sort or Ranking:

Important Notes:

Report Author:

Appendix E…Impromptu Report Documentation

Report Title: Revision date:

Report Reference # Requestor:

Impromptu File Name	Directory	Catalogue
	C:\My Documents\Cognos\Powerplay	C:\My Documents\Cognos\Catalogues

Report Description:

Prompt	Type	Description

· Data Elements:

· Filters:

· Sort Order:

· Important Notes:

· Estimated run time:

· Report Developer:

Appendix F…PowerPlay Cube Documentation

Cube File Name: Revision Date:

Cube Developer:

Cube Description:

Measure Name	Description

Dimension Name	Dimension Hierarchy	Description and Notes	Valid Measures	Alternate Hierarchy
All Locations (item)	Institution	The location at the item record level
	Library
	Item Location Name
Date	Year	Date the transaction occurred.		Current Month Last Month QTD (Quarter to date) Prior QTD QTD Grouped YTD (Year to date) Prior YTD YTD Grouped
	Term
	Month

PowerPlay Model File Name:

Appendix G …Access/User Services Revised Reports List

Explanation of Statuses: X = No longer required, R = Required, S = Satisfied, N = Done, but needs modification (Priority 1 or 2), A = Additional one needed.

Number	Title	Software	Status	Purpose	Enhancements
Notices 02 (U-01)	Overdue Notices	Reporter	R, N	Informs patrons of overdue items of the 4th and 11th day overdue. For UW sites these are run centrally in Porter User Services.	Add data elements, e.g.. ID number
Notices 03 (U-02)	Recall Notices	Reporter	R, N	Informs patrons of recalled items. For UW sites, these are run centrally in Porter User Services.
M-11 (U-03)	Book Purchase Request (book replacement form)	Impromptu	R, N	An UW report, each notice is sent to the appropriate selector for a decision on whether to replace or withdraw items that are lost from the collection. Used in conjunction with U-08. A breakdown of circulation and reshelve counts by year may be possible by creating another report using Impromptu to query an Access database. N.B. Exact content of this report to be reviewed by TUG circ towards making it applicable TUG-wide.	Modify to sort into selector call number ranges.
C-08 (U-04)	Holds Listing (HoldsListDate)	Impromptu	R, N	Used daily for identifying TUGholds. The older version of this report, HoldsList, is also available. It is not regularly used because it does not have a date filter.	Add patron group.
C-09 (U-04A)	Recalls Listing (RecallList)	Impromptu	R, N	Created to track the success of recalls when TRELLIS first went live. Not currently used.	Formatting required. Add Patron Group.
U-05	Circulation Usage Report (circ usage notice)	Impromptu	R, N	An UW report for making decisions on whether to bind, replace, or discard damaged material. N.B. Exact content of this report to be reviewed by TUG Circ toward making it applicable TUG-wide.	Needs new title - Binding Decision Notice? Copies to Donna, Vera.
U-06	Circ Accounting Report (circ account ledger)	Impromptu	R, N	Used daily for cash balancing.	Title change. Date display goes into next column.
C-10 (U-07)	Administrative Holds Outstanding (AdminHolds)	Impromptu	R, N	Used daily for UW for following up on Library Administrative Requests.	Add patron group.
C-10A (U-07A)	Administrative Recalls Outstanding (AdminRecalls)	Impromptu	R, N	Created to track the success of administrative recalls when TRELLIS first went live. Not currently used. Used at UG for reserves.
C-03 (U-08)	Master List of Lost Items (Lost Items)	Impromptu	R, S	Used in conjunction with U-03. User Services does a final search on items that have been declared as lost. The report is then passed to cataloguing staff to suppress items that were not found in the final search, pending the final decision made by the selector on report U-03. The report includes both Library Applied and System Applied lost types.
M-23	Master List of Missing Items (Missing Items)	Impromptu	R, S	Created at the same time as Lost Items. Not regularly required by UW.	Should be renumbered as a "C" i.e. C-03A
U-09	Discharged Lost List (DisLostF)	Impromptu	R, N	Used by staff to cancel replacement and fines, and assess overdue charges, for those items that have come back to the collection after having been marked Lost-System Applied. An older version of the report, DisLost, is available but no longer regularly used as it lacks a date filter.	Want new date showing, not just original due date. Break out Lost types into the two types.
M-09 (U-11)	Excessive Holds Listing (Excessive Holds Recalls)	Impromptu	R, N	Used to capture items that have three or more holds, for placing them in Controlled Access on reduced loan.	Renumber to "C". Cut out title-level holds. Cut out extra barcodes.
M-12 (U-11A)	Items Located at a Temporary Location (List items by temporary location)	Impromptu	R, S?	Used to capture items which have been temporarily assigned to locations, in order to return them to the permanent location. For example, it's used to return items to the stacks that were placed in Controlled Access because of excessive holds.	Renumber to "C". Paper sample to Donna, Vera.
Reports 02 (U-12)	Hold Shelf Expired	Reporter	R, N	Created as part of the same Circ job that updates the status of items on holds. Not currently used at UW.	Want option to sort by patron name, or date, or call number.
U-13	Update Shelving	Reporter	R, S	A job that is run each day to update the circulation status of items in the system. No report is created.
None	Circ Utilities Job	Reporter	R, S	A job that is run every weeknight to clear up phantom holds.
Notices 00 (U-15)	Hold/Recall Cancelled Notices	Reporter	R, N	A daily job informing patrons of expired or cancelled holds. The print version of this job has been suppressed - only email notices are being sent.	Need a way to block this notice on an individual basis.
Reports 00 (U-16)	Reserves Items Active List	Reporter	R - at WLU N? Vera checking.	Useful to UW occasionally as a back-up in cases of expected downtime. UG does not require a back-up. An enhancement request is planned for a redesign of this canned report.
U-17	Items Discharged as a Result of a Self-Charge. (selfcharge discharge)	Impromptu	R, N	Assists in those cases where a patron has returned a book, and the book is removed from their file when another patron charges it on self-charge. Fines can then be cancelled before the first patron is aware of them. N.B. To be reviewed by TUG Circ for possibility of wider application, which would necessitate modification.	Include all situations, not just self-charge. Alex, Vera - checking if this shows on exemptions report.
Reports 01 C-13 (U-18)	Reserve Items Expired List	Reporter	R, N	Not presently used at UW. Used at UG. Designed to inform staff of when to pull items from reserves.	Ask H. Martin.
C-15 (U-29)	Fines Report (fines reconciliation)	Impromptu	R, S?	Enables us to determine how much money each TUG library owes the other ones for fines and replacement charges paid at one library for books owned by the other.	Need to include Renison, Brantford.
U-37	(Long Overdue) Pre-Lost List (circ long overdue)	Impromptu	R, S	Captures material that is more than 53 days overdue, in order that staff can do a final search before a Replacement notice is sent to a patron.
C-16 (U-38)	Aged Account Reports (marks withhold)	Impromptu	R, N	This is actually a run of notices that captures students who have books in the replacement stage, and/or who owe the library $10 or more. Used for withholding marks.	Donna, Vera reviewing to adapt.
None	(marks withhold-no items)	Impromptu	R, N?	Same as U-38, except this report captures fines that are not associated with any library items, i.e. "fees". Both reports need to be run in conjunction.
None	(marks withhold - mailing labels)	Impromptu	R, S	Produces a sheet of labels of temporary addresses, according to the same criteria used for running the marks withhold reports. Used because the marks withhold report cannot determine the mailing address, and it therefore defaults to the permanent address, whereas in many cases mail should go to the temporary, or local, address.
C-04, C-06 (U-40)	Reserve Instructors Statistical Reports (ReserveUsage)	Impromptu	R, S?	Report to send to faculty at the end of term to inform them of usage of their reserves material. Runs against all active reserve lists in the specified location for those items marked "On Reserve".
None	Reserve Instructors Statistical Reports By Course (ReserveUsagebyCourse)	Impromptu	R, S?	Same as above, except can be run for specific courses.
E-15 (U-43)	Registrar Interface Report	Impromptu	R, N	Used by UG in conjunction with U-38, to transfer information to the registrar so those patrons are prevented from re-registering.
Reports 07 (U-44)	Transaction Exception Reports	Reporter	R, N	Used by UW primarily to catch renewals that were done in error by overriding outstanding holds. All TUG using to monitor action.	Add patron and item. See self-charge discharge - if this info is there, then S.
C-17 (U-45)	Delinquent High School Borrowers Report (overdues (by patron statistical))	Impromptu	R, N	A UW report to be run in springtime, in order to allow enough time to work with high schools to recover long overdue books.	Include a statement for mailing. For UG, include fines, overdues.
C-14 (U-46)	Patron Fines Purge Report	Impromptu	R, A	To capture patron files, using established rules for minimum fines and minimum time expired, in order that they can be purged from the system.
Notices 05 (U-47)	Fine/Fee Notice	Reporter	R, N	A report run once per term on all patrons with fines of $5 or more, to advise them of accounts owing.	Add ID number. Paper and email version - combine totals.
Notices 07 (U-48)	Courtesy Notices	Reporter	R, N	Run once per term to advise faculty/grads of items that are signed out for extended periods.	Add ID number. Combine all print locations so notices are produced by patron group.
U-49	On Order Holds Report (OnOrderHolds)	Impromptu	R, N?	Run in the cataloguing department, along with the OnOrderHoldsNoLineItems, to capture holds placed on material that has been received by not yet catalogued. This material is processed as a Rush request.	Patron group. Capture only "received".
Q-04 (U-50)	Item Status Capture Report	Impromptu	R, A	A report to speed up UW Cataloguing requests for material from Davis and UMD.	Can LAR be used instead?
C-05 (U-51)	Reserve Items Overdue (ReserveODue)	Impromptu	R, S?	To be run each morning before morning notices are sent, to catch reserve items that were not properly discharged. This will allow time to prevent mailing of those overdue notices that should not be mailed.	HM checking.
C-01 (U-53)	Recall Fine Correction Report (Recall Fines Correction)	Impromptu	R, N	To be run to catch recall fines that were unfairly assessed before patrons are aware of them. TRELLIS assigns overdue recall fines retroactively when it shouldn't.	To cut out stuff not overdue.
C-12 (U-54)	Circulation History from the Patron Record		R, N	Designed to pull together full circulation history on a patron, in order to assist with problem solving in those cases where the patron is disputing a fine on other material charged at the same time as material with no fines.	Bill has roughed up a version.
C-19 (U-55)	Patron Email Addresses	Impromptu	R, S	Run daily to search for incomplete email addresses added to TRELLIS.
C-07 (U-58)	Off Reserve Items Retrieval List	Impromptu	R, S	Used for sorting and retrieving those items on currently effective Reserve lists (I.e. not past and future lists). This report is currently used only at Guelph, but has potential workflow applications at UW and Laurier also. Some modifications will be required in order of the report to be used at the other two sites. A slightly different version of this report is being run at UG, so the 3Day Reserves are excluded.
C-N1	(list patrons by stat code & expiry)	Impromptu	R, S?	Used occasionally when a report is required on a particular patron statistical category. The list patrons by stat code is an earlier version of this report.
	(patrons with outstanding fines)	Impromptu	X	Created as a first attempt at a Marks Withhold report. Not regularly used.
	(ptrnupdt barcode problems)	Impromptu	R, S	An UW report that was created to capture errors resulting from the Patron Update program. This report, along with a statistical report, has been created in another format, and "crooned", and they are being sent automatically to Laurie Strome after each update. The above report is no longer being run in Impromptu.
	Replacement Notices	Impromptu	R, A	To be run daily on items that have been overdue for 60 days. Should include the replacement cost of the item, as well as the processing amount.
	Charge Info for Specific Item	Impromptu	R, S	Run as needed against items that are presently charged, to determine the Circulation Group and Operator ID where the charge was executed.
	List Items by Location/Item Type	Impromptu	R, S?	Run on demand to get a call number list of items of a specific item type, with a specific permanent location.
M-08 (U-10)	Overdue Statistics	PowerPlay			Doesn't include patron statisticals. Does it include renewals that are overdue?
M-13 (U-19)	Internal TUG Circulation Report	PowerPlay
M-14 (U-20)	Renewal Counts - 1	PowerPlay
M-15 (U-21)	Renewal Items 2	PowerPlay
M-16 (U-22)	Number of Items Discharged by Location	PowerPlay
M-04, M-10 (U-23)	Number of Holds Report	PowerPlay
M-05 (U-24)	Holds Satisfied Report	PowerPlay
M-17 (U-25)	Recalls Placed Report	PowerPlay
M-18 (U-27)	Term Loan Report	PowerPlay
M-19 (U-28)	New Term Loans Report	PowerPlay
M-20 (U-30)	Reserves Circulation Statistics	PowerPlay
M-21 (U-31)	Notices Count	PowerPlay
M-22 (U-32)	Reserves Processing Statistics	PowerPlay
U-33 (See also C-11)	Counts of Items Removed from Reserves	PowerPlay
M-01 (U-34)	Annex Statistics - 1	PowerPlay		To determine how much material each of the TUG libraries is sending to the Annex.
U-35	Annex Recalls Report	PowerPlay
M-03 (U-36)	Items Returned to Stacks from the Annex	PowerPlay
C-18 (U-41)	Reserves - Daily-Monthly Use Statistics	PowerPlay
U-42	Reserve Statistical Summary	PowerPlay
M-24 (U-52)	New Book Report	PowerPlay
M-06 (U-56)	Holds and recalls not satisfied	PowerPlay
M-07 (U-57)	Hold and recalls satisfied but never picked	PowerPlay
	up
C-02	Lists for Inventory				In Draft
Notices 01	Item Available	Reporter
Notices 04	Recall Overdue	Reporter
Notices 06	Statement fine/fee	Reporter
Reports 03	Missing in Transit	Reporter
Reports 04	Circ transaction stats	Reporter
Reports 05	Circ item related exceptions	Reporter
Reports 06	Circ patron related exceptions	Reporter
Reports 08	Global circ transaction stats	Reporter
	Renewal Info for Specific Item			Run as needed against items that are presently renewed, to determine the Circulation Group and Operator ID where the renewal was executed.	Needs to be done.
		Cube-patron			Create a cube for analyzing patron registration information.
		Cube-			Create cubes as needed for analysing patron statistical information.
		Cube-trans			Add a dimension for loan period in transactions cube.
		Cube-trans			Check the overdue count logic in transactions cube.
		Cube-trans			Add a measure which totals charge and renew transaction counts.
		Cube-trans			Create dimension hierarchy for Patron Groups and eliminate the special categories.
		Cube-trans			Add a dimension for # time renewed with categories 0, 1-5, 6-10, 11-20, 21-40, 41-80, >80.
		Cube-trans			Verify how transit discharges are counted in transactions cube.
		Cube-hold/recall			Make changes to the hierarchy for hold create location dimension in the hold/recall cube.
		Cube- hold/recall			Does "Undefined" = OPAC
		Cube- hold/recall			Test the OPAC holds are counted as Annex holds.
		Cube- hold/recall			Test whether cancelled holds are counted as expired holds.
		Cube-			Note in documentation on description of Title-level holds that they are only counted once for each title.
		Cube- hold/recall			Move Annex from Waterloo group.
		Cube- hold/recall			Add Renison to the Waterloo group, and break out UMD from "other".
		Cube- hold/recall			Remove operator profile from hold/recall cube.
		Cube-trans			Only have self-charge operator ids, OPAC id and all other operator id's would become "Staff" or "Other" in transactions cube.
		Cube-trans			Date dimensions to go to week level.
		Cube-trans			Possibly consolidate all date and happening location dimensions into one dimension for dates and one for happening location.
		Cube-trans			Remove item count and related measure/dimensions from transactions cube.
		Cube-trans			Default is appropriately defined as the Date charge and the location of the charge. Charge count is the default measure.
		Cube- hold/recall			Location is default row. Type is column.
		Cube-			Operator names - do not need to be a part of any cube for the TUG Circ group. In the long term, remove operator as a factor in calculating statistics in all dimensions/measures.
		Cube- hold/recall			OPAC placed holds - The total numbers of holds for Undefined locations is unclear; until we sort out the numbers we will keep cube as is. Bill/Gail will look into where numbers come from in stats related to Undefined location etc.
		Cube- hold/recall			Hold Create date - Calculate in "terms" rather than in "quarters".
		Cube- patron			Number of patrons in a particular category will be "measured".
		Cube-			Folder names - any suggestions for changes to folder names should be directed to Gail/Bill eg.Charged items (Charges). Dimensions (Gail uses full location names); Bill codes, decision was reached to use full location names.
		Cube-trans			Charge count - change to = charges
		Cube-trans			Renewal count - change to = renewals
		Cube-trans			Charge renewal count - How many times a charge was renewed. Not needed.
		Cube-trans			Renew item count - % of charges renewed. Stays, change to Renewed items.
		Cube-hold/recall			Recall notice count - place in Holds/Recall cube; overdue recall count needs to be added to the Holds/Recall cube if it is not there.
		Cube-hold/recall			Measure for Holds/Recalls - copy/title level holds - Bill will email the definitions.
		Cube-hold/recall			Unsatisfied holds to be renamed Cancelled.
		Cube-			Colleges at UW will be separated and not under UW.
		Cube-			Creation of cube for tugdocs and other sources of data need to be explored. Would TUGdoc lend itself to manipulation?
		Cube-hold/recall			Create an Impromptu Report to analyse Hold-Create, Hold Pick-up Date and Hold-Satisfy Date. If possible, Bill will confirm.
		Cube-trans			Remove- Dates (Due)
		Cube-trans			Remove- All Locations (Temporary)
		Cube-trans			Remove- Operators
		Cube-trans			Remove- Dates (Item Create)
		Cube-trans			Change "weekday" to "day of the week".
		Cube-trans			Add- Item Types (Temporary)

TUG Access/User Services Statistics and Reports Review Team

FINAL REPORT

April 7, 2000

CONTENTS:

MANDATE AND TERMS OF REFERENCE

Mandate

Terms of Reference

The Team

EXECUTIVE SUMMARY

RECOMMENDATIONS

Administration

Support

General

Impromptu

PowerPlay

TUGdoc

Voyager Reporter

Framework for Other Functional Areas

NEXT STEPS

APPENDICES

Appendix A …Process Followed by the Team

Appendix B…Report Request Form

Report Title:

Request type:

Report Reference #

Request date:

Report Type:

Date Required:

Requester:

Report Description:

Data Elements

Filters and prompts:

Sort Order:

Important Notes:

Appendix C…Data elements required in the reports status spreadsheet.

Appendix D…PowerPlay Report Documentation

Report Title: Revision date:

Report Reference #: Requestor:

Report Description:

Filters:

Sort or Ranking:

Appendix E…Impromptu Report Documentation

Report Title: Revision date:

Report Reference # Requestor:

Report Description:

· Data Elements:

· Filters:

· Sort Order:

· Important Notes:

· Estimated run time:

· Report Developer:

Appendix F…PowerPlay Cube Documentation

Appendix G …Access/User Services Revised Reports List