Create a reporting service with no fixed pre defined reports

Question

I have to create a service/app which allows creation of custom report templates & schedules using those templates(like create the report daily) that will generate the report in a the specified format (CSV or PDF for now) and send it onto a fileserver through SFTP. The backend database is Oracle, rest of the stack is in java - spring boot.

The problem is the report templates are not pre defined and what the PO essentially wants is a way for the user to dynamically define the report she wants and then schedule it. This is a pretty open ended requirement because the kind of reports are not pre-defined. In a way its almost like building a SQL client but instead of returning the rows there itself it will schedule the report in the form of CSV or PDF.

I was thinking about some ways to implement this and would like opinion on them or any other approach or advice would also helpful.

Option 1 : If its only used by operations - Simply provide a text box for the query with placeholders with a form to add and define the placeholder field names and their data types separately. Also provide a text box to define the report column headers (users responsibility to match them etc). When the user has to schedule the request she may select a template and provide the required placeholder values and schedule the report.

Pros:

• Generic
• can be used with any data source
• Relatively much lower effort to implement.

Cons:

• Even if its for the operations free text box to define queries does seem like the best of the user experience.
• Parsing queries for validations or feeling placeholder etc can be a challenge. I found a library to do it : https://github.com/JSQLParser/JSqlParser not sure LGPL is available for commercial use though and haven't evaluated it completely.

Option 2: Understand the datamodel and mostly used query patterns. Create and define groups of such domain objects and show them in a drop or something in the GUI. Along with that provide an option to select report headers which will again map to column names in the DB. (This mapping can be stored in a config file may be), an option to add a filter - Gui to create boolean expression to filter the data (This can get tricky on the UI side), option to select sorting direction & field, Grouping etc.

This will not support complex relations and will mostly work on related object groups but will provide a better GUI. This data will be collected on the server side and a query will be generated from it to get the data from the DB.

Pros: Better Gui and user experince.

Cons: Gui wont support all cases. I can think of adding joins in some way but then its a full blow SQL query builder which in my opinion will take a lot of time and effort probably to build. So for simple queries the above form can be used but for complex queries one can still provide the text box.

All in all, I am not very sure what the best way to handle this. Do the above options make sense? Or are their some other approaches that I can think of? Are there any off the shelve products perhaps that can help with this?

Answer 1

You've already mentioned the options and their pros/cons. You might also want to look at commercially available options for comparison. In any case, the decision should likely be one of economy - how much time (money) does a more costly solution save?

At the end of the day, it feels very unlikely that a sophisticated solution will ever save enough money to pay for its development - some simple utilities for ops to make their work in providing new reports, plus raising some customer awareness that reports don't come for free just because they wish for them, might be the most feasible route.