Scope: This procedure explains the process LTS managers and the LTS Administrative Support Specialist must follow to gain access to the database, and gives guidelines for its use. Step-by-step explanations for the use of some specific reports and full documentation of current practices for gathering batch processing statistics are included.
Contact: Jenn Colt
Date last updated: 03/31/2020
Date of next review: December 2021
Connecting to the TS Stats Database
Brief Description of Each Report
Using the "Unit" Reports in the TS Stats Database
Using the Batch Processing Stats Database
How to Compile Batch Processing Statistics for the Fiscal Year End
Using the Principal Cataloger's and Director of Cataloging and Metadata Special Reports
Compiling Database Quality Unit Statistics
A. Introduction
Library Technical Services tracks and compiles production statistics at the department, unit, and individual levels. This information is important for individual employee performance evaluation and management, and for short and long term planning at both the unit and department levels. Information from 948 fields is harvested and processed to determine what type of work was done to a record, on what day, and by whom. An Access database was built to query this data and create reports for individuals, units, and the entire department.
Managers may retrieve statistics for each member of their staff, and may also retrieve statistics for their units, for any desired date range. They may run these queries at any time they desire. Monthly retrieval of statistics for the entire department is executed by the LTS Administrative Support Specialist, who compiles this information into a spreadsheet for each fiscal year. This spreadsheet is distributed throughout LTS management and shared with Library Administration.
Additional specialized reports have been written in a separate database to gather information regarding batch processing production in LTS. These statistics are compiled by the Batch Processing Supervisor for each fiscal year.
B. Connecting to the Technical Services Statistics Database
Note: If you need help with the following set-up, please send a message to CUL Desktop Support at cul-dshelp-l@cornell.edu.
- Download and install the MySQL ODBC 5.1 driver. The installer is available on the LTS share, in "netadmins\Software\MySQL." Also, either create a desktop shortcut to the database, or map drive to it. The database is found at \\files.cornell.edu\lib\ds-deptshares\LTS\tsstats\1New_culv2-2012-2013.accdb.
- Ask Amy Blumenthal for the database password.
- Configure the driver, using the 32-bit ODBC Administrator, here: c:\windows\syswow64\odbcad32.exe. (Note: Do not use the ODBC Administrator in Control Panel/Administrative Tools.)
C. Basic Guide to the Database
- Go to \\files.cornell.edu\LIB\ds-deptshares\tsstats (Probably mapped on your computer as L:\tsstats)
Open the file named 1New_culv2-2012-2013.accdb
- You should see the following sidebar on the left side of the Access window:
Make sure that the "Macros" menu is showing. If it does not say "Macros," click on the little circled arrow and select "Macros."
4. Choose the macro for the report you wish to run; click on the macro to start the query. DO NOT try to run the report from the "Reports" drop-down. You will get an error message. The macro walks you through all the variables you need to enter in, i.e. the net ID of the staff member and the date range.
5. Individual Reports and Staff/Bib ID Reports will ask for the net ID of the staff member whose activity you wish to view. (See below.) Only one net ID can be entered for these reports at a time.
6. All macros will ask you for a date range of activity to be reported out. (See below) You may query as far back as July 2012 in this system, if you wish.
7. The first time you run a report in each session of Access, you will be asked for a database user and password. Both are "dbread," just as with our other Voyager Access databases.
8. You will get a message box asking you if it is ok to delete the existing table. The answer is "yes," otherwise you will be getting data from the last query run in the system, instead of for the query you just requested.
9. The query will now run and will generate a report for the data you requested. How long it takes will depend upon the length of the date range, and whether the query is harvesting data for a whole unit, the whole department, or just one staff member.
10. When the report is returned, you may print it. You may also save it as a .pdf if you wish to keep a digital copy of it for your records. You should save that .pdf in your own, secure files on your computer, as opposed to the LTS share.
a. To print without saving the document: While the report is open, click the "File" button in the upper left corner, then click "Print" in the drop-down.
b. To print AND save:
i. Click File/Print, then click "Print Preview," as seen below.
ii. Toward the middle of the tool bar, click the "PDF or XOP" button.
iii. The following menu box with appear:
iv. Check to make sure the program is defaulting to the folder in which you'd like to save the report. (See above)
v. Give the file a useful name, such as the net ID of the staff person and the date range of the data you harvested.
vi. Then click the "Publish" button.
vii. The program has saved your report as a .pdf file in your chosen folder and has opened it in Adobe Acrobat for you to review. If you wish to print it at this time, click the "print" button on the toolbar. Now that you have saved the report as a .pdf, you may retrieve and print it at any time.
D. Brief Description of Each Report
- "Enter OPID" is a sub-function of these other macros. You will never need to select this; it's built into any staff member report you run.
- "Enter Start and End Dates" is also a sub-function of these other macros. You will never need to select this; it's built into every report you run.
- Run “Dept Summary Report” if you want a summary of all work done in LTS for the desired date range.
- Run “Individual Report” if you want to extract a summary of all work done by a specific staff member during the desired date range.
- Run “Staff/Bib ID Report” if you want to extract a list of all bib IDs worked on by a specific staff member during the desired date range.
- Run “Law TS Summary Report” if you want a summary of all work done in LAW TS for the desired date range.
- Run “BATCH Summary Report” if you want a summary of all work done via batch processing for the desired date range. Gathers statistics for a chosen date range of all work done by all TS staff who have logged in under a “batch[netid]” sign-on AND any records with a 948 $d of “batch”, broken down by both Format (type of materials) and by Function (type of work done on records).
- Run “NON-BATCH Summary Report” if you want a summary of all work done by non-batch processes for the desired date range. Gathers statistics for a chosen date range of all work done by LTS staff who are NOT logged in under a “batch[netid]” sign-on, broken down by both Format (type of materials) and by Function (type of work done on records).
- Run "Maintenance Summary Report" if you want a summary of all maintenance processes for the desired date range.
- Run "Transfers Summary Report" if you want a summary of all transfers for the desired date range. Gathers statistics for the chosen date range of all transfers, listed by date and bib key, and whether the item had been an added copy or an added location.
- Run "Unit Reports" if you want a summary of all work done by members of a specific unit within LTS for the desired date range.
E. Using the “Unit” Reports in the TS Stats Database
- Go to L:\tsstats and select "1New_culv-2012-2013.accdb." Make sure that the "Macros" menu is showing. If it does not say "Macros," click on the little circled arrow and select "Macros." Click on the "Unit Reports" macro.
- Enter the desired date range in the boxes and click "Ok."
- This dialog box will most likely appear (below). If it does, you must click YES to clear the data from the last time someone ran this report for their unit.
- Next the macro will ask you to enter the name of your unit. (See below.) You may enter it in full, as rendered in the Unit ID/ Unit Name table shown below, or type the first three letters of the name as shown in that same table (case sensitive); the script will then automatically choose the corresponding unit name. After entering the name, click “ok” to run the query and return a report.
NOTE: Please note that staff members have been entered into this database under the unit of their administrative supervisor. For example, while Betty Datacruncher does some copy cataloging, she is a direct report of the Batch Processing Unit Supervisor and is therefore entered under Batch Processing. If you have a staff member who does some work for you but who reports to another manager, you can run your unit report and then run the “Individual Report” for any such staff member(s).
When you have new staff, you must notify the TS Stats Database Admin (currently Jenn Colt) in order to have them added to the database. In order for a staff member (including student workers) to be included in your unit report, their name and net id must be in this database, and they must be assigned the proper unit name.
5. The query will now run. It may take a minute, since it’s parsing so much data. When it completes, it will return two reports: The “Unit Summary Report” and the “Unit Detail Report.” The Unit Summary Report returns a communal tally of all the work done by the unit, while the Unit Detail Report breaks out the same information by staff member. In the Unit Detail Report, each staff member’s statistics for the date range are arranged on a separate page, with the very last page showing the total number of records processed by the unit as a whole.
F. Using the Batch Stats Database
(Note: This process is carried out by the TS Stats Database Admin on behalf of the Batch Processing Supervisor.) The purpose of this Batch Stats Access database is to account for work done by LTS staff using Gary Strawn’s Location Changer and Record Reloader programs. Records altered using these programs are not given 948 field by which we can tally them in the TS Stats database. Appropriate staff members have been given individual “batch” log-ins to use when running Strawn software; these ID’s show up in the “Operator_ID” field in the Voyager tables. This database queries and reports this work, using these ID’s and other parts of the records. Please note that there is a query and report in the TS Stats database for those batch jobs which are processed using other tools and therefore contain 948 fields by which to count them. Together, the information from the two reports account for work done in batch form by LTS staff.
- Go to \\files.cornell.edu\LIB\ds-deptshares\tsstats and open the .mdb file called “3_BATCHstats.mdb.”
- At the right side of the screen, double-click on the macro called “1 – Batch BIB Summary Report.” (see below)
The following window will appear (below). Enter the starting and ending dates for the time period for which you wish to collect data.
If you haven’t already been in an Access database for Voyager during your computing session, Access will ask you for the VGR7 username and password (dbread, dbread). (see below)
The script will start to run a series of queries. When it is done, it will open the report containing the data for the date range you entered. (See next page for screenshot.) NOTE: If you want to save a copy of your report, you’ll need to right click on it and Export it as a PDF. If you or someone else runs the query before you’ve done that, your report will be overwritten and you will have to run it again (and export it immediately) in order to save the information it generated. Here's a sample report (below). See also the annotated version, after this one.
G. How to Compile Batch Processing Statistics for the Fiscal Year End
(Note: This process is carried out by the TS Stats Database Admin on behalf of the Batch Processing Supervisor. It is done by running multiple queries from multiple databases, and utilizes multiple spreadsheets. Please click on the section G title, above, to go to the documentation for this process.)
H. Using the Principal Cataloger and Director of Cataloging and Metadata Special Reports
- Go to L:\tsstats and select "1New_culv-2012-2013.accdb." Make sure that the "Macros" menu is showing. If it does not say "Macros," click on the little circled arrow and select "Macros." Click on either the "Sarah's Cataloging Reports" or the "Naun's Cataloging Reports" macro.
- Both of these macros will ask you for a date range of activity to be reported out. (See below) You may query as far back as July 2012 in this system, if you wish.
3. The first time you run a report in each session of Access, you will be asked for a database user and password. Both are "dbread," just as with our other Voyager Access databases
4. You will get a message box asking you if it is ok to delete the existing table. The answer is "yes," otherwise you will be getting data from the last query run in the system, instead of for the query you just requested.
5. The query will now run and will generate a report for the data you requested. How long it takes will depend upon the length of the date range you have chosen.
6. When the report is returned, you may print it. You may also save it as a .pdf if you wish to keep a digital copy of it for your records. You should save that .pdf in your own, secure files on your computer, as opposed to the LTS share.
a. To print without saving the document: While the report is open, click the "Print" button in the upper left corner, then click "Properties" button in the upper left of the resulting window. Most of us in LTS have our printing preferences set to print on both sides of a piece of paper; since you will want each cataloger's report on a separate page, under Properties/Paper/Output, you should deselect "Print on both sides."
b. To print AND save:
i. Click File/Print, then click "Print Preview," as seen below.
ii. Toward the middle of the tool bar, click the "PDF or XOP" button.
iii. The following menu box with appear:
iv. Check to make sure the program is defaulting to the folder in which you'd like to save the report. (See above)
v. Give the file a useful name, such as the net ID of the staff person and the date range of the data you harvested.
vi. Then click the "Publish" button.
vii. The program has saved your report as a .pdf file in your chosen folder and has opened it in Adobe Acrobat for you to review. (If you wish to print it at this time, click the "Print" button on the toolbar and remember to go to "Print Properties and deselect "print on both sides".) Now that you have saved the report as a .pdf, you may retrieve and print it at any time.
I. Compiling Database Quality Unit Statistics
The fiscal year end spreadsheet, L://tsstats/LTS_Stats_2005-[current year].xlsx, requires information for the Database Quality Unit that require the use of more than one report in the Technical Services Statistics Database. (See screenshot, below.)
The Record maintenance and Withdrawals statistics can be gathered simply by running the Unit Summary Report for Database Quality, and retrieving the two figures highlighted in the screenshot below:
To gather the information for "Transfers," you will need to run the "Transfers Summary Report" for the FY in question. This query will return an .xlsx spreadsheet with a list of BIB ids that were transferred. Simply enter the total number of records in the spreadsheet to get the number of transfers executed by Database Quality staff members.