| Table of Contents |
|---|
| minLevel | 1 |
|---|
| maxLevel | 2 |
|---|
| outline | false |
|---|
| type | list |
|---|
| printable | false |
|---|
|
...
Overview
This how-to guide contains instructions on exporting data from Consortia Manager (CM) to help answer various questions.
Listed in the table below are the relevant report types.
...
Relevant Report Types
Report Types | Possible Uses | Limitations |
|---|
Prices | List past and current subscriptions, and all future renewals
List cancellations | No Deposit Account metadata
Doesn’t show subscription notes |
Subscriptions | List past and current subscriptions, and any future renewals that have been accepted
Use to calculate price increases | Doesn’t show upcoming renewals that aren’t accepted
Doesn’t show cancellations |
Please contact OCLS if you would like want the raw data for a pro forma Deposit Account Balance Statement.
Report Field Terms
| Expand |
|---|
| title | Expand for a glossary of terms |
|---|
|
Some price fields are distinguished by (Original currency) VS (Local currency). Field Name | Report | Notes |
|---|
Account | Subscriptions | Paid out of Deposit Account or not | 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 | List price | Subscriptions | Only recorded for Full Service subscriptions | Sales Price last year | Price, Subscriptions | The previous year’s 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 | 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. | Price type | Price | If a subscription is New or a Renewal | Product category | Subscriptions | Type of product e.g. book / journal / multimedia | Publisher Adjustments | Subscriptions | The amount added/subtracted from Sales Price when there’s prorating or a one-time credit / discount. | Publisher name | Subscriptions, Price | Vendor name; we do not distinguish | Status | Subscriptions | Active/inactive - whether the subscription is ongoing or has expiredbetween vendor and publisher | Sales Price | Price, Subscriptions | 12 month price / annual rate | Start Date/End Date | Subscriptions, Price | When the subscription starts and ends | Status | Subscriptions | Active - a subscription term is ongoing
Inactive - a subscription term has ended or yet to start | 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.
|
Get to the Reports Module
| Excerpt |
|---|
|
In the top navigation bar, hover over MORE In the drop down, click on REPORTS
 Image Modified |
List All past and current subscriptions
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.
Steps
Go to the Reports Module
In the main menu, 📖 copy the report titled “Subscriptions Report Template”
In the “Edit the report” interface, you can further customize this report.
Filter the report using the facets in the left navigation sidebar as you desire
Filter options
| Expand |
|---|
| title | 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” Filter by Currency under “SUBSCRIPTIONS” 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
|
Click the REFRESH button to preview
Click SAVE REPORT & GENERATE
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.
| Expand |
|---|
| title | 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
 Image RemovedUncheck 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.
 Image RemovedClick Ok to close the drop down and see your filtered results.
Alternatively, to delete all unwanted records: Follow steps 1-3 above Click the box next to the year to unselect everything Expand the start and end years of your reporting period Check all the months that you don’t want Click ok Select all displayed rows and delete them
 Image RemovedClear the filter
 Image RemovedOnly the subscription records that fall within your reporting period should remain. |
Price increases report
All current and future renewals
n.b. can also include past subscription in this report
Steps
<screenshot of nav bar >
<choose report type>
List cancellations
...