Maybe this is a shot in the dark here but I'm trying to find out if anyone has thoughts on this problem we have been presented with.
The situation is that we have a database that contains all kinds of data about a large list of projects. There are dozens of tables that all provide supporting info about a project, both in 1 to 1 manner, where some specific type of info about projects (say ProjectInfoTypeA) might be stored in a table called ProjectInfoTypeA, and we'd do a inner join between that and the projects table, as well as 1 to many, like maybe ProjectScopeKeywords, where a project can be assigned N attributes or in this case "keywords" for a number of different attribute/lookup tables.
In the end we need to have the user in our web app build up things like: Show me all projects completed in the last 5 years that took at least 4 years to do, cost at least $1MM, and have all 3 of these keywords ( x,y,z ) associated with it.
We also want users to be able to save their queries so they, and other users, can select them from a list of saved queries.
Once we get the list of projects from their filter, we need to then work with it in all different ways: but not as a report. If this were a report I'd just give them some report builder of some kind, but we need to work with their filtered list in the web app.
Currently we are thinking of 2 different ideas: 1) being that we just try to write our own UI for building up the query, and then create some giant SQL statement.
2) we store the data about each of their filters in the database, and then when they slick "Search" we would essentially prune down the list of projects by iteratively stripping off the projects that didn't match each query, based on the data they stored in the database.
I'm guessing no one out there has had to deal with something like this, but if any of you had, I'd be interested to hear any suggestions/patterns that would be worth looking into.
I would recommend choosing option 1. I have used a query-builder approach on a number of projects, with varying degrees of sophistication depending on the complexity of the requirements.
If you are in a position to use a ready-made solution, you can find several on the web: http://www.google.com/search?q=sql+query+builder
For a custom built solution, at a minimum, you would probably want to provide flattened views for the user to query from; this will simplify the designer complexity, reduce the learning curve for the user, and provide some abstraction against future schema changes.
After defining your base data sources, you need to provide means by which the user can select specific columns, define filter criteria, specify value aggregation, and define sub-queries (based on your example query requirement). The column selection and filter definition should not be too difficult, but the value aggregation and sub-query creation would be non-trivial to define. You should be able to use the ready-made solutions as examples of how to present this functionality to the user.