Reporting App Part 2: Connecting a Repository for Reports
This page documents connecting a repository to FOLIO and building reports that can then be run within the Reporting App. For information on building queries within the app, check out Reporting App Part 1: Using the App.
Getting Started
Running reports is the most robust way to use the Reporting App but requires you to have a repository (GitHub or GitLab) containing the SQL and associated files. The advantage of this is that your queries can join tables to provide more useful reports for staff. This involves the following processes:
Creating a GitHub or GitLab repository for your report files.
Connecting the repository to FOLIO through Settings.
Running reports within the Reporting App.
There are a couple of things to note about the repository connection:
Once connected, if you add report files to your repository, they will appear in the Reporting App.
As of Ramsons, while your reports may organized using folders in your repository, the reports are presented as a single list in the Reporting App.
The Reporting App simply lists the .sql files using the display name.
Starting a Repository
For those new to GitHub, it is a popular, and free, platform for storing and sharing code. Creating an account and 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 building your own files for the Reporting App. 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 “reports” 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. This is what staff initially see when they open a report in the app.
A SQL (.sql) file that contains the SQL code for the query.
Markdown Files
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:
JSON Files
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. There are also Source links to the SQL and JSON files in the repository so you can view the code. Here is an example of how the JSON file displays in the Reporting App:
The report display name you see in the reporting app comes from the .json file.
SQL Files
SQL files contain your queries. Consult the Building Reports section on this page for resources on building these queries.
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:
How the repository files are displayed in the Reporting App
The Reporting App distills your repository files into a simple list of reports. The Display name comes from the associated JSON file. The Reporting App does not currently support folders.
Building Reports
General practice is to create a user defined function for the query. The FOLIO Analytics Wiki has a good section on how these are structured. These resources can also help you with building your .sql report files.
Tips and Tricks
Extracting elements from JSON objects
Some tables will have columns that are JSON objects. These columns are usually labelled as jsonb. You can extract elements from the JSON object using a function like jsonb_extract_path_text(table.jsonb, ‘JSON element name’). Resources:
Using Arrays
It is possible to enter a list of values for a parameter by using an array:
In your JSON and SQL files, designate the array using the data type. Examples: text[], integer[].
When you run the report, enter your values in {}. Example: {location a, location b, location c}.
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.
Additional Assistance
The FOLIO Reporting SIG is an excellent place to meet others and ask for assistance with building reports. You can also join the Open Library Foundation Slack to post questions. Key Channels to Join:
folio-reporting-general
folio-reporting-metadb