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.


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


  • 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?

  • It sounds largely like an attempt to reproduce standard facilities that exist for developers. I could understand building a front-end for running or altering the scheduling of reports which developers pre-define for users. But once the end-user is writing SQL, you may as well just allow them access to ordinary developer tools.
    – Steve
    Commented Oct 16, 2023 at 10:06
  • Yes that's what the ops were doing but then some customers have started asking for different data periodically and hence the ops want some way to do it from gui. Commented Oct 16, 2023 at 11:03

1 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.

  • Agree and they are not free the Customers do pay for it. Just that it's blocking ops team a lot so they want to automate it. Do you know of any commercially of the shelf products that do this well? Commented Oct 16, 2023 at 15:28
  • I don't really know this area well, so I can not recommend any product (and shouldn't on this site anyway). I would run an internet search and try to find out which products might fit my environment, needs and budget :-) "sql report generator open source" even lists some free ones which might fit your case. Commented Oct 16, 2023 at 15:40
  • Well, of course I googled it. Reason to put it here is to leverage someone else's experience or knowledge with this sort of requirement which will save me some pitfalls, pain, time and effort in the future. Commented Oct 17, 2023 at 4:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.