Add support for retrieving and running parameterized reports from a Git repository
Description
Environment
Potential Workaround
Attachments
- 30 May 2022, 01:35 PM
defines
has to be done after
has to be done before
is blocked by
relates to
Checklist
hideTestRail: Results
Activity
Charlotte Whitt September 27, 2023 at 6:11 PM
Really nice @Mike Taylor and @Nassib Nassar.
Mike Taylor September 27, 2023 at 5:02 PM
With https://folio-org.atlassian.net/browse/UILDP-100#icft=UILDP-100 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 https://folio-org.atlassian.net/browse/UILDP-99#icft=UILDP-99 and https://folio-org.atlassian.net/browse/UILDP-100#icft=UILDP-100, so no, I don't think it should be marked as unblocked.
I see https://folio-org.atlassian.net/browse/UILDP-104#icft=UILDP-104 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
@Mike Taylor - should I unblock this ticket and move it to In progress?
Also should I assign https://folio-org.atlassian.net/browse/UILDP-104#icft=UILDP-104 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 AMEdited
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 (https://folio-org.atlassian.net/browse/UILDP-104#icft=UILDP-104) for adding recursive traversal later.
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 }}:
-- {{ instance_type_filter1 }} ''::VARCHAR AS instance_type_filter1,
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.