Page tree
Skip to end of metadata
Go to start of metadata

Prioritize Reports

Report prioritization involves members of the Reporting SIG and their institutions. Initial priorities were included by institutions in the Reporting SIG Master Spreadsheet. Since transferring all reports to JIRA, institutions have been maintain rankings in JIRA instead.

Select Report to Prototype

A list of high priority reports is created by considering institution rankings of reports, current FOLIO development and documentation, breadth of coverage of previously prototyped reports, and feedback from Reporting SIG members. The list of high priority reports is then reviewed by Report Prototype Working Group members, who volunteer to prototype individual reports.

Write Report Specification

Create Report Wiki Page

  • review report details, including data elements and report requirements, using the Reporting SIG Master Spreadsheet and the corresponding JIRA issue for the selected report
  • create a name for the report and one or more queries that will be required for the report (see Naming Conventions)
  • create a wiki page for this report, using report name
  • include a link to the JIRA issue for the report
  • toward the beginning of the page, explain the purpose of this report
  • include report name and names of all queries and describe any difference between queries
  • add narrative text as needed, explaining the steps for the developers

Identify Data Elements

  • review report details on the Reporting SIG Master Spreadsheet and the corresponding JIRA issue
  • Note: if working on a report cluster, or if the report has variations listed, make sure to identify data elements required across all queries
  • for any fields mentioned in the issue or present in a sample report, locate an appropriate match in the FOLIO source data
    • go to
    • scan the modules to find the general area that is most likely to include the data element
    • use only "-storage" modules
    • when you find a likely module, see "View 1" or "View 2" for the description in the API reference documentation
    • data element endpoints are in pairs; take the second element of the pair; look for the description in the GET or POST section in the Response or the Request
    • find the correct element, and note the full path to the data element
    • if you have trouble understanding the data elements:
  • Once you have matched up the data elements that are visible in the final report, do a final pass through the FOLIO documentation to make sure that you also include any data elements that will be required for joining tables together. These are likely identifier elements. Even if you want just the name to appear in the report, please locate and list all required identifiers as well.
  • On the report wiki page, create a table that lists all required data elements, their path in the FOLIO modules, and a description of how that field will be used (including whether or not the field should be displayed in the final report)

  • Optional: for especially complicated reports, it might be nice to include a relationship diagram that helps query builders make the correct connections between tables


Folio Attribute (Module, Path, Attribute)Folio Data Element DescriptionParameters/Query
(mod-users:) groups/idA UUID identifying this (patron) groupsort and count results by (patron group) id
(mod-users:) groups /groupThe unique name of this (patron) group
(mod-users:) users/idA globally unique (UUID) identifier for the user
(mod-users:) users/patronGroupA UUID corresponding to the group the user belongs to; this is a reference to (mod-users:) groups/id; this is a foreign key to the id attribute under groups

(mod-circulation-storage:/loan-storage/loans)idUnique ID (generated UUID) of the loan; represents the loan transaction; storage module; we get data from the storage modulecount loan transactions
(mod-circulation-storage: /loan-storage/loans) userIdforeign key; ID of the patron the item was lent to. Required for open loans, not required for closed loans (for anonymization).
(mod-circulation-storage: /loan-storage/loans) loanDateDate time when the loan beganfilter loan transaction ids by date range
(mod-circulation: /circulation/loans)idUnique ID (generated UUID) of the loan; business logic module; called "loanid" in the reporting database, will be marked as a foreign key
(mod-circulation: /circulation/loans)locationThe effective location of the item; nested under "items"

Include Sample of Report Output

  • After mapping out the required data elements, include a sample of the desired report output
  • The JIRA issue may have an attachment, or the master spreadsheet may have a link - reproduce one or both here
  • Alternately, can create a table in the wiki page that simulates the output
  • Note: if working on a report cluster or if report has variations listed, include separate samples for each query that needs to be developed
  • Make sure it is clear if a filter or aggregation has been applied

Review Specification with Report Requesters, RPWG, Reporting SIG

  • Make sure report requesters agree that the desired output listed is correct and that the purpose of the report has been understood accurately
  • Have RPWG review the specification
  • Have Reporting SIG review the specification

Communicate with Developers

  • If all approve the specification and wiki page is final, let developers know we need test data for these data elements

Write Query

Create a Fork of the ldp-analytics Repository

Create an LDP Issue for Your Work on This Query

  • Log into JIRA
  • Click on Create to create a new issue
  • For "Project" select LDP
  • For "Issue Type" select "Task" 
  • In "Summary" note that this is work on the query(ies) for a specific report (include report name)
  • Click Create
  • Notify Nassib to assign the task to you

Build SQL Query

  • Create new folder in ldp-analytics/sql to store sql, using the name of the query specified in the wiki page
    • If you have any trouble, contact one of the maintainers of ldp-analytics
  • Create a file with the query name and the extension ".sql"
  • Follow SQL conventions specified in LDP documentation
  • Additional tips for SQL:
    • Use all-caps for keywords (e.g., "SELECT", "FROM")
    • Start new lines for new keywords, items in long lists
    • Indent lines with 4 spaces
    • Review previous queries for other style conventions 

Test Query

  • Confirm with developers that the LDP has all required test data needed for your query
  • Log into the LDP using your reporting tool of choice
  • Execute the query
  • Review results and revise query as needed
  • When results are correct, save sample output in query folder

Push Query Back to GitHub

  • Periodically throughout your query development, you should commit and push your file edits to GitHub
  • Once the query is complete, you can submit a "pull request" to have the new query merged into the ldp-analytics repository
  • See Contributing report queries to ldp-analytics

  • No labels