I am building out a "report generator layout" and I am getting stuck on how best to implement this.
The idea is simply a layout which allows a user to select a report they want, and a date range, and possibly some additional search parameters - not quite sure on the last one yet. Then they would simply click generate and it would open the layout where that report has already been created and run a script to do the searching and everything. Another important piece is being able to filter reports by access level of the user, certain reports cannot be accessed by everyone.
So I assume I am going to need a reports table which keeps the name of the reports, and perhaps the level required to access them - but I am not quite sure where to go from here. First, I added some fields to my preferences table, such as report name and date range, and access level, and built a layout with that as its context, related to my reports table on user access level field:
Preferences -< Reports (based on access level field)
The idea being that I could show only the related report fields in the drop down. I am not sure this is going to work that well though, because I would like any user under a certain level to have access to the reports. I also run into some issues with multiple users running reports because it will say that someone is already editing that record since the preferences table only has 1 record. I also have a staff table that has a record for every company user available to potentially use.
Just looking for some suggestions around how to design this, any best practices you might have from past implementations would be appreciated. Thank you!