How to Create Reports in CM

Overview

This how-to guide contains instructions on exporting data from CM to help answer various questions.

Listed in the table below are the relevant report types.

CM Report Types	Possible Uses	Limitations
Prices	List past and current subscriptions, and future renewals List cancellations	No Deposit Account metadata Doesn’t show subscription notes
Subscriptions	List past and current subscriptions Use to calculate price increases	Doesn’t show upcoming renewals Doesn’t show cancellations

Please contact OCLS if you would like data for a pro forma Deposit Account Balance Statement.

Expand for a glossary of terms

Some price fields are distinguished by (Original currency) VS (Local currency).

Field Name	Report	Notes
List price	Subscriptions	Only recorded for Full Service subscriptions
Sales Price	Price, Subscriptions	12 month price / annual rate
Publisher Adjustments	Subscriptions	The amount added/subtracted from Sales Price when there’s prorating or a one-time credit / discount.
Price last year	Subscriptions	The previous year’s 12 month price
Product category	Subscriptions	Type of product e.g. book/journal/multimedia
Publisher name	Subscriptions, Price	Vendor name; we do not distinguish
Status	Subscriptions	Active/inactive - whether the subscription is ongoing or has expired
Start Date/End Date	Subscriptions, Price	When the subscription starts and ends
Total price	Subscriptions	Sales Price + Publisher Adjustments. If Total price (Local currency), it includes exchange rate.
Price type	Price	If a subscription is New or a Renewal
Price status	Price	If a price has been Accepted, Rejected, or is Waiting for a decision. “Not Sent” - these are either not ready, or we don’t have pricing on hand.
Discounts	Price	Publisher Adjustments that are subtracted
Fees	Price	Publisher Adjustments that are added
Final price	Subscriptions	Ignore. Sales price with exchange rate applied.
Group start/end date	Subscriptions, Agreements	The start and end date for multi-year agreements
Account	Subscriptions	Paid out of Deposit Account or not

A little on system logic

All subscription records are price records, but not all price records are subscriptions.
Subscriptions are specifically price records with a status of “Accepted”, and only “Accepted”.
Renewals are price records with a type of “Renewal”, and can have different statuses.

Objective: To export a spreadsheet of all your past and currently active subscriptions.

This will not include cancellation records or upcoming potential subscriptions that aren’t Accepted.

Filter options

Expand for different ways to filter your report

Filter by Period: Click on the “Start year” facet under “SUBSCRIPTIONS” to filter to specific years
- This means the calendar year in which the subscription started
- i.e. for FY 22/23 subscriptions, check 2022 and 2023
- Leave blank for all years up to the latest facet
Filter by Subscription Type under “SUBSCRIPTIONS”
- Check “New” for only New* subscriptions
- Check “Renewal” if you only want to see renewals
- Leave blank for all
Filter by Currency under “SUBSCRIPTIONS”
- Check the relevant box if you only want to see subscriptions paid in a certain currency
Filter by Publishers under “PUBLISHERS”
- Search by the vendor’s name, or part of the name, e.g. “Criterion”; “EBSCO”
- Click on “Name” to use checkbox options
Filter by Products under “PRODUCTS”
- Search by a product name, or part of the name, e.g. “Psyc”; or “Science”
- Click on “Name” to use checkbox options

You should receive a spreadsheet in your email shortly.

In Excel

After downloading and opening your spreadsheet, you will have to filter for just the months in your desired reporting period.

If you would like more guidance, expand this box for suggested steps.

Select all cells with data
Apply a filter
Click on the grey square button in the “Start Date” column header
In the drop down, click on the + sign expand the start and end years of your reporting period

Uncheck the months that fall outside the fiscal year. For example, if the reporting period is FY22/23 and FY 23/24, I will uncheck all months after March 2024.

Alternatively, to delete all unwanted records:

Only the subscription records that fall within your reporting period should remain.