Using the Reporting App (DRAFT)
This is a the draft of a new page to help libraries with getting started with the FOLIO Reporting App. Feel free to comment on this page or reach out to @Pennington Jr, Buddy to suggest content, resources, etc. for this page.
Getting Started
The Reporting app provides a FOLIO interface for interacting with a MetaDB database.
Permissions
Permissions in Ramsons are fairly limited. There is an ‘all permissions’ permission needed to allow the user to see and use the Reporting app. Then a limited set of permissions for Reporting Settings. Permissions in Sunflower include the ‘all permissions’ permission as well as a slightly larger set for Reporting Settings.
Structure
The Reporting App utilizes a typical database structure where FOLIO data is included in tables that include columns for fields and rows for records. Given the large number FOLIO apps and records, tables are grouped into Schema, typically by app or function. Here are some examples
Schema | Tables |
|---|---|
folio_derived | This is a good place to start as the schema includes a number of tables commonly used for reports. |
folio_finance | budget, expense_class, fund, ledger, transaction, etc. |
folio_inventory | holdings_record, instance, item, location, etc. |
folio_users | groups, proxyfor, users, etc. |
Running Queries and Exporting Data
Queries come in three flavors:
Build: Build a query within the Reporting App. This has the severe limitation that you cannot join tables. You can only query a single table.
The issue with not being able to join tables quickly becomes apparent when you start seeing UUIDs in columns where you are expecting text values and then remember that FOLIO data is a very large set of linked tables.
Load: This allows you to load and run a saved query.
Run report: This is the more common way to use the Reporting App. After connecting a GitHub or GitLab repository through Reporting Settings, you can select and run queries contained in the repository. Check out the Running reports section on this page for more details on setting this up.
Building Queries
Selecting Build query in the Navigation pane will allow you to start building a query. Icons in the builder pane will let you start a new query, save query (to be loaded later) or copy query. To build your query:
Select your schema and table. Remember that you cannot join tables using the query builder.
A good place to start is the folio_derived schema as this includes tables commonly needed for reports.
Once you’ve selected your table, you can select fields to include/exclude records.
The Reporting App is limited to comparison operators. LIKE and ILIKE operators are not supported.
You can use multiple parameters using AND (OR is not supported).
Then select the columns you want to display.
You can select a field column for sorting purposes (ascending or descending).
You can set a results limit.
Click Submit to review results. When viewing results, you can export via CSV.
You can save or copy the query if needed.
Example
The query in the screenshot below is to retrieve item records with “On order” status and to sort by barcode. Only 10 records will be retrieved.
And here are the results after hitting Submit:
In the results pane, you can export to CSV by clicking the CSV button in the upper right.
The CSV export will export all the records, not just those you’ve limited to display in the app.
Load Query
Saved queries are available using Load query. Select the query you want, make any needed changes to the parameters, and then Submit to get results.
Making changes and then saving the record will overwrite the original query so if you want to save as a different query, use the copy query feature.
Running Reports
Running reports is the most robust way to use the Reporting App but requires you to have a GitHub repository containing the SQL and associated files. The advantage of this is that your queries can join tables to provide more useful reports for staff.
Starting a Repository
For those new to GitHub, it is a popular, and free, platform for storing and sharing code. Creating and account starting a new repository is relatively easy. The Reporting SIG has a directory of libraries with existing repositories that can serve as good examples to get you started. These repositories typically include separate sections for LDP or MetaDB and the reporting app.
Note: GitLab repositories are also supported by the Reporting App.
Reporting app queries contain three separate files:
A Markdown (.md) file that serves as a reference page in the repository. These files typically summarize the report, and may include parameters and what fields are output.
A JSON (.json) file that contains the report name, description, instructions, and optional parameters.
A SQL (.sql) file that contains the SQL code for the query.
Markdown files are not used in the Reporting App but are helpful when users are looking at your GitHub repository. Here is an example of what you might see:
In the Reporting App, when you select a report from the connected repository, you will see the JSON file content (the report title, description instructions, etc. If the JSON includes parameters, they will also display as fields for the staff person to populate. The are also Source links to the SQL and JSON files in the repository so you can view the code. Here is an example JSON file in the Reporting App:
Connecting a Repository to FOLIO
Once you have a repository with some queries, you can connect it to FOLIO via Settings >> Reporting >> Report repositories. Then fill in the following for your repository:
Type: GitHub or GitLab
Git site user: This is the user for the repositories. It typically follows the “https://github.com/” of your repository URL. For example: https://github.com/metadb-project/metadb-examples
Repository name within user’s area: The branch typically follows the site user. For example: https://github.com/metadb-project/metadb-examples
Branch of the specific repository: This will typically be ‘main’ though you can confirm in GitHub.
Directory within the repository: This will be the folder containing the query files for the Reporting App.
Once you have entered all the data, you should see a URL below the settings. You can open that to confirm it takes you to your repository.
Here is an example:
Running Reports
Once connected, when you open the Reporting App and select “Run report” you should see a list of your reports (name, SQL file, and repository). Select a report, provide parameter values if needed, and then Submit to get the report.
Resources