How to develop a graphical report designer for users?

Question

SQL

Good afternoon!
I am developing (in Yii2, for example) a report configurator, with which users inexperienced in SQL will rivet themselves what they want. The project has several difficulties at once, but it is quite capable, the percentage of completion is good.

And now, after a while, I - suddenly! - I decided to take an interest in the opinion of independent people. How would you approach this challenge?
How would you store a list of user accessible tables and fields? How would you access them from code ? How would you provide, for example, pagination with ajax loading of the following portions of data? Sorting? Bicycle or something ready? Would be built into an existing ERP or would be a separate tool? Would you link the target SQL from the code "manually", or would you use some regular Active Record framework?

What it should be for the user is understandable . Something similar is popular in 1C:


How should programmers implement it?

Answer 1

I would first determine whether users really need this functionality. Usually, users of various kinds of accounting systems are extremely skeptical about independent programming of something, which is understandable.

Answer 2

Run SSRS Builder. Or Excel. Or Power BI or Power App.
In theory, this was all done for users so that they would quickly throw it in and that's it, in the end you can see Power BI senior engineer.
What exactly delivers. And the programs are not simple. It’s easier for me to throw in a script, create the desired view and throw it into SSRS

Answer 3

In vain you are guided by 1C.
The ACS, which is in the picture, is a tool not for users, but for developers. And as a developer tool, it leaves a lot to be desired, trust me.
On the other hand, the end result that the user gets after applying this tool is really great. This is a report that the user can customize exactly to the extent that he needs (and that the developer provided). No less, but no more.
There is no talk of developing any reports by users from scratch, the 1C developers did not set such a task. That is, they refused your idea "users inexperienced in SQL will rivet themselves what they want" .
This idea is a myth. I advise you to once again discuss this with the directors of your task.

Answer 4

make a bike
google redash, metabase
and all sorts of powerbi