RM-Cost Per Use Report Prototype

Next Step

-ask Annika and Owen if this report is already included in the eUsage app (see UXPROD 1264) ; set up meeting to discuss this with Martina Schildt, Martina Tumulla, Scott Perry, Bjorn, Owen, and Kalila at WolfCon or afterwards online 

-check snapshot for eUsage app (no data yet)


Questions

-what is the source report requirement?

-is there an example of this report somewhere?


(REPORT EXAMPLE)


Report Description 

This ERM (Electronic Resources Management) report prototype is modeled on (REP-???, ID900???), which provides the cost of electronic resource titles per view and per download. This requires data from the eUsage application FOLIO attributes for agreements, orders, and invoices.


The application that captures eUsage statistics for an institution varies. Some may use the eUsage app, others may use another application.


See ERM Statistics summary for report descriptions and priority.


Report Criteria

  • cost per view of given title
  • cost per download of given title
  • invoice amount / number of titles = cost per title
  • cost per use = cost per title/use of title


FOLIO Attribute Definition Sources

API reference documentation for all modules located at:  https://dev.folio.org/reference/api/

eUsage data attributes???

https://docs.google.com/document/d/1jeFPHIUmRPCAPFpKkciIOBidQbXGBEE-8Fez0vKszrk/edit

Finance Data Model

UXPROD-1264 - Getting issue details... STATUS


See mod-erm-usage in API documentation for FOLIO Attributes


Agreements App- manages packages and titles within packages with connection to Order and Invoice in the Finance App; for ongoing agreements and orders, there will be many invoices, so you may need to look at particular time periods

eUsage app - harvesting counter statistics through SUSHI of downloads and views of given titles; harvest downloads and views from one vendor


Data Elements

-title from eUsage

-downloads and views from eUsage

-eUsage is connected to Agreements app and Invoices are connected to Orders at the PO line

-Agreements lines are connected to the PO line and the PO line is connected to the Invoice line

-invoices from Invoices App

-orders from Orders App



(MORE DETAILED DESCRIPTION)

Folio Attribute (Module/Path:Object)Folio Data Element DescriptionParameters/Query



Table name: mod-finance-storage/transactions

Transaction ID (mod-finance-storage:transactions:id)The UUID of a transactionWill provide the Id's for all transactions needed for this report. The ID itself does not connect with other tables. Connection from transactions will be done via fiscal year, fund, and invoice tables.
Transaction Amount (mod-finance-storage/transactions:amount)The expenditure amount associated with a particular financial transaction

The report asks libraries to report the sum of all expenditures within a given category (i.e., "ongoing" or "one-time"), for a specified time period (e.g., fiscal year 2019).

This report should return a sum of transaction amounts, as filtered by other elements.

Transaction Type (mod-finance-storage/finance-storage/transactions:transactionType)Describes the type of transaction, based on the pre-defined categories of: allocation, credit, encumbrance, payment, and transfer.This element will be used to account for expenditures, when transactionType parameter will be selected to "Payment" and "Credit". One may wish to also include encumbrances, in which case the transactionType "encumbrances" parameter will be selected.
Table Name: mode-finance-storage/fiscal_years

Fiscal Year ID (mod-finance-storage/fiscal_years:id)UUID for a specific fiscal year

Will be used to provide a key time-based filter point. Provides a join point between mod-finance-storage/transactions and mod-finance-storage/fiscal-years. Connects with: mod-finance-storage/transactions:fiscalYearId

Fiscal Year Name (mod-finance-storage/fiscal-years:name)The name of the fiscal year periodProvides a key time-based filter point for this report. This report involves all transactions for a specific fiscal year period.
Fiscal Year Code (mod-finance-storage/finance-storage/fiscal-years:code)The code of the fiscal year periodProvides the code of the fiscal year. This report reporting involves all transactions for a specific fiscal year period. Ex: FY2020
Table Name: mod-finance-storage/Ledgers

Ledger ID (mod-finance-storage/ledgers:Id)

UUID for a specific ledgerProvides a join between mod-finance-storage/finance-storage/funds and mod-finance-storage/ledgers. Connects with mod-finance-storage/funds:ledger:id
Ledger Name (mod-finance-storage/ledgers:name)Provides the name of the ledgerSupports filtering by ledger. 
Table Name: mode-finance-storage/Invoices

Invoice ID (mod-invoice-storage/invoices:id)UUID of the invoice

Provides a join between mod-orders-storage/invoices and mod-orders-storage/orders_invoice_relns. This will give us access to mod-orders-storage/po_lines through mod-orders-storage/orders_invoice_relns . Connects with mod-orders-storage/orders_invoice-relns:invoiceId

Table Name: mode-orders-storage/orders

Order ID (mod-orders-storage/orders:id)UUID for a specific orderProvides a join between mod-order-storage/po_lines/purchaseOrderId and mod-storage/orders. Connects to: mod-orders-storage/po_lines:purchaseOrderId. This will allow us to pull order details related to an invoice.
Order Type  (mod-orders-storage/orders:order_type)Provides the label for the order type ( One-Time or Ongoing)Will be needed to get "One-time" or" Ongoing" expenditures. This report is asking for a sum of all expenditures for each category. Should be able to filter by order type.
Table name: mod-orders-storage: po_lines

PO lines ID: (mod-orders-storage/po_lines:id)The UUID of a PO LineWill be used to connect to the table 'pieces' in order to be able to get to material types and location.  Connects with mod-orders-storage/pieces:PoLineId.
Po lines Order Format name: (mod-orders-storage/po-lines/order-format:name)Provides the format of the item purchasedWill provide in the report the format name of the order. (Electronic Resource, Physical Resource, P/E Mix,Other).
Table Name: mod-orders-storage/orders_invoice_relns

Orders_Invoice_relns ID (mod-orders-storage/orders_invoice_relns:id)The UUID of a relationship between order and invoiceProvides a join between mod-orders-storage/invoices and mod-orders-storage/orders. Connects with mod-orders-storage/orders_invoice_relns:purchaseOrdeId.