Add support for retrieving and running parameterized reports from a Git repository

Description

Develop a method of retrieving SQL reports (authored elsewhere) from a Git repository and running them with user-specified parameters. This functionality is somewhat separate from the form-based query builder, though part of the same App. It also makes use of a settings screen for configuring URLs/connections to Git repositories.

The reporting community's SQL queries—for example in https://github.com/folio-org/folio-analytics/tree/main/sql/report_queries—already implement similar functionality encoded in the SQL. For example:

https://github.com/folio-org/folio-analytics/blob/main/sql/report_queries/title_count/title_count.sql

At the top of the query there is a clause that begins "WITH parameters AS" which contains the parameters. The user takes the query and fills in some of those parameters.

A separate metadata file (in JSON or a simpler, standard format) will be located in the same directory with the SQL query file(s). For each parameter the metadata include the parameter name, data type, allowed values or range of values, and text description. User-supplied values are merged into the parameters defined in SQL by some method to be determined. For example, the parameter definition could be preceded by a comment line containing the parameter name in {{ and }}:

Merging the values in this way cannot be done by a simple, global template substitution, but it could be done by processing the SQL line-by-line.

Environment

None

Potential Workaround

None

Attachments

1

Checklist

hide

TestRail: Results

Activity

Show:

Charlotte Whitt September 27, 2023 at 6:11 PM

Really nice and .

Mike Taylor September 27, 2023 at 5:02 PM

With complete, it's now possible to see the results of running a templated query in the UI. I therefore consider this closed. We will want to tweak how it works, but such tweaks should be their own issues.

Mike Taylor August 3, 2023 at 11:49 AM

I consider the present issue blocked on MODLDP-43 as well as and , so no, I don't think it should be marked as unblocked.

I see as for another day, but Nassib will have a better sense of the relative urgency of this from the perspective of users.

Charlotte Whitt August 3, 2023 at 11:26 AM

- should I unblock this ticket and move it to In progress?
Also should I assign to the feature we are working on for Poppy, or is this work planned to be solved at a later flower release?

Mike Taylor August 3, 2023 at 8:45 AM
Edited

OK. In the mean time, I went ahead and made the simplifying assumption that the queries (and their associated JSON descriptions) are in the specific directory itself.

So for now I will continue with that code, and I've filed a separate issue () for adding recursive traversal later.

Done

Details

Assignee

Reporter

Tester Assignee

Priority

Sprint

Development Team

Thor

Release

Poppy (R2 2023)

TestRail: Cases

Open TestRail: Cases

TestRail: Runs

Open TestRail: Runs
Created October 19, 2021 at 9:45 PM
Updated November 28, 2024 at 11:41 AM
Resolved September 27, 2023 at 5:02 PM
TestRail: Cases
TestRail: Runs